-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Case-insensitive queryByExample on TEXT field in SQLServer
PostPosted: Tue Feb 19, 2008 7:38 am 
Newbie

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!

_________________
Cheers
Tracey


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.