jboss-3.2.0 cmp order by bug ?| JBoss.org Content Archive (Read Only)

15. Re: jboss-3.2.0 cmp order by bug ?

kv_moj Apr 29, 2003 11:31 AM (in response to kryptontri)

Ok,
I have some answers now but still no solution.
It seems that all is well with ORDER BY when I use a java.util.Collection as the return type of my DQL method. I can see now that this problem began when I needed a distinct result and to achieve this I changed the return type of my method to java.util.Set.
My tests with the JDBC standalone client revealed that the DISTINCT tag on the SQL statement is not what is causing this problem so I guess this has something to do with Jboss after all. ????
I'd really appreciate some confirmation of this from other users.
If you have a finder or ejbSelect method that returns java.util.Collection, try changing it to a java.util.Set and see if ORDER BY clause continues to work correctly.

Thanks,
kv.

16. Re: jboss-3.2.0 cmp order by bug ?

kv_moj Apr 29, 2003 5:07 PM (in response to kryptontri)

Now I also have the solution.
Use the DISTINCT tag in the original DynamicQL statement and leave the return type as java.util.Collection.
So simple - I can't believe it's taken me a week to get here.

kv.

17. Re: jboss-3.2.0 cmp order by bug ?

kryptontri May 7, 2003 6:06 AM (in response to kryptontri)

Hi I tried the same application under jboss3.2.1, here is my config
jdk 1.4.1_o2
win 2000
MSQL Database 3.23.54

Jboss deploys the app fine. But order by clauses still do not work. The log files which are on DEBUG show the generated SQL, and there is no ORDER BY clause appended to the query.

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
-----------------------


<query-method>

Find Supplier by Age

<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>

log file
---------
[uk.co.myapp.proxy.SupplierManagerBean] ####### INVOKING HOME ####### findByAge
2003-05-07 12:50:15,308 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.SupplierBean.findByAge] Executing SQL: SELECT t0_s.supplierId FROM SUPPLIER t0_s
2003-05-07 12:50:15,318 INFO [uk.co.studbook.proxy.SupplierManagerBean]:convertSupplierLocalToModel

Seems that jboss-ql is ignored completely and the select from the ejb-jar is called only. Any ideas people ? This order by issue is holding me up before i can go live .. ?

TIA k

18. Re: jboss-3.2.0 cmp order by bug ?

kryptontri May 7, 2003 6:08 AM (in response to kryptontri)

Hi I tried the same application under jboss3.2.1, here is my config
jdk 1.4.1_o2
win 2000
MSQL Database 3.23.54

Jboss deploys the app fine. But order by clauses still do not work. The log files which are on DEBUG show the generated SQL, and there is no ORDER BY clause appended to the query.

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
-----------------------


<query-method>

Find Supplier by Age

<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>

log file
---------
[uk.co.myapp.proxy.SupplierManagerBean] ####### INVOKING HOME ####### findByAge
2003-05-07 12:50:15,308 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.SupplierBean.findByAge] Executing SQL: SELECT t0_s.supplierId FROM SUPPLIER t0_s
2003-05-07 12:50:15,318 INFO [uk.co.studbook.proxy.SupplierManagerBean]:convertSupplierLocalToModel

Seems that jboss-ql is ignored completely and the select from the ejb-jar is called only. Any ideas people ? This order by issue is holding me up before i can go live .. ?

TIA k

19. Re: jboss-3.2.0 cmp order by bug ?

kv_moj May 7, 2003 7:47 AM (in response to kryptontri)

Hi,
I can't be sure exactly what your problem is but looking at your initial posting on this thread, it looks like jboss is not overriding the query definition in ejb-jar.xml with the one you're providing in jbosscmp-jdbc.xml. This can be the result of spelling mistakes or method signatures (perhaps you have overridden the method in which case you should use the <method-params> tag to describe the correct method).
The clue here is the original Exception which you get in the first posting.
I simply leave the <ejb-ql/> tag empty and as jboss is able to find the overriding <jboss-ql/>, the app deploys with no Exception.

ejb-jar.xml
--------------

Find Supplier by Age

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

20. Re: jboss-3.2.0 cmp order by bug ?

kv_moj May 7, 2003 7:52 AM (in response to kryptontri)

Ok,
Having looked at your original posting, I believe I can now see your typo.
It's in your jbosscmp-jdbc.xml

jbosscmp-jdbc.xml



<query-method>
<method-name>findByAge</method-name>
<method-params/>
***** MOVE </query-method> TO HERE ********
<jboss-ql><![CDATA[
SELECT OBJECT(s)
FROM Supplier s
ORDER BY s.age ASC
]]></jboss-ql>
</query-method> ***** THIS TAG IS IN THE WRONG PLACE

It's a bit strange that the paser validation doesn't pick these errors up - I know.

kv.

21. Re: jboss-3.2.0 cmp order by bug ?

kryptontri May 7, 2003 8:39 AM (in response to kryptontri)

Hi KV,

thanks for the help, I'll make the mod's when in a few hours and post my findings ..

Thanks again.
K

22. Re: jboss-3.2.0 cmp order by bug ?

kryptontri May 7, 2003 8:52 AM (in response to kryptontri)

KV, it WORKS!!! Thanks so much !
Thankfully the changes are minimal ! I can see in the logs that ORDER BY is now part of the generated sql.

Many Thanks Again!
K