I have a problem with a query that matchs a blob column with a String parameter using the a like sentence in db2:

LOWER(CAST(SUBSTR(detail,1,32000) AS string)) LIKE LOWER(:detail)

I need to do the cast because if I use "detail LIKE BLOB(:detail)" insted, this query is case sensitive and i need a case insensitive query.

This is the xml associated with the blob column:

<property name="detail" type="byte[]">
<column name="DETAIL" not-null="true" />
</property>

When I ejecute the query, the translated sql is LOWER(CAST(SUBSTR(detail,1,32000) AS varchar(255))) LIKE :detail
but I need to cast as varchar(32000).

Does anyone knows how I can do that? If I use a custom type as second argument of the Cast function, hibernate throws NullPointerException

I don't speek English, so If I don't make me understand, I ask for apologies.

Hi,
I have expirience with porting application on DB2. It's night mare :) One of the issue was exaclly the same as you mentioned. There is general rule to not use a markers (':param') as a parameter of DB function. Of course you can use cast but is not clear and maybe cause another problem during maintenance. The best solution is (in your case) use simple marker (:param) and use lower() or upper() method in bustness logic e.g. Java ;)
HTH