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

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.

On Tue, Dec 30, 2008 at 11:47 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

>
>
> 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.
>>>>
>>>
>>>
>>
>