0

Simplified for illustrative purposes:

String[] filter = {"foo", "bar"};
String sql = "SELECT * FROM table WHERE column IN ?";
declareParameter(new SqlParameter(Types.ARRAY));

This doesn't work when I execute it (I get a java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s). exception.

Andrew
  • 1,590
  • 4
  • 19
  • 25

2 Answers2

1

JDBC doesn't support this kind of query officially and SQL Server doesn't either as far as I know. This topic has been discussed on SO many times and several workarounds have been proposed:

You can probably find many more as it is indeed a very relevant yet still open topic.

Community
  • 1
  • 1
Codo
  • 75,595
  • 17
  • 168
  • 206
0

I am not sure about MS SQL Server. But such a code wouldn't work on oracle DB. In oracle, we cannot pass a java array to an IN clause. The way to work around that limitation is to construct a PL/SQL function that converts a list of strings into a table, something like stringToTable. Then pass a concatenated string as a parameter.

Again, this answer is applicable for Oracle DB; It might not work on MS SQL server.

Basanth Roy
  • 6,272
  • 5
  • 25
  • 25
  • It can be done in Oracle. See http://stackoverflow.com/questions/5198856/pass-array-to-oracle-procedure and http://stackoverflow.com/questions/6155146/problem-using-oracle-parameters-in-select-in/6155801#6155801. – Codo Aug 05 '11 at 18:33
  • I did not say that it cannot be done in oracle. I just said that you cannot directly pass a java array into a PL?SQL array without doing some intermediate processing or mapping. Your links provide one way to do it. The answer I gave is another way that I have done in my code. – Basanth Roy Aug 05 '11 at 18:42