setArray

Hi,

Iam trying to set an array in a prepared statement like this:

PreparedStatement stmnt = ds.prepareStatement(
"INSERT INTO trainingen (\"Atleet\", \"Datum\", \"Tijdstip\",
\"Afstand\", \"Tijd (Zone)\") VALUES (?,?,?,?,?)"
);
stmnt.setString(5, "{1,2,3,4,5,6}");

Assuming that the 5th parameter is an array of integer i get the follow
error message:

ERROR: column "Tijd (Zone)" is of type integer[] but expression is of
type character varying

I understood from previous discussions that the driver underwater will
convert the parameter of setArray
into a string representation like i do in the example above. I there
any other way of setting an array
field in a prepared statement?

Alex.

Hi,

I recently had to solve this too. What I did is:

1) I created class that implements java.sql.Array (it is sufficient to
implement getArray(), getBaseType(), getBaseTypeName() and toString(),
for details see Java API docs) + I added one extra method,
setArray(Class componentType, Object[] array), where componentType is
component type of the array, which I use for setting the array value
into the array

2) Then I use this code to set the array:

final MyArray array = new MyArray();
array.setArray(componentType, arrayValue);
pstm.setArray(index, array);

HTH

--
Miroslav Šulc

Alex Stienstra napsal(a):
> Hi,
>
> Iam trying to set an array in a prepared statement like this:
>
> PreparedStatement stmnt = ds.prepareStatement(
> "INSERT INTO trainingen (\"Atleet\", \"Datum\", \"Tijdstip\",
> \"Afstand\", \"Tijd (Zone)\") VALUES (?,?,?,?,?)"
> );
> stmnt.setString(5, "{1,2,3,4,5,6}");
>
> Assuming that the 5th parameter is an array of integer i get the
> follow error message:
>
> ERROR: column "Tijd (Zone)" is of type integer[] but expression is of
> type character varying
>
> I understood from previous discussions that the driver underwater will
> convert the parameter of setArray
> into a string representation like i do in the example above. I there
> any other way of setting an array
> field in a prepared statement?
>
> Alex.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Hi,

Could you just provide a concrete code snippet?

I would like to pass int[], String[], timestamp[] and float[] arrays.

Thank you in advance,
Andreas

Miroslav Šulc schrieb:
> Hi,
>
> I recently had to solve this too. What I did is:
>
> 1) I created class that implements java.sql.Array (it is sufficient to
> implement getArray(), getBaseType(), getBaseTypeName() and toString(),
> for details see Java API docs) + I added one extra method,
> setArray(Class componentType, Object[] array), where componentType is
> component type of the array, which I use for setting the array value
> into the array
>
> 2) Then I use this code to set the array:
>
> final MyArray array = new MyArray();
> array.setArray(componentType, arrayValue);
> pstm.setArray(index, array);
>
> HTH
>
> --
> Miroslav Šulc
>
>
>
> Alex Stienstra napsal(a):
>> Hi,
>>
>> Iam trying to set an array in a prepared statement like this:
>>
>> PreparedStatement stmnt = ds.prepareStatement(
>> "INSERT INTO trainingen (\"Atleet\", \"Datum\", \"Tijdstip\",
>> \"Afstand\", \"Tijd (Zone)\") VALUES (?,?,?,?,?)"
>> );
>> stmnt.setString(5, "{1,2,3,4,5,6}");
>>
>> Assuming that the 5th parameter is an array of integer i get the
>> follow error message:
>>
>> ERROR: column "Tijd (Zone)" is of type integer[] but expression is of
>> type character varying
>>
>> I understood from previous discussions that the driver underwater will
>> convert the parameter of setArray
>> into a string representation like i do in the example above. I there
>> any other way of setting an array
>> field in a prepared statement?
>>
>> Alex.
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>