Prepared statements with % wildcards

[Joe McClelland]

I'm trying to do something a little different here, I'd like to use a prepared statement to implement searching for a user.

My SQL (which doesn't work) looks like:

"SELECT * from users where name like '%?%'"

(postgres 7.4 is the database)

I want to do partial matches here, but it seems to be interpreting the ? as part of the string literal.

As expected, if I do this instead then it works but I force the user to enter % manually which I don't want to do:

"SELECT * from users where name like ?"

I know, I could just use a regular statement, but I'd rather not fumble with string concatenation if at all possible. Then I'd have to check for special charaters such as ' in the input, etc. Too much hassle!

Thoughts anyone?

[Bacon]

hmmmm... I'm not sure what you are trying to do. Can you explain it a bit more?

I would expect "SELECT * from users where name like '%?%'" to return all rows with a "?" in the name column.

[ September 10, 2004: Message edited by: Ray Marsh ]
[ September 10, 2004: Message edited by: Ray Marsh ]

[Joe McClelland]

Hi Ray,

Thanks for the reply.

You are exactly right, "SELECT * from users where name like '%?%'" returns all rows with ? in the name column. It technically works, but just not the way I had hoped.

What I'm trying to do is pad the value from ? with two percent signs on either side. In effect, I was hoping that ? would be replaced with whatever "statement.setString(1, <somevalue> " would set it to, and that the two percent signs would remain allowing me to do a partial match.

But I think ? cannot be used as a literal replacement in any part of the SQL, only as a variable for a conditional statement within a clause. For example, I know that "SELECT * from ? where . . . " doesn't work, you can't use ? to make a table name a variable. I'm thinking what I'm trying to do isn't possible using a prepared statement and that I'll have to use a regular one instead and simply build the string at runtime (yuck!).

Joe.

[Bacon]

Can you show me your code that creates the prepared statement and that sets the statement?

I'm thinking it should work. Try adding a space between the % and the ?, like this ---> % ? % <---- although the space might stay and mess up the select.

Example:
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);

updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");

If I have a chance I'll take a swipe at it. I've never tried to do exactly that before.

[Avi Abrami]

Joe,
Pardon me for stating the obvious, but just tack a "%" character to the start and end of the string entered by the user, and use that as the parameter value to your "PreparedStatement", for example:
[NOTE: Uncompiled and untested.]

String enteredByUser = "whatever";
String forSql = "%" + enteredByUser + "%";
String sql = "select * from USERS where NAME like ?";
Connection con = //however you get it.
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, forSql);

But I think ? cannot be used as a literal replacement in any part of the SQL

Correct, "?" in the SQL string passed to a "PreparedStatement" can only be used anywhere that a literal value can be used.

Good Luck,
Avi.
[ September 10, 2004: Message edited by: Avi Abrami ]

[Joe McClelland]

Wow, I must be more tired than I thought. I had a "visitor" last night and she stayed quite late. Anyway, I guess I was trying too hard to figure out how to accomplish it in SQL, trying also to determine the nature and capabilities of wildcards in prepared statements. That is definitely the obvious solution that I will use.

Thanks Avi and Ray for your responses,

Joe

[Bacon]

Avi nailed it. That's the most obvious answer. However there is no forgiveness for stating the obvious, you must always answer for it!

I can use Joe's excuse too, except it was not for the same reason, I got a support call from work @ 12:45 and got back to bed @ 1:30. So, I'm sleepy too and have no business trying to say anything remotely intelligent.

Glad you got it solved.

[wilson alwaris]

wow! Avi.. that was such a simple effective answer thanks a lot... i am using it for my college project after 7 year :-)