Skip site navigation (1) Skip section navigation (2)

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

From: "Eric Davies" <slowcanuck(at)gmail(dot)com>
To: "Dave Cramer" <pg(at)fastcrypt(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Trying to insert an array using a prepared statement.
Date: 2008-12-30 22:26:54
Message-ID: d106cb7f0812301426i224df167x7f864193532873f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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.
>>>>
>>>
>>>
>>
>

In response to

pgsql-jdbc by date

Next:From: MiƂosz TylendaDate: 2008-12-31 12:19:23
Subject: Support of getting generated keys in driver version 8.4devel
Previous:From: Dave CramerDate: 2008-12-30 19:47:47
Subject: Re: Trying to insert an array using a prepared statement.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group