3 Replies Latest reply on Dec 8, 2006 11:07 AM by tsar_bomba

    Problem w/ quoted fields in generated SQL

    tsar_bomba Newbie

      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)
      ...............................................