Hi all, I am having trouble to generate case insensitive LIKE sql clauses with ojb. The SQL I want to produce in the end should look something like this: SELECT * FROM <table> WHERE UPPER(<column>) LIKE UPPER(<user_input>); Until now I used the solution suggested in loads of forums and mailing lists to use java.lang.String.toUpperCase(): Criteria criteria = new Criteria(); criteria.addLike("UPPER(column_name)","%"+(user_input.replace(' ','%')).toUpperCase()+"%"); ReportQueryByCriteria q = QueryFactory.newReportQuery(table_name.class, criteria,true); This solution however is not satisfactory as the db function UPPER and the java.lang.String method toUpperCase() are two totally different funtions and potentially return different results for the same input (ie special characters). Therefore I tried to use Criteria.addSQL() instead of Criteria.addLike() in order to get the 2nd UPPER into the resulting SQL: Criteria criteria = new Criteria(); criteria.addSql("UPPER(column_name) Like UPPER('" + <user_input> + "')"); ReportQueryByCriteria q = QueryFactory.newReportQuery(table_name.class, criteria,true); This works fine in the sense that the resulting SQL looks as required, however only as long as the user does not input anything out of the ordinary. If the <user_input> string contains for example a ' character the above code will result in SQL code that produces errors when executed. So the problem is that I need to escape the user input before using it for db lookups and would preferably like to use the build-in escape funtionality of Criteria.addLike(). However I also need to do a case insensitive search (and it is also no option to update the strings in the db to be all upper case) which I only seem to be able to achieve by using addSQL(). Does anyone know a solution to this problem? Any help appreciated. Thanks a lot in advance. Regards Thomas HOTEL RESERVATION SERVICE Robert Ragge GmbH
Blaubach 32 | 50676 Koln | Germany www.HRS.de | www.HRS.com Geschaftsfuhrer: Robert Ragge AG Koln HRB 6099 --------------------------------------------------------------------- HRS - HOTEL RESERVATION SERVICE gewinnt den eco-Award 2007. Damit wird die Hotelplattform erneut als bestes Webportal fur Geschaftsreisende ausgezeichnet. Weitere Informationen... www.hrs.de/showDynamic.do?treeID=503107 ---------------------------------------------------------------------