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 19:19:13
Message-ID: d106cb7f0812301119k6a0f2a76y439fb0fefa6be1c4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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.

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

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2008-12-30 19:47:47
Subject: Re: Trying to insert an array using a prepared statement.
Previous:From: Dave CramerDate: 2008-12-30 01:02:48
Subject: Re: Trying to insert an array using a prepared statement.

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