JDBC Exception when setting parameters on a prepared statement

Question

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 ..

Are you calling statement.setLong(i, lp.longValue()) in a loop, with i being an int starting at 0? Bind variables are indexed from 1, not 0. — ernest_k
– ernest_k, Commented Oct 15, 2021 at 4:51

Tim Biegeleisen · Accepted Answer · 2021-10-15 05:04:20Z

2

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.

answered Oct 15, 2021 at 5:04

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Luke Woodward Over a year ago

"Your current WHERE IN tuple syntax is not valid for Oracle": that syntax works fine for me, at least with Oracle 18c XE.

Tim Biegeleisen Over a year ago

@Luke Not working for 11g, at least not when I tested earlier today.

Dmitriy Ryabin Over a year ago

Thanks for the comment. The (1.) I can't use the method suggested because, I don't know how many tbl.fields I have ahead of time. 2 table.field and 4 ? placeholders are only an example .. At least, it would not be an easy change in a program to parse and deal with loops, etc... (2.) I can run the construct from Oracle SQL Developer. So, I could understand the issues with different versions of Oracle, but ... I can't agree with it not working in Oracle.

Tim Biegeleisen Over a year ago

@DmitriyRyabin To the contrary, you can fairly easily write a flexible prepared statement in Java, which generates the expression you want.

Collectives™ on Stack Overflow

JDBC Exception when setting parameters on a prepared statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related