CODEWITHSUNDEEP
javaoracle-databaseexceptionjdbc
Dmitriy Ryabin
Dmitriy Ryabin

Reputation: 468

JDBC Exception when setting parameters on a prepared statement

I would appreciate the help with the following:

I am getting JDBCException when setting parameters onto JDBC prepared statement.

The fragment of the query where the error is looks like this

... AND (tbl1.field1, tbl2.field2) IN ((?, ?), (?, ?)) ...

And my list of parameters is a an ArrayList of values

{123, 235689, 25,2568982}

If I run the query in the SQLDeveloper, substituting values for '?' - I get the right result But, when running in the Java program it throws the JDBCException

An JDBC exception occurred. Statement: SELECT ... FROM .. WHERE ..AND (tbl1.field1, 
           tbl2.field2) IN ((?, ?), (?, ?)) .. Invalid column index

The debugger brings me to the line that does this:

statement.setLong(i, lp.longValue());

where 'statement' is a PreparedStatement ..

Upvotes: 0

Views: 459

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Your current WHERE IN tuple syntax is not valid for Oracle:

AND (tbl1.field1, tbl2.field2) IN ((?, ?), (?, ?))

This only would work on MySQL and a few other databases. You should refactor it to:

WHERE (tbl1.field1 = ? AND tbl2.field2 = ?) OR (tbl1.field1 = ? AND tbl2.field2 = ?)

Then, iterate your list of values and assign the 4 ? placeholders appropriately. As the comment above notes, your exact error could be due to an array index problem, but even if you fix that, the syntax would still fail for Oracle.

Upvotes: 2

Related Questions