2 Replies Latest reply on Sep 10, 2002 10:07 PM by hui lan

    Order by error in jboss-ql (CMP2.0)

    hui lan Newbie

      Im working on CMP2.0 under jboss3.0 and oracle8. I face certain order by with jboss-ql. The situation is as following:

      1. SELECT DISTINCT OBJECT(c) FROM CurrencyBean c order by c.CcyID. The CcyID is primary key n it executes successfully.
      2. SELECT DISTINCT OBJECT(c) FROM CurrencyBean c order by c.CcyName. The CcyName is one of the field, the execution cause the following error:

      2002-09-10 10:02:00,657 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.CurrencyBean.findAll] Executing SQL: SELECT DISTINCT t0_c.CCY_ID FROM SWCURRENCY t0_c ORDER BY t0_c.CCY_NAME ASC
      2002-09-10 10:02:00,696 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.CurrencyBean.findAll] Find failed
      java.sql.SQLException: ORA-01791: not a SELECTed expression

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2110)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:365)
      at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:289)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:107)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:40)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:472)
      at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:348)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:323)
      at org.jboss.ejb.EntityContainer.find(EntityContainer.java:662)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1116)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:73)
      at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:257)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:215)
      at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:134)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:79)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:44)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:98)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:167)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:52)
      at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:104)
      at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:109)
      at org.jboss.ejb.EntityContainer.invokeHome(EntityContainer.java:487)
      at org.jboss.ejb.Container.invoke(Container.java:726)
      at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1055)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:98)
      at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:102)
      at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:73)
      at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:76)
      at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:185)
      at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:76)
      at $Proxy39.findAll(Unknown Source)
      at com.ib.bean.AllProductInfo.getCurrencyInfo(AllProductInfo.java:47)
      at org.apache.jsp.IB1009$jsp._jspService(IB1009$jsp.java:125)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:371)
      at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:607)
      at org.mortbay.http.HttpContext.handle(HttpContext.java:1387)
      at org.mortbay.http.HttpContext.handle(HttpContext.java:1326)
      at org.mortbay.http.HttpServer.service(HttpServer.java:757)
      at org.jboss.jetty.Jetty.service(Jetty.java:527)
      at org.mortbay.http.HttpConnection.service(HttpConnection.java:742)
      at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:915)
      at org.mortbay.http.HttpConnection.handle(HttpConnection.java:757)
      at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:151)
      at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:287)
      at org.mortbay.util.ThreadPool$JobRunner.run(ThreadPool.java:715)
      at java.lang.Thread.run(Thread.java:484)

      in my jbosscmp-jdbc.xml:


      <query-method>
      <method-name>findAll</method-name>
      <method-params>
      </method-params>
      </query-method>
      <jboss-ql>
      <![CDATA[SELECT DISTINCT OBJECT(c) FROM CurrencyBean c order by c.CcyName]]>
      </jboss-ql>


      in my ejb-jar.xml

      <query-method>
      <method-name>findAll</method-name>
      <method-params>
      </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql></ejb-ql>


      I wondering is this my setting problem or jboss-ql not support such order by method?

      Please help!!!!