Re: Accepting Object[] as an acceptable input to setObject with Types.ARRAY?

I don't know how to unwrsp C3P0 connection. If no API is exposed you should at
least use (Netbeans) debugger to track where PSQL connection is, and then
using reflection/introspection (I can't remember what stands for), traverse
methods or fields manually giving it's name.

I think, I may be wrong, but JDBC2 support setarrays, only create array may be
unsupported (it was introduced in 4).

2nd workaround You may try is to create array with e.g. in temp table try to
modify it and pass to statement, or try to call this statement to get array.
Maybe something like this
SELECT {1}::int[]
Then You may try to use getResultset on array.

Regards,
Radek

Steven Schlansker <stevenschlansker(at)gmail(dot)com> Friday 03 of June 2011 22:04:58
> Hi all,
>
> First off, the environment -
> PostgreSQL 9.0, driver 9.0-801.jdbc4
> C3P0 0.9.1.2
> H2 1.3.154
>
> Here's my dilemma. I am attempting to use SQL Arrays (a JDBC 4 feature)
> but all the JDBC pools I have had good success with (to date, only C3P0)
> do not support JDBC 4. Specifically, if you try to call
> Connection.createArrayOf, the pool intercepts it and fails with an
> AbstractMethodError as the Connection did not specify that interface
> method when C3P0 was compiled (against the JDBC 2 API). It is possible to
> break through this barrier with reflective magic, but I don't like this as
> a long term solution.
>
> This means that it is not possible to create the java.sql.Array instance
> that would be required to call setArray to set an array argument on a
> prepared statement in a portable way.
>
> H2 (http://www.h2database.com) supports a nifty workaround - if you call
> setObject with a Object[] it will "do the right thing" and internally
> convert this into the SQL Array. This means that the driver does the work
> so client code does not have to hack around the lack of createArrayOf.
>
> (ref: http://www.h2database.com/html/datatypes.html#array_type )
>
> It looks like adding support for such a fix to the Postgres driver would be
> extremely easy. In particular looking around
> AbstractJdbc2Statement.java:1732
>
> case Types.ARRAY:
> if (in instanceof Array)
> setArray(parameterIndex, (Array)in);
> else
> throw new PSQLException(GT.tr("Cannot cast an instance
> of {0} to type {1}", new Object[]{in.getClass().getName(),"Types.ARRAY"}),
> PSQLState.INVALID_PARAMETER_TYPE); break;
>
> it could check if in is an array type and if so synthesize the Array object
> necessary.
>
> Does this sound like a reasonable feature request? Did I miss an easier
> way to do this? It is probably outside of the JDBC spec but it at least
> has some traction with H2...
>
> If this is a reasonable approach I would be happy to contribute a patch,
> although I am sure an actual PG JDBC developer could do it much faster
> than I.
>
> Thanks much for any input,
> Steven