4

I have a List of Strings that I want to set as a parameter in a preparedstatement. This question and answer here, makes it look easy:

How to use an arraylist as a prepared statement parameter

Well, not really easy. There is still the conversion of a List, to an SQL-Array, which I found easiest to do by creating a String[] in between.

Below is my code:

PreparedStatement s = con.prepareStatement("SELECT  * FROM Table WHERE Country IN ? ");

String[] countryArray = new String[countryListObject.size()];
countryArray = countryListObject.toArray(countryArray);

Array cArray = con.createArrayOf("VARCHAR", countryArray); //<--- Throws the exception
s.setArray(1, cArray);

This answer Seems to adress a similar problem, but I can't really understand how this helped solve anything. The answer is ambigous at best, stating only that:

Basically what you are wanting to do is not directly possible using a PreparedStatement.

I've come to learn from the API Documentation that this exception is thrown if the JDBC driver does not support this method. I'm running a com.microsoft.sqlserver sqljdbc4 version 3.0. I am trying to see which versions do and don't support setArray, but I can't find the information. It is probably right in front of me, but I would really appreciate a little help on this.

How can I figure out if my JDBC's do support setArray()?

Community
  • 1
  • 1
jumps4fun
  • 3,994
  • 10
  • 50
  • 96
  • You are asking us to compile a list of JDBC drivers with a specific feature. That is pretty hard to do, and it is off topic (as you are asking as to find off site resources/libraries). – Mark Rotteveel Mar 29 '16 at 14:00
  • I'm using JTDS which implements JDBC 3.0 and it doesn't have this method. So I'm guessing that createArrayOf is JDBC 4.0 and beyond. – Dalton Apr 22 '16 at 12:31
  • You can see it here: http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf?AuthParam=1461328696_44ccddf49ead0351ae90095666c4c949 – Dalton Apr 22 '16 at 12:37
  • @MarkRotteveel I am really trying to ask how i can use a collection as a parameter in an SQL preparedstatement. The fact that I have done a lot of research, but have been unable to find the documentation for this problem, doesn't make it off topic. I am not asking for a library or resource which can solve a more or less abstract problem, I am referring to a specific method, asking how I can make it work. This is at most debugging help, with well documented efforts, which is in my opinion very much on topic for this community. – jumps4fun Apr 25 '16 at 11:16
  • Asking us _"Which JDBC's do support setArray()?"_ is exactly that: asking us to compile a list of JDBC drivers which support or don't support certain features. On top of that you have an XY problem, you are asking about `setArray` on the assumption that it will solve your problem (which it likely won't, because availability and usefulness of arrays vary per database; not all database systems will allow use of an array in `IN` for example). – Mark Rotteveel Apr 25 '16 at 11:17
  • You're getting hung up on the details. I'll change the last bit of the question. My problem remains the same though. I have clearly specified which version I am using, and that I am unable to figure out if that particular version is supporting the method or not. This is semantic nitpicking @MarkRotteveel – jumps4fun Apr 25 '16 at 11:20
  • The edited update of your comment, stating that I might have wrongful assumptions about the method, is really helpful though. But right now I am simply trying to figure out if my version of JDBC is supporting the method or not, so that I can figure out if that is the problem with my setup, or something else is. – jumps4fun Apr 25 '16 at 11:27
  • To answer your new question: SQL Server doesn't have arrays, so `setArray` is - as far as I know - not implemented (eg see [this](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ed83343-142b-443e-9634-d1770b24a98b/jdbc-setarray-method)). To check you can call `setArray` and if it throws `SQLFeatureNotSupportedException`, then it is not supported by your driver. – Mark Rotteveel Apr 25 '16 at 11:30
  • I did come across this page, but I read it in a little different meaning. I figure it refers to the structure of the database itself, when it states that it only has tables, and not the construction of preparedstatements. SQL-server 2008 do support IN statements, and I have successfully made generated preparedstatements where I generate a String containing questionmarks, created by a loop, and later populated the same way, but this approach is very verbose. Do you think that the link you provided answers that question? It seems ambiguous to me. – jumps4fun Apr 25 '16 at 11:40
  • Possible duplicate of [Call stored procedure with table-valued parameter from java](https://stackoverflow.com/questions/16047818/call-stored-procedure-with-table-valued-parameter-from-java) – Vadzim Apr 29 '19 at 21:00

0 Answers0