Hi Dave,
In the binary attempt, you'll see a "LinearArray" object wrapping up the simple array of Integers.
The LinearArray is my implementation of java.sql.Array. Without it, I wouldn't have gotten as far as getting an exception since I wouldn't have been able to call the setArray method.
But thank you for responding.
Eric.
On Mon, Dec 29, 2008 at 5:02 PM, Dave Cramer
<pg@fastcrypt.com> wrote:
Eric,
You have to implement the java.sql.Array interface on your object.
DaveOn Mon, Dec 29, 2008 at 7:39 PM, Eric Davies
<slowcanuck@gmail.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.