Обсуждение: Trying to insert an array using a prepared statement.
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();
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:
Binary attempt:org.postgresql.util.PSQLException: ERROR: cannot cast type character varying to integer[]
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.
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
org.postgresql.util.PSQLException: ERROR: array value must start with "{" or dimension information
Any ideas appreciated.
Thank you.
Eric,
You have to implement the java.sql.Array interface on your object.
Dave
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@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:Binary attempt:org.postgresql.util.PSQLException: ERROR: cannot cast type character varying to integer[]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.
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.
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:Binary attempt:org.postgresql.util.PSQLException: ERROR: cannot cast type character varying to integer[]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.
On Tue, Dec 30, 2008 at 2:19 PM, Eric Davies <slowcanuck@gmail.com> wrote:
You have to implement the toString method in the LinearArray class which then outputs {1,2,3}
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.
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.
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:Binary attempt:org.postgresql.util.PSQLException: ERROR: cannot cast type character varying to integer[]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.
Hi Dave,
Having to implement the toString method comes under the category of "so bizarre it has to be true" :-).
Lo and behold, it works!
Thank you!
Eric.
Having to implement the toString method comes under the category of "so bizarre it has to be true" :-).
Lo and behold, it works!
Thank you!
Eric.
On Tue, Dec 30, 2008 at 11:47 AM, Dave Cramer <pg@fastcrypt.com> wrote:
On Tue, Dec 30, 2008 at 2:19 PM, Eric Davies <slowcanuck@gmail.com> wrote: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.
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.
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:Binary attempt:org.postgresql.util.PSQLException: ERROR: cannot cast type character varying to integer[]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.
On Mon, 29 Dec 2008, Dave Cramer wrote: > You have to implement the java.sql.Array interface on your object. > With JDBC4 support you should really be using Connection.createArrayOf, not implementing Array and the PG specific getBaseType and toString methods that are needed. Kris Jurka
"Eric Davies" <slowcanuck 'at' gmail.com> writes: > 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[] You may use string_to_array. In some cases, it's quite handy for passing SQL arrays to PG (AFAIK in your case you still need to cast to integer[], but it works). http://zarb.org/~gc/html/doc-misc.html#2008-08-21 -- Guillaume Cottenceau