I have a working connection and the following works
String sql = "SELECT * FROM table WHERE field LIKE 'A%'";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();

I do however want to be able to use LIKE without having to escape the search criteria myself. Using ps.setXXX(x,y) instead i have tried and failed to get the following example to work;

String sql = "SELECT * FROM table WHERE field LIKE ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "A%");
ResultSet rs = ps.executeQuery();

Why? Please help!

ps.setString(1, "'" + var + "'");

Sorry Udayan, that doesn't change the result. Besides, not having to add the extra ' before and after the [var] is my main reason for using ps.setString.

String sql = "SELECT * FROM table WHERE field LIKE ?;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "A%\"");

It seems that not even this is working...

ps = conn.prepareStatement("SELECT * FROM table WHERE fieldName LIKE ?");
ps.setString(1, "Fubar");

Might I have some weird config problem? Running Java 1.4.2 on Win XP with MS SQL 2000 desktop and connecting via JdbcOdbc.

As stated before, the connection is live and working with other queries.

I guess the prepared statement doesn't work for the clauses of type "like" or "in".

I got bitted by "in" for sure and I suspect it is case for "like" too.

ex: select * from employee where employee_id in (?)
String a = "1234"
pstmt.setString(1, a) works fine.

String b = "1234, 4567, 8590"
pstmt.setString(1, b) doesn't work

if you set a single value to ? then it works fine.. but if you set a value which is a concatenated comma separted string it doesn't work

% is used as a wildcard in every SQL implementation I have seen. I believe its in the ANSI SQL Standard.

so when you do your do the following

String whereValue = '%myVal%'

pstmt.setString(1,whereValue);


only do the % where you need it for your search. Doing percents needlessly in front and back can hurt performance, since DBs need to do more protracted index range scans. They don't kill you, so you can use them, but only where necessary.

Hi,

Why don't you try using the CONCAT() function, like in :

select * from <table_name> where <field_name> LIKE  CONCAT('%', ? ,'%');

where <table_name> and <field_name> need to be substituted for appropiate values.  Think about it. Suppose you have a table name USERS with a field FULL_NAME containing their name.

Then if you want to find out how many users called John you have in your table, you will do something like.

SELECT * FROM USERS WHERE FULL_NAME LIKE CONCAT('%', ? , '%');

? --> John, Mark, etc.

regards,

Omar V.M.