-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
colinwilliams
 Post subject: order-by problems
PostPosted: Mon Nov 22, 2004 11:59 am 
Newbie

Joined: Mon Nov 22, 2004 11:41 am
Posts: 8
Hi,

I am having a problem using order-by on as400. I have used it previously in other mapping files, no problem, but for some reason hibernate seems to be using the wrong field name in the order-by. It looks as if it is giving the result field a new name in the sql statement, but using the original name in the order-by

Anyone have any workarounds for this, or suggest what im doing wrong?

the order-by is on SBDESC , a description field that I want to sort on?

Hibernate version:
2.1.6 (also tried on 2.1.2 and 3 with the same result)

Mapping documents:
Code:
<hibernate-mapping package="com.bertrams.bertweb.domain.customer">
    <class name="Customer" table="BBEDPDL5">
      <composite-id name="id" class="CustomerKey">
        <key-property name="account" type="long" column="BEACCN"/>
        <key-property name="branch" type="long" column="BEBRAC"/>       
      </composite-id>
       
       <map name="baskets" where="SBSTAT='OPN'" [b]order-by="SBDESC"[/b]>
          <key>
               <column name="SBACCT"/>
               <column name="SBBRAN"/>
            </key>
           <index column="SBUUID" type="com.bertrams.bertweb.dao.hibernate.TrimmedString"/>
            <one-to-many class="com.bertrams.bertweb.domain.order.Basket"/>
       </map>

      </class>
</hibernate-mapping>

<hibernate-mapping package="com.bertrams.bertweb.domain.order">
    <class name="Basket" table="BBSHBM">
      <composite-id name="id" class="BasketKey">
        <key-property name="account" type="long" column="SBACCT"/>
        <key-property name="branch" type="long" column="SBBRAN"/>
        <key-property name="uuid" type="com.bertrams.bertweb.dao.hibernate.TrimmedString" column="SBUUID"/>
      </composite-id>
                                   
        <property name="description" type="com.bertrams.bertweb.dao.hibernate.TrimmedString">
           <column name="SBDESC" sql-type="char(50)" not-null="true"/>
        </property>

    </class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Code:
Customer customer = (Customer)currentSession().get(Customer.class, ck);


Full stack trace of any exception that occurs:
Hibernate: select baskets0_.SBACCT as SBACCT__, baskets0_.SBBRAN as SBBRAN__, baskets0_.SBUUID as SBUUID__, baskets0_.SBACCT as SBACCT0_, baskets0_.SBBRAN as SBBRAN0_, baskets0_.SBUUID as SBUUID0_, baskets0_.SBDESC as SBDESC0_, baskets0_.SBTYPE as SBTYPE0_, baskets0_.SBTYPEID as SBTYPEID0_, baskets0_.SBSTAT as SBSTAT0_, baskets0_.SBCDAT as SBCDAT0_, baskets0_.SBCUSR as SBCUSR0_, baskets0_.SBCPGM as SBCPGM0_, baskets0_.SBMDAT as SBMDAT0_, baskets0_.SBMUSR as SBMUSR0_, baskets0_.SBMPGM as SBMPGM0_ from BBSHBM baskets0_ where baskets0_.SBACCT=? and baskets0_.SBBRAN=? and baskets0_.SBSTAT='OPN' order by baskets0_.SBDESC
[22/11/04 15:38:41:562 GMT] 5a795a79 SystemOut O as400: PreparedStatement STMT0005 (1741514701) open. Parent: Connection S44B4901 (744107098) .
as400: PreparedStatement STMT0005 (1741514701) : Escape processing = "true".
as400: PreparedStatement STMT0005 (1741514701) : Fetch direction = "1000".
as400: PreparedStatement STMT0005 (1741514701) : Fetch size = "0".
as400: PreparedStatement STMT0005 (1741514701) : Max field size = "0".
as400: PreparedStatement STMT0005 (1741514701) : Max rows = "0".
as400: PreparedStatement STMT0005 (1741514701) : Query timeout = "0".
as400: PreparedStatement STMT0005 (1741514701) : Result set conncurrency = "1007".
as400: PreparedStatement STMT0005 (1741514701) : Result set type = "1003".
as400: PreparedStatement STMT0005 (1741514701) : Preparing [select baskets0_.SBACCT as SBACCT__, baskets0_.SBBRAN as SBBRAN__, baskets0_.SBUUID as SBUUID__, baskets0_.SBACCT as SBACCT0_, baskets0_.SBBRAN as SBBRAN0_, baskets0_.SBUUID as SBUUID0_, baskets0_.SBDESC as SBDESC0_, baskets0_.SBTYPE as SBTYPE0_, baskets0_.SBTYPEID as SBTYPEID0_, baskets0_.SBSTAT as SBSTAT0_, baskets0_.SBCDAT as SBCDAT0_, baskets0_.SBCUSR as SBCUSR0_, baskets0_.SBCPGM as SBCPGM0_, baskets0_.SBMDAT as SBMDAT0_, baskets0_.SBMUSR as SBMUSR0_, baskets0_.SBMPGM as SBMPGM0_ from BBSHBM baskets0_ where baskets0_.SBACCT=? and baskets0_.SBBRAN=? and baskets0_.SBSTAT='OPN' order by baskets0_.SBDESC].
SQLException: SQLState(42707) vendor code(-208)

