How do I declare a String array in a Java SQL declareParameter call?

Question

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.

Community · Accepted Answer · 2017-05-23 10:34:21Z

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.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Aug 5, 2011 at 18:53

Codo

79.6k18 gold badges176 silver badges218 bronze badges

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

Comments

Basanth Roy · Accepted Answer · 2011-08-05 18:27:26Z

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.

answered Aug 5, 2011 at 18:27

Basanth Roy

6,5305 gold badges27 silver badges25 bronze badges

2 Comments

Codo Over a year ago

It can be done in Oracle. See stackoverflow.com/questions/5198856/… and stackoverflow.com/questions/6155146/….

Basanth Roy Over a year ago

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.

Collectives™ on Stack Overflow

How do I declare a String array in a Java SQL declareParameter call?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related