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.
You have to implement the toString method in the LinearArray class which then outputs {1,2,3}
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.
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