[22/11/04 15:38:41:656 GMT] 5a795a79 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -208, SQLState: 42707

[22/11/04 15:38:41:687 GMT] 5a795a79 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0208] ORDER BY column SBDESC or expression not in result table.

[22/11/04 15:38:41:718 GMT] 5a795a79 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -208, SQLState: 42707

[22/11/04 15:38:41:718 GMT] 5a795a79 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0208] ORDER BY column SBDESC or expression not in result table.

[22/11/04 15:38:41:734 GMT] 5a795a79 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter could not initialize collection: [com.bertrams.bertweb.domain.customer.Customer.baskets#[account:63173][branch:1]]

[22/11/04 15:38:41:734 GMT] 5a795a79 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: The following exception was logged java.sql.SQLException: [SQL0208] ORDER BY column SBDESC or expression not in result table.
at java.lang.Throwable.<init>(Throwable.java)
at java.lang.Throwable.<init>(Throwable.java)
at java.sql.SQLException.<init>(SQLException.java:52)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:388)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1075)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:208)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1419)
at com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:669)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1458)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1424)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:93)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3268)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3123)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2117)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1991)
at net.sf.hibernate.impl.SessionImpl.get(SessionImpl.java:1927)
at com.bertrams.bertweb.dao.CustomerDAO.getCustomer(CustomerDAO.java:20)
at com.bertrams.bertweb.delegate.CustomerDelegate.getCustomerBaskets(CustomerDelegate.java)
at com.bertrams.bertweb.controller.action.ShoppingBasketAction.list(ShoppingBasketAction.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:41)
at java.lang.reflect.Method.invoke(Method.java:386)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java)
.
java.sql.SQLException: [SQL0208] ORDER BY column SBDESC or expression not in result table.
at java.lang.Throwable.<init>(Throwable.java)
at java.lang.Throwable.<init>(Throwable.java)
at java.sql.SQLException.<init>(SQLException.java:52)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:388)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1075)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:208)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1419)
at com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:669)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1458)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1424)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:93)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3268)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3123)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2117)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1991)
at net.sf.hibernate.impl.SessionImpl.get(SessionImpl.java:1927)
at com.bertrams.bertweb.dao.CustomerDAO.getCustomer(CustomerDAO.java:20)
at com.bertrams.bertweb.delegate.CustomerDelegate.getCustomerBaskets(CustomerDelegate.java)
at com.bertrams.bertweb.controller.action.ShoppingBasketAction.list(ShoppingBasketAction.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:41)
at java.lang.reflect.Method.invoke(Method.java:386)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java)

Name and version of the database you are using:
db2/400 v5r1

The generated SQL (show_sql=true):

Hibernate: select baskets0_.SBACCT as SBACCT__, baskets0_.SBBRAN as SBBRAN__, baskets0_.SBUUID as SBUUID__, baskets0_.SBACCT as SBACCT0_, baskets0_.SBBRAN as SBBRAN0_, baskets0_.SBUUID as SBUUID0_, baskets0_.SBDESC as SBDESC0_, baskets0_.SBTYPE as SBTYPE0_, baskets0_.SBTYPEID as SBTYPEID0_, baskets0_.SBSTAT as SBSTAT0_, baskets0_.SBCDAT as SBCDAT0_, baskets0_.SBCUSR as SBCUSR0_, baskets0_.SBCPGM as SBCPGM0_, baskets0_.SBMDAT as SBMDAT0_, baskets0_.SBMUSR as SBMUSR0_, baskets0_.SBMPGM as SBMPGM0_ from BBSHBM baskets0_ where baskets0_.SBACCT=? and baskets0_.SBBRAN=? and baskets0_.SBSTAT='OPN' order by baskets0_.SBDESC

Debug level Hibernate log excerpt:
[/b]
Top
 Profile  
 
tamersalama
 Post subject:
PostPosted: Wed Jan 26, 2005 3:24 am 
Newbie

Joined: Wed Jan 26, 2005 3:04 am
Posts: 3
As you know by now, this is seemingly fixed in DB2/400 V5R2.


The problem was because Hibernate "auto generated" alias for the column in the select clause is not used in the ORDER BY clause (as expected by DB2).

It seems like a DB2/400 V5R1 limitation (add this to the lack of Autogenerated IDs, Recursive Queries, Resultset Offest,... and the list goes on).


Regards,

Tamer
Top
 Profile  
 
tamersalama
 Post subject:
PostPosted: Wed Jan 26, 2005 3:30 am 
Newbie

Joined: Wed Jan 26, 2005 3:04 am
Posts: 3
Ah! forgot to mention a possible work around (ORDER BY problem in DB2/400 V5R1-):

Use the column sequence in the ORDER BY clause:

Code:
from Wrmst as wrmst order by 1 asc


This is only valid when using HQL rather than Criteria API.

Regards,

Tamer
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.