I am running JBoss 3.0.3. I have the following finder query in the jbosscmp-jdbc.xml file for a bean named Groups:
<query-method>
<method-name>findAllOrdered</method-name>
<method-params></method-params>
</query-method>
<jboss-ql>SELECT OBJECT(g) FROM Groups g ORDER BY g.groupName</jboss-ql>
I am using Informix as my database server. This is a snippet of the server.log file:
2002-11-25 12:40:41,756 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.GroupsEJB.findAllOrdered] Executing SQL: SELECT t0_g.group_number FROM GROUPS t0_g ORDER BY t0_g.name ASC
2002-11-25 12:40:41,763 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.GroupsEJB] Executing SQL: SELECT group_number,name, description FROM GROUPS WHERE (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?) OR (group_number=?)
The first SQL statement DOES NOT work in Informix!! It will produce an error (SQL error code -309). Anything in the order by clause must be specified in the select list.
The question I have is why is the container even trying to perform an ORDER BY while fetching the primary keys? I would have though that the ORDER BY would be performed on the subsequent query.
I would appreciate it if anyone could het back to me or has come across this before!
Partha Dutta
Bloomberg L.P
