Case-insensitive queryByExample on TEXT field in SQLServer

TAnnison · **Joined:** Thu Dec 08, 2005 11:44 am **Posts:** 5

Hiya!

I have some Hibernate code that runs over several database alternatives.

It includes one file (represented by MessageDTO) that has a TEXT type field, for holding large volumes of textual data.

Code:

  <property name="messageSOAPBody" column="MessageSOAPBody" type="text" length="2147483647">
        </property>

We want to search over that file without case sensitivity, and we have a QueryByExample for it like this :

Code:

            final Example example = Example.create(exampleDTO).excludeZeroes()
                    .ignoreCase().enableLike(MatchMode.ANYWHERE);
            final Criteria criteria = session.createCriteria(
                    MessageDTO.class).add(example);
            return criteria.list();

This works perfectly for a MySQL database, but fails on a SQLServer database, with this error :

Code:

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL 
[select this_.MessageID as MessageID1_0_, this_.ProcessedDateTime as Processe2_1_0_, this_.MessageDirection as MessageD3_1_0_, this_.MessageAction as MessageA4_1_0_, this_.MessageTarget as MessageT5_1_0_, this_.MessageOrigin as MessageO6_1_0_, this_.MessageSOAPHeader as MessageS7_1_0_, this_.SearchKeys as SearchKeys1_0_, this_.MessageSOAPBody as MessageS9_1_0_ from MESSAGE this_ where (lower(this_.MessageSOAPBody) like ?)]; SQL state [S1000]; error code [8116]; 
Argument data type text is invalid for argument 1 of lower function.;
nested exception is java.sql.SQLException: Argument data type text is invalid for argument 1 of lower function.
java.sql.SQLException: Argument data type text is invalid for argument 1 of lower function.

Which Googling tells me is probably because LOWER is an issue on SQLServer, and

Code:

        SELECT LOWER(data) 
must be replaced by : 
        SELECT LOWER(SUBSTRING(data,1,DATALENGTH(data)) 

But this is in a QueryByExample, so I guess this is buried in Hibernate's code somewhere, triggered by the .ignoreCase() that's in our example builder. So, how do I get Hibernate to take this need for special handling into account?

Any help would be much appreciated!