PreparedStatement and Arrays

[AndreB]

Okay, now this is tricky and google didnt help much (only to relize this is a difficult problem)

I use postgresql so i will do it in that dislaect.
Here the query:

I have this simple query (in pgadmin3)

select '{1,3}'::int[]

it returns an array and it is great ;-)

but in my code i want to use a prepared statement

PreparedStatement s = connection.prepareStatement("select ?::int[]");

now the question: which method should i use to set the value?
if i e.g. use s.setString(1,"{1,3}") i have a cast exception.
so which one is right?

thanks in advance

[r035198x]

Did you try the setArray method?

[AndreB]

no, like i said the setArray method does not really work, as i have to implement the java.sql.Array myself.

but i found the solution:

select string_to_array('1,2,3',',')

this converts a string to an array and therefore all i have to to do is

PreparedStatement s = connection.prepareStatement("select string_to_array(?,' '));

[AndreB]

btw: how do i mark as solved ?

[r035198x]

no, like i said the setArray method does not really work, ...

Where did you mention that you tried setArray and it didn't work?

[AndreB]

sry, my bad :-) forget to mention. but many google hits said that setArray is optional and is only possible for oracle db without own implementation of the Array interface.