How do I place an IN parameter with a LIKE with a RowSet?

Question

I have fighting to get a IN parameter to work inside of a LIKE statement now for hours! I am using a CachedRowSet, which I understand should follow the same rules as a PreparedStatement.
Here is the basic query:

CachedRowSet cache;
String sql = "SELECT x " +
                "FROM   Y " +
             "WHERE z LIKE '?__'" 

cache.setCommand(sql);
cache.setString(1, "someString");

someString is a known id but the database( by the way is PostgreSQL) entry has a unknown 2 char suffix.

score 5 · Accepted Answer · answered Jan 29 '09 at 03:51

Parameter placeholders inside quotes are ignored. They're interpreted as a literal "?". If you use a parameter, you must put the placeholder outside quotes in the SQL expression.

But LIKE can be compared to any string or any expression that produces a string. For example:

SELECT x FROM y WHERE z LIKE (? || '__')

Now you could supply "someString" for the parameter, and then it will be concatenated with the constant string '__'.

How do I place an IN parameter with a LIKE with a RowSet?

1 Answers1