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

On Tue, Dec 30, 2008 at 2:19 PM, Eric Davies <slowcanuck(at)gmail(dot)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(at)fastcrypt(dot)com> wrote:
>
>> 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.
>>>
>>
>>
>