Hi

I have a problem with the sorting of data in my database. We have several different customers in several different countries and each of these countries have different sorting. The following specs on the system:

Hibernate: 3.2.0.GA
Application server: Jboss 4.0.5.GA (EJB3)
Runtime: Java 1.5
Database: MySQL 5.0.45
Operationsystem: Linux

What I would like to accomplish is a way of making it possible to set the type of collation I want to use in a specific search. To test this I constructed the following HQL query:

SELECT object(o) FROM UserEntity o INNER JOIN o.person.vehicleUser.statuses s WHERE s.division.id in (:ids) ORDER BY o.person.familyName COLLATE utf8_swedish_ci

Where I later planned to replace utf8_swedich_ci with :collate and using the query.setParameter method to state what collation I wich to use (depending on who is doing the search).

Unfortionally hibernates HQL can't recognize the COLLATE statement.

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: COLLATE near line 1, column 180 [select object(o) from se.pilotfish.fairfleet.user.domain.entity.UserEntity o INNER JOIN o.person.vehicleUser.statuses s WHERE s.division.id in (:ids) order by o.person.familyName COLLATE utf8_swedish_ci]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258


Is there any other way of accomplishing this?

Regards
Martin