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:
<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():
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]
Tuesday, November 23, 2004
Subscribe to:
Post Comments (Atom)
Subscribe To as400 blog
Posts
Comments
No comments:
Post a Comment