FrontBase + ordering clause generates invalid sql query

abaudoux · **Joined:** Fri Mar 24, 2006 1:20 pm **Posts:** 7

Hello,

I have a problem using ordering clauses with Frontbase, as frontbase requires to specify any defined column alias in an order by clause, and not the column name itself. The below generated sql is thus incorrect, the correct format for FrontBase is "Order by c40_4_0_ desc".

How can i make this work? This is verry annoying.

Thanks in advance for your help

Antoine

Hibernate version:
3.1.3

Code:

Code:

.....
        Criteria query =  sess.createCriteria(CORequest.class);
           query.add(Property.forName("chamber").eq(rquery.getManager().getChamber()));
           
           if (!rquery.isIgnoreAssign()) {
              if (rquery.getAssigned() == null) {
                 query.add(Property.forName("manager").isNull());
              } else {
                 query.add(Property.forName("manager").eq(rquery.getAssigned()));
              }
           }
           
           if ((rquery.getStates() != null) && (rquery.getStates().size()>0)) {
              query.add(Restrictions.in("state",rquery.getStates()));
           }
           
           // THE PROBLEM IS HERE
           query.addOrder(Order.desc("value"));
           
           query.setMaxResults(MAX_TOTAL_RESULTS);
           
           List<CORequest> allRequests = query.list();

.....

Full stack trace of any exception that occurs:

Code:

Error code 363.
errorCode:   363
Stack Trace:
com.frontbase.jdbc.FBJErrorMetaData.errorMessageAtIndex(FBJErrorMetaData.java:160)
com.frontbase.jdbc.FBJErrorMetaData.getExceptionChain(FBJErrorMetaData.java:189)
com.frontbase.jdbc.FBJStatement.checkMetaData(FBJStatement.java:433)
com.frontbase.jdbc.FBJStatement.execute(FBJStatement.java:175)
com.frontbase.jdbc.FBJPreparedStatement.<init>(FBJPreparedStatement.java:43)
com.frontbase.jdbc.FBJConnection.prepareStatement(FBJConnection.java:531)
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:185)
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
org.hibernate.loader.Loader.doQuery(Loader.java:661)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
org.hibernate.loader.Loader.doList(Loader.java:2145)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
.......

Name and version of the database you are using:
Frontbase 4.4.5

The generated SQL (show_sql=true):

Code:

select this_.id as id4_0_, this_.c_deleted as c2_4_0_, this_.c_expName as c3_4_0_, this_.c_expAddress as c4_4_0_, this_.c_expZip as c5_4_0_, this_.c_expCountry as c6_4_0_, this_.c_factName as c7_4_0_, this_.c_factAddress as c8_4_0_, this_.c_factZip as c9_4_0_, this_.c_factCountry as c10_4_0_, this_.c_boName as c11_4_0_, this_.c_boAddress as c12_4_0_, this_.c_boZip as c13_4_0_, this_.c_boCountry as c14_4_0_, this_.c_destName as c15_4_0_, this_.c_destAddress as c16_4_0_, this_.c_destZip as c17_4_0_, this_.c_destCountry as c18_4_0_, this_.c_askName as c19_4_0_, this_.c_askAddress as c20_4_0_, this_.c_askZip as c21_4_0_, this_.c_askCountry as c22_4_0_, this_.c_dateReceived as c23_4_0_, this_.c_dateTreating as c24_4_0_, this_.c_dateAccepted as c25_4_0_, this_.c_dateBilled as c26_4_0_, this_.c_note as c27_4_0_, this_.c_transportInformations as c28_4_0_, this_.c_generalMarchandiseDescription as c29_4_0_, this_.c_marchandiseDetails as c30_4_0_, this_.c_marchandisePacking as c31_4_0_, this_.c_shippingMarks as c32_4_0_, this_.c_quantities as c33_4_0_, this_.c_ownFabrication as c34_4_0_, this_.c_ownFabricationDetails1 as c35_4_0_, this_.c_ownFabricationDetails2 as c36_4_0_, this_.c_ownFabricationDetails3 as c37_4_0_, this_.c_additionalInformations as c38_4_0_, this_.c_destinationCountry as c39_4_0_, this_.c_value as c40_4_0_, this_.manager_id as manager41_4_0_, this_.destination_id as destina42_4_0_, this_.chamber_id as chamber43_4_0_, this_.expeditor_id as expeditor44_4_0_, this_.company_id as company45_4_0_, this_.state_id as state46_4_0_ from t_CORequest this_ where this_.chamber_id=? order by this_.c_value desc

jt_1000 · **Posted:** Fri Mar 24, 2006 3:41 pm

check out:

http://forum.hibernate.org/viewtopic.php?t=956317&highlight=orderby

In a nutshell he:
Since HQL will define its own aliases, he needed pick up those from the generated SQLs whenever there is a need to order the results.

abaudoux · **Joined:** Fri Mar 24, 2006 1:20 pm **Posts:** 7

Basically you are saying that in order to be able to add sorting, i must not only drop the Criteria API, but also drop HQL and make plain SQL qtatements? This is not an acceptable solution for me. Can someone guide me on how i can patch the hibernate code to make The HQL and criteria API for sorting Work with frontbase?