PreparedStatement using LIKE

[Javoso Torso]

Hello,

I�m tired of searching the internet for this one


using a PreparedStatement with the query

   ...
String theQuery = "select c1 from t1 where c2 = ? and c3 like ?";
String scpChar = "%";
   ...
ps.setString(1, str1);
ps.setString(2, (scpChar + str2));
   ... 

I also tried other similar ways like:
ps.setString(2, "%" + str2);
ps.setString(2, '%' + str2);
I am not having the right result. I tested with a DB-Tool + I am sure it is not working.

Jamie Robertson wrote in Jamie Comment:

keep in mind that every driver/database combo deals with special characters differently. Some escape all special characters, some escape only single quotes. So you'd have to find out from your jdbc driver documentation how your specific driver handles the special character %. If it escapes it, your search is looking for the % literally. If it doesn't escape it, then it will work as the intended wildcard. The same code used in the example you've linked to works fine as a wildcard search using classes12.zip Oracle drivers to an Oracle 8.1.7 database.

I think that is my case: the code is taking % literally and not using it as a wildCard

the driver is sun.jdbc.odbc.JdbcOdbcDriver
SDK 1.4.1_01
database = sql server

anyone knows how can I deal with it...

thanks + regards,
Javoso
[ August 03, 2004: Message edited by: Javoso Torso ]

[Javoso Torso]

got something work!:

  
ps.setObject(2, '%' + str2, java.sql.Types.VARCHAR);

there are rumors that some mssql drivers have a bug that makes setString() not work with "LIKE"

So, that's my contribution for all the people looking for hours for a solution...

Regards,
Javoso
[ August 03, 2004: Message edited by: Javoso Torso ]

[David O'Meara]

Thanks for posting the follow-up!