Problem with preparedStatement

Question

Excerpt from code

PreparedStatement preparedStatement =  connection.prepareStatement("SELECT * FROM sch.tab1 where col1 like lower ( 'ABZ' ) ");
preparedStatement.executeQuery();

The above code executes successfully.

But when i try to execute this

PreparedStatement preparedStatement =  connection.prepareStatement("SELECT * FROM sch.tab1 where col1 like lower ( ? ) ");
preparedStatement.setString ( myValue );
preparedStatement.executeQuery();

It throws an exception."STRING TO BE PREPARED CONTAINS INVALID USE OF PARAMETER MARKERS"

What could be the problem here?

---

Answer found, see the comments

Answer 1

I suspect the problem is that you can't apply functions directly to parameters. Is there any particular reason why you want the lower casing to be performed at the database rather than in your code? (I can think of some potential reasons, admittedly.) Unless you really need to do this, I'd just change the SQL to:

SELECT * FROM sch.tab1 where col1 like ?

and call toLower() in Java, preferably specifying the appropriate locale in which to perform the lower-casing.

Answer 2

I think Carlos is on to something. Try

SELECT * FROM sch.tab1 where col1 like lower ( '' + ? )

or whatever passes for string concatenation operator in your version of SQL. Forcing a string context might get you past the error. May require extra parentheses.