Hibernate version: 3.0.2

Name and version of the database you are using:DB2 v7

OK, so the bottom line, per a previous thread I posted is that Criteria based queries with Expression.ilike on VARCHAR fields that hold values greater than 255 characters in DB2 v7 tank out because the LOWER() function used to perform the case insensitive searching fails due to:

SQL0171N The data type, length or value of argument "1" of routine "LCASE" is incorrect. SQLSTATE=42815

The DBAs recommend nixing Hibernate and doing straight JDBC to grab result sets and use Java based text parsing (regex or otherwise) to perform the case insensitive matching. Obviously grabbing the entire table for every search is not a solution I am willing to implement because DB2 v7 LOWER can't handle large values, so any H3 gurus have any recommendations on achieving case insensitive searching in DB2 on fields with values larger than 255 characters?

*shameless bump*

Anyone have any suggestions on an implementation for this issue?

I've not worked with DB2, but is it possible to implement your own user functions? Suppose, for example, you create your own ILIKE( VARCHAR, VARCHAR ) function that returned 1 if the comparison matches, and 0 if not. You could then modify your Criteria query as follows:



- Jesse