jboss-3.2.0 cmp order by bug ?

[kryptontri]

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 ?

[raja05]

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

[kryptontri]

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

[jonlee]

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?

[kryptontri]

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.

[jonlee]

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.

[kv_moj]

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.

[kv_moj]

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.

[raja05]

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.

[jonlee]

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.

[kv_moj]

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.

[kv_moj]

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.

[jonlee]

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.

[kv_moj]

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.

[kv_moj]

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.