[jboss-user] [EJB 3.0] - Problem w/ quoted fields in generated SQL
tsar_bomba
do-not-reply at jboss.com
Tue Nov 28 13:46:15 EST 2006
I'm using a Progress 10.1 database that requires all queries to have quoted field names, e.g. select "name", "age" from customer.
In my entities, I'm using the back-tick (`) character to make sure field names are quoted. It works fine until I have parent-child relationship in a @OneToMany (bi-directional) setup.
Here are the entities:
| @Entity
| @Table(name="pub.customer")
| public class Customer implements Serializable
| {
| @Id
| @Column(name="`Cust-no`", nullable=false)
| private String custno;
|
| @OneToMany(mappedBy="customer")
| private List<CustomerOrder> orders;
| ...................
| }
|
| @Entity
| @Table(name="pub.order")
| public class CustomerOrder implements Serializable
| {
| @EmbeddedId
| private CustomerOrderPK customerOrderPK;
|
| @Column(name="`Cust-no`", nullable=false, insertable=false, updatable=false)
| private String custno;
|
| @ManyToOne(fetch=FetchType.LAZY)
| @JoinColumn(name="`Cust-no`")
| private Customer customer;
| .................
| }
|
...and here's the PK class from the CustomerOrder entity, just for the sake of being thorough:
| @Embeddable
| public class CustomerOrderPK implements Serializable
| {
| @Column(name="`Ar-entity`", nullable=false)
| private String arentity;
|
| @Column(name="`Order-no`")
| private Integer orderno;
| .......................
| }
|
I query it in a SLSB like so:
| public Customer getTestCustomer(String custno)
| {
| String query = "select c from Customer c " +
| "left join fetch c.orders " +
| "where c.custno = :custno";
|
| Query q = this.em.createQuery(query);
| q.setParameter("custno", custno);
|
| return (Customer)q.getSingleResult();
| }
|
Here is the query that is generated at runtime:
| Hibernate:
| /* select
| c
| from
| Customer c
| left join
| fetch c.orders
| where
| c.custno = :custno */ select
| customer0_."Cust-no" as Cust1_160_0_,
| orders1_."Ar-entity" as Ar1_161_1_,
| orders1_."Order-no" as Order2_161_1_,
| customer0_."Name" as Name2_160_0_,
| customer0_."Filler1" as Filler3_160_0_,
| customer0_."City" as City4_160_0_,
| customer0_."St" as St5_160_0_,
| customer0_."Zip-code" as Zip6_160_0_,
| customer0_."Country" as Country7_160_0_,
| customer0_."Telephone" as Telephone8_160_0_,
| customer0_."Contact" as Contact9_160_0_,
| customer0_."Status-code" as Status10_160_0_,
| customer0_."Slsmn-code" as Slsmn11_160_0_,
| customer0_."Type-code" as Type12_160_0_,
| customer0_."Via-code" as Via13_160_0_,
| customer0_."Terr-code" as Terr14_160_0_,
| customer0_."Sort-name" as Sort15_160_0_,
| customer0_."Priority" as Priority16_160_0_,
| customer0_."Comm-code" as Comm17_160_0_,
| customer0_."Description" as Descrip18_160_0_,
| customer0_."Dest-loc" as Dest19_160_0_,
| customer0_."Country-code" as Country20_160_0_,
| customer0_."Address" as Address21_160_0_,
| customer0_."Partner" as Partner22_160_0_,
| customer0_."Residential" as Residen23_160_0_,
| customer0_."Gst-code" as Gst24_160_0_,
| customer0_."Cust-number" as Cust25_160_0_,
| customer0_."sls-contact" as sls26_160_0_,
| customer0_."hold-order" as hold27_160_0_,
| customer0_."supl-code" as supl28_160_0_,
| orders1_."In-entity" as In3_161_1_,
| orders1_."Whs-code" as Whs4_161_1_,
| orders1_."Cust-no" as Cust5_161_1_,
| orders1_."Ship-no" as Ship6_161_1_,
| orders1_."Cust-po" as Cust7_161_1_,
| orders1_."Order-date" as Order8_161_1_,
| orders1_."Order-code" as Order9_161_1_,
| orders1_."Slsmn-code" as Slsmn10_161_1_,
| orders1_."Via-desc" as Via11_161_1_,
| orders1_."Order-disc" as Order12_161_1_,
| orders1_."Ship-name" as Ship13_161_1_,
| orders1_."Ship-city" as Ship14_161_1_,
| orders1_."Ship-st" as Ship15_161_1_,
| orders1_."Ship-country" as Ship16_161_1_,
| orders1_."Ship-zip" as Ship17_161_1_,
| orders1_."Pps-no" as Pps18_161_1_,
| orders1_."Invoice-no" as Invoice19_161_1_,
| orders1_."Invoice-date" as Invoice20_161_1_,
| orders1_."Qty-orig-ord" as Qty21_161_1_,
| orders1_."Qty-shipped" as Qty22_161_1_,
| orders1_."Qty-open-ord" as Qty23_161_1_,
| orders1_."Alloc-qty" as Alloc24_161_1_,
| orders1_."Alloc-value" as Alloc25_161_1_,
| orders1_."Seq-no" as Seq26_161_1_,
| orders1_."Partial-inv" as Partial27_161_1_,
| orders1_."Estimate-no" as Estimate28_161_1_,
| orders1_."Total-weight" as Total29_161_1_,
| orders1_."Sent-by" as Sent30_161_1_,
| orders1_."Sent-order" as Sent31_161_1_,
| orders1_."Sent-entity" as Sent32_161_1_,
| orders1_."Transfer-to" as Transfer33_161_1_,
| orders1_."Via-code" as Via34_161_1_,
| orders1_."Country-code" as Country35_161_1_,
| orders1_."Dest-loc" as Dest36_161_1_,
| orders1_."Drop-ship" as Drop37_161_1_,
| orders1_."Site-code" as Site38_161_1_,
| orders1_."Ship-address" as Ship39_161_1_,
| orders1_."Trans-type" as Trans40_161_1_,
| orders1_."Trans-mode" as Trans41_161_1_,
| orders1_."Ship-site" as Ship42_161_1_,
| orders1_."Order-site" as Order43_161_1_,
| orders1_."ship-date" as ship44_161_1_,
| orders1_."address-no" as address45_161_1_,
| orders1_."bev-country" as bev46_161_1_,
| orders1_."Route-no" as Route47_161_1_,
| orders1_.Cust-no as Cust48_0__, --PROBLEM!!
| orders1_."Ar-entity" as Ar1_0__,
| orders1_."Order-no" as Order2_0__
| from
| pub.customer customer0_
| left outer join
| pub.order orders1_
| on customer0_."Cust-no"=orders1_.Cust-no --PROBLEM!!
| where
| customer0_."Cust-no"=?
|
|
As you can see, the field names are quoted but the right-hand side of the join is incorrect...the join field is not quoted, resulting in this exception:
| Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
| at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:647)
| at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:99)
| at com.myapp.CustomerBean.getTestCustomer(CustomerBean.java:61)
| ...............................
|
| Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
| at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
| at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
| at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
| at org.hibernate.loader.Loader.doList(Loader.java:2147)
| at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
| at org.hibernate.loader.Loader.list(Loader.java:2023)
| at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
| at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
| at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
| at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
| at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
| at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:80)
| ... 71 more
| Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column "ORDERS1_.CUST" cannot be found or is not specified for query. (13865)
| at com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepareProcessReply(Unknown Source)
| at com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepare(Unknown Source)
| at com.ddtek.jdbc.openedge.OpenEdgeImplStatement.prepare(Unknown Source)
| at com.ddtek.jdbc.base.BaseImplStatement.prepare(Unknown Source)
| ...............................................
|
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3989443#3989443
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3989443
More information about the jboss-user
mailing list