case insensitive LIKE and addLike vs addSQL

["Geike, Thomas" <Th...@hrs.de>]

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


---------------------------------------------------------------------

Re: case insensitive LIKE and addLike vs addSQL

[Armin Waibel <ar...@apache.org>]

Hi Thomas,

Geike, Thomas wrote:
> 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>);
....
> Does anyone know a solution to this problem?
> Any help appreciated. Thanks a lot in advance.

Sorry no. Currently OJB doesn't analyze query parameter values (e.g. 
"LIKE UPPER(<user_input>)") for sql-functions.
By the way, is it allowed to use function expressions as parameter value 
in a PreparedStatement?

regards,
Armin


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org