| From: | "Eric Davies" <slowcanuck(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-jdbc(at)postgresql(dot)org | 
| Subject: | Trying to insert an array using a prepared statement. | 
| Date: | 2008-12-30 00:39:10 | 
| Message-ID: | d106cb7f0812291639h75243a8cs4c4d4f75f0aba9bf@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-jdbc | 
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Cramer | 2008-12-30 01:02:48 | Re: Trying to insert an array using a prepared statement. | 
| Previous Message | ahmet temiz | 2008-12-26 17:53:53 | what to do to display largeobject (image) |