permaculture playing cards*
The moose likes Object Relational Mapping and the fly likes [JPA/TOPLINK] is the function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "[JPA/TOPLINK] is the function "lower" supported in "order by" clause? if not, how...." Watch "[JPA/TOPLINK] is the function "lower" supported in "order by" clause? if not, how...." New topic
Author

[JPA/TOPLINK] is the function "lower" supported in "order by" clause? if not, how....

Edward He
Greenhorn

Joined: Mar 17, 2004
Posts: 7
In EJB-QL
I can use lower() or upper() in where clause.
But there is always a parse exception thrown when i tried to use it in main clause or order by clause.

works:
select s from Student s where lower(s.name) like 'm%'

exception thrown:
select s from Student s where s.name like 'm%' order by lower(s.name)

OR
----------------------------------------------------------------------------Why i am asking this is that the resultset returned from database is not alphabetical sorted but ascii sorted, which means any characters with upper case is always prior to the ones with lower case. i.e. Zac is prior to billy in ascending order which is not acceptable.

If EJB-QL doesn't support using lower() in order by clause, do I have any other options to avoid this problem?

BTW, it is the Oracle 10g we are using as DB
TOPLINK as the ORM framework

many thanks,
Edward
[ November 29, 2007: Message edited by: Edward He ]
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
My guess is that the JPQL grammar does not allow this. However TopLink still should. I logged this issue internally in Oracle's bug database, feel free to log a bug on Glassfish if using TopLink Essentials, or Eclipse if using EclipseLink, or contact Oracle technical support.

As a work around you could use a TopLink Expression query, or use a native JPA query, or sort in memory.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Edward He
Greenhorn

Joined: Mar 17, 2004
Posts: 7
Thanks a lot James,
I will take a look at Toplink expression query, i guess it should do the trick
Edward He
Greenhorn

Joined: Mar 17, 2004
Posts: 7
Hi James,
is there a public access to the toplink bug tracking system?
There are few people in my organization who also interested in the defect you reported.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
The TopLink Essential bug tracking system is public, and the EclipseLink bug tracking system is public. You can access the Oracle TopLink bug tracking system through Oracle support. You can see some of the published bugs through Oracle meta-link, but I did not publish the bug.
Edward He
Greenhorn

Joined: Mar 17, 2004
Posts: 7
We found another solution for this issue.
by changing the oracle system setting parameter "nls_sort"

change it to nls_sort = BINARY_AI will solve the puzzle
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
BTW, James is correct that JPA does not support using string functions in the ORDER BY clause of JPQL queries. This is outside the realm of the spec.


-Mike
Pro JPA 2: Mastering the Java Persistence API
 
Don't get me started about those stupid light bulbs.
 
subject: [JPA/TOPLINK] is the function "lower" supported in "order by" clause? if not, how....