1 2 Previous Next 22 Replies Latest reply on May 7, 2003 8:52 AM by krypton

    jboss-3.2.0 cmp order by bug ?

    krypton Novice

      Hi,

      I'm running 3.2.0, a simple order by ..

      ejb-jar.xml:



      Find Supplier by Age

      <query-method>
      <method-name>findByAge</method-name>
      <method-params/>
      </query-method>
      <ejb-ql/>


      jbosscmp-jdbc.xml

      <!-- Over Ride the EJB-QL -->

      <query-method>
      <method-name>findByAge</method-name>
      <method-params/>
      <jboss-ql><![CDATA[
      SELECT OBJECT(s)
      FROM Supplier s
      ORDER BY s.age ASC
      ]]></jboss-ql>
      </query-method>


      Error during deploy:

      21:00:37,863 ERROR [EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error compiling EJB-QL statement ''; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseExc
      eption: FROM not found)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.(JDBCEJBQLQuery.java:46)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createEJBQLQuery(JDBCCommandFactory.java:53)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.start(JDBCQueryManager.java:218)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:505)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:410)
      at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:170)
      at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:339)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor163.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy12.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy569.start(Unknown Source)
      at org.jboss.ejb.EjbModule.startService(EjbModule.java:347)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy12.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy21.start(Unknown Source)
      at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:541)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
      at sun.reflect.GeneratedMethodAccessor24.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy7.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:280)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:421)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:200)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:211)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:190)
      Caused by: org.jboss.ejb.plugins.cmp.ejbql.ParseException: FROM not found
      at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.parse(EJBQLParser.java:38)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler.compileEJBQL(JDBCEJBQLCompiler.java:121)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.(JDBCEJBQLQuery.java:40)
      ... 57 more

      I've looked at the CMP doco, can't see and its basically the same methodology w.r.t overriding ejb-ql by jboss-ql. I've even tried using <declared-ql> etc ...

      Can anyone show me what i am doing wrong ? The error message indicates 'FROM' .. but the table is named correctly, other methods work fine, that is the ones that are not overridden.

      As soon as i use the ejb-ql delaration with a simple select etc, without the order by clause, all deploys fine. I feel like bashing my pc with a hockey stick right now :-(

      Am i missing something fundamental ?

        • 1. Re: jboss-3.2.0 cmp order by bug ?
          Raja Master

          Hi
          This is the relevant portion of code from the EJBQLParser


          String lowerCase = ejbql.toLowerCase();
          int fromBeginIndex = lowerCase.indexOf("from");
          if(fromBeginIndex < 0) {
          throw new ParseException("FROM not found");
          }


          So you need to put in a dummy value for EJB-QL(with a FROM clause) and override this with the actual query in JBossQL. This way u wud get the result you want, also making JBoss happy.

          Spare your PC for today ;-)

          -Raj

          • 2. Re: jboss-3.2.0 cmp order by bug ?
            krypton Novice

            Hi, I'm still have issues with ORDER BY, my results are not ordered at all, even though jboss deploys the ear correctly without any warnings now, it seems jboss-ql is not even being invoked ?

            ejb-jar.xml



            Find Supplier by Age

            <query-method>
            <method-name>findByAge</method-name>
            <method-params/>
            </query-method>
            <ejb-ql>
            <![CDATA[
            SELECT OBJECT(s)
            FROM Supplier s
            ]]>
            </ejb-ql>


            jbosscmp-jdbc.xml

            <!-- Over Ride the EJB-QL -->

            <query-method>
            <method-name>findByAge</method-name>
            <method-params/>
            <jboss-ql><![CDATA[
            SELECT OBJECT(s)
            FROM Supplier s
            ORDER BY s.age DESC
            ]]></jboss-ql>
            </query-method>


            Any ideas about this, perhaps I have missed something fundamental ?

            TIA k

            • 3. Re: jboss-3.2.0 cmp order by bug ?
              Jon Barnett Master

              We don't use that many CMPs but here are a few questions so we might understand the circumstances:
              1) Did this work in a previous JBoss?
              2) Are there any restrictions in the underlying database?
              3) What is the underlying database?

              • 4. Re: jboss-3.2.0 cmp order by bug ?
                krypton Novice

                Hi jonlee,

                I did not try this with other versions. I will later tonight. The underlying database is MySQL. Even the server logs which are on DEBUG do not contain order by clauses when the query is invoked. :-(

                I'll keep looking around + try this on another version.
                TIA k.

                • 5. Re: jboss-3.2.0 cmp order by bug ?
                  Jon Barnett Master

                  Your problem seems similar to this problem:
                  http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= tests performed here seem to show some sort of problem perhaps with JBoss. However, I'm not absolutely convinced as the generated SQL works ok on the mySQL command line and as far as I can see in a quick scan of the JBoss code, this gets passed as is through to the driver connection pool. One thing to test is whether the SQL qorks through your mySQL jdbc driver. Just a thought.

                  One other thing to note but won't effect CMP I don't think - some DBs like DB2 don't like the distinct tag if the table has a binary column.

                  • 6. Re: jboss-3.2.0 cmp order by bug ?
                    Kayvan Mojarrad Newbie

                    Ok,
                    I tried running a jdbc client using the same driver and this works fine.
                    I also tried taking the distinct tag on and off and this makes no difference.

                    mmmm.

                    What's interesting is that I'm sure this was working fine a while back. I know that I recently upgraded to version 3.0.4, but when I test older and newer versions of jboss (3.0.2 - 3.0.6 - 3.2.0) I get the same problem, which suggests to me that the problem is not so much with jboss as is either with coding or config.

                    I'll keep at it and will post anything I find.

                    kv.

                    • 7. Re: jboss-3.2.0 cmp order by bug ?
                      Kayvan Mojarrad Newbie

                      I still haven't found out why this is happening but just wanted to update the thread with my latest test.
                      I tried using a the jdbc client from within a stateless session bean, and obtaining the DataSource from the jboss pool.
                      This also worked correctly which I guess eliminates the possibility that the problem is with the driver or the connection pool.
                      I'd really appreciate some input here.
                      Even some confirmation that ORDER BY works perfectly ok for other users.
                      Many thanks.
                      kv.

                      • 8. Re: jboss-3.2.0 cmp order by bug ?
                        Raja Master

                        Can you turn on the "DEBUG" option for logging and see what SQL is getting generated. That should tell us if there is a bug in Jboss code picking up the correct Query.

                        • 9. Re: jboss-3.2.0 cmp order by bug ?
                          Jon Barnett Master

                          I realise that you have already turned the DEBUG on and we saw the SQL that looked OK.

                          Now, when you executed the test in the stateless session bean, did you use a JDBC query invocation or did you execute a prepared statement?

                          It is probably going to take me a couple of weeks to get around to building a Postgresql Entity Bean - I've got a bundle of client evaluations and a friend's wedding to deal with. Hopefully, someone else can help here in the meantime.

                          • 10. Re: jboss-3.2.0 cmp order by bug ?
                            Kayvan Mojarrad Newbie

                            Hi raja,
                            Thanks for responding.
                            I've already done what you ask - I started with this problem in another thread:
                            http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= fact is that jboss generates the correct SQL statement and this returns the correct result on the mySQL command line.
                            I've also performed some other tests from which I've posted the results in this thread.
                            I take it you're not experiencing the same problem, and ORDER BY works ok in your app?

                            Thanks again,
                            kv.

                            • 11. Re: jboss-3.2.0 cmp order by bug ?
                              Kayvan Mojarrad Newbie

                              Hi jonlee,
                              As it happens I used a JDBC query invocation and not a prepared statement. This I will do now as I realize Jboss uses a prepared statement invocation.
                              I'll post my findings soon.
                              Thanks,
                              kv.

                              P.S. Have loads of fun at the wedding. Don't forget to do everything I wouldn't do.

                              • 12. Re: jboss-3.2.0 cmp order by bug ?
                                Jon Barnett Master

                                Just another question on this. Does the record order change in the CMP with ORDER BY and without ORDER BY? Just trying to see if the ORDER BY clause has any effect whatsoever.

                                • 13. Re: jboss-3.2.0 cmp order by bug ?
                                  Kayvan Mojarrad Newbie

                                  If I call findAll method on the home interface I get the results ordered by primary key.
                                  Calling the DQL method with no ORDER BY clause or ORDER BY <any column path> ASC returns the same results which appears to be of an undefined order. And when called with ORDER BY <any column path> DESC always the last 2 rows are flipped around with no regard to their order of ascension.

                                  • 14. Re: jboss-3.2.0 cmp order by bug ?
                                    Kayvan Mojarrad Newbie

                                    Just did the JDBC client test using PreparedStatement - This also returned the correctly ordered results.
                                    Also I should say that although within the app I use LinkedHashMaps to buffer results, for the sake of these tests, I've been simply iterating over the Collections that are returned by the CMP engine and printing results on to the console.

                                    1 2 Previous Next