How to ORDER BY case-insensitively using jboss-ql?

[hakucho]

I'd like to be able to find entities in a case-insensitive order. In normal JDBC I would do something like

select * from Employee order by upper(Employee.FirstName)

so in jboss-ql I tried this:

SELECT DISTINCT OBJECT(p)
FROM PersonSchema p
ORDER BY UCASE(p.firstName)

However at deploy time I got this error:

14:49:44,919 ERROR [EntityContainer] Starting failed jboss.j2ee:jndiName=local/Person,service=EJB
org.jboss.deployment.DeploymentException: Error compiling JBossQL statement 'SELECT DISTINCT OBJECT(p)
 FROM PersonSchema p
 WHERE p.lastName = ?1
 ORDER BY UCASE(p.firstName)'; - nested throwable: (org.jboss.ejb.plugins
.cmp.ejbql.ParseException: Encountered "UCASE" at line 4, column 58.
Was expecting one of:
 <NUMERIC_VALUED_PATH> ...
 <STRING_VALUED_PATH> ...
 <DATETIME_VALUED_PATH> ...
 )
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.<init>(JDBCJBossQLQuery.java:52)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createJBossQLQuery(JDBCCommandFactory.java:72)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.start(JDBCQueryManager.java:260)
etc. etc.

Does jboss-ql not support the UCASE function in ORDER BY clauses? It seems to work fine in WHERE clauses.

Also, is there a JBoss-ql reference somewhere? I looked through the online docs (http://docs.jboss.org/jbossas/jboss4guide/r2/html/ch11.chapter.html#ch11.queries.sect) but couldn't find anything definitive, only some examples.

Thanks for any help.

[aloubyansky]

Unfortunately, not supported.

[fbiaggi]

Hi,
you may try the declared-sql

[hakucho]

Yes, I got it to work using declared SQL, but this is not ideal, because it uses RDBMS-specific SQL in the jbosscmp-jdbc.xml file. If it could be done in jboss-ql, that would keep my JAR file independent of the DB vendor.

[hakucho]

Logged as JBAS-1390, in case someone finds it useful.