Re: Trying to insert an array using a prepared statement.

Eric,

You have to implement the java.sql.Array interface on your object.

Dave

On Mon, Dec 29, 2008 at 7:39 PM, Eric Davies <slowcanuck(at)gmail(dot)com> wrote:

>
> I'm trying to insert an array into an array column, with no great success.
> Has anybody gotten this to work, and if so, may I inquire how?
>
> I've tried using both text and binary arguments as described below. I'm
> using Postgresql 8.2.4 with the jdbc driver postgresql-8.3.604.jdbc4.jar on
> a Linux box.
>
> Text attempts.
>
> java.sql.PreparedStatement st = conn.prepareStatement("insert into
> vectortest(a) values(?)");
> st.setString(1, "{1,2,3}");
> st.execute();
>
> which threw the exception:
> org.postgresql.util.PSQLException: ERROR: column "a" is of type integer[]
> but expression is of type character varying
>
> java.sql.PreparedStatement st = conn.prepareStatement("insert
> into vectortest(a) values(?::integer[])");
> st.setString(1, "{1,2,3}");
> st.execute();
>
> which threw the exception:
> org.postgresql.util.PSQLException: ERROR: cannot cast type character
> varying to integer[]
>
> Binary attempt:
> I first queried a table containing an integer [] column to determine that
> the the baseTypeName was int4 and that the array was passed in the form of
> an array of java.lang.Integer objects.
> I implemented the java.sql.Array interface (ignoring the map arguments in
> the getArray methods), and tried the below sequence of statements.
>
> java.sql.PreparedStatement st = conn.prepareStatement("insert
> into vectortest(a) values(?)");
> Integer []vals = new Integer[3];
> vals[0] = new Integer(1);
> vals[1] = new Integer(2);
> vals[2] = new Integer(3);
> st.setArray(1, new LinearArray(vals));
> st.execute();
>
> which threw the exception
> org.postgresql.util.PSQLException: ERROR: array value must start with "{"
> or dimension information
>
> Any ideas appreciated.
> Thank you.
>