Re: PreparedStatement and setting an array is now broken

On Wed, 22 Dec 2004, Andrew Lazarus wrote:

> Until version 8 (now using build 308), I used a prepared statement
>
> INSERT INTO mytable(array_column) VALUES(?);
>
> setString(1, my_formatter(java_array));
>
> This doesn't work any more, with the error message that character
> varying can not be converted to real[] . I think it's with the driver,
> because the braces form still works fine with psql.

This is an expected error message. The 8.0 driver uses strongly typed
parameters. By using setString you are claiming that you have varchar
data. The psql equivalent is:

The old driver:

jurka=# select '{1}'::int[];
int4
------
{1}
(1 row)

The 8.0 driver:

jurka=# select '{1}'::varchar::int[];
ERROR: cannot cast type character varying to integer[]

The correct JDBC solution is to use setArray() or setObject(). The
postgresql JDBC driver doesn't support using setObject on Java arrays, so
that's out. The pg implementation of setArray is very fragile and
requires a specific java.sql.Array implementation. So that's less than
ideal (especially considering the extra code/work on the client side to
construct such an array).

The move to strong typing, especially without providing workarounds (like
this case), is definitely going to be a problem in the 8.0 release. This
is part of the growing pains the driver has to go through to use the V3
protocol to its fullest.

Unfortunately your options at this point are:
- Use the 7.4 driver.
- Use the 8.0 with the protocolVersion=2 URL parameter.
- Provide a java.sql.Array implementation
- Add support for java arrays in setObject
- or complain loudly enough that someone else will

Kris Jurka