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

On Fri, Jun 3, 2011 at 2:10 PM, Steven Schlansker <
stevenschlansker(at)gmail(dot)com> wrote:

> Response inline
>
> On Jun 3, 2011, at 1:59 PM, Radosław Smogura wrote:
>
> > I don't know how to unwrsp C3P0 connection.
>
> It provides a custom API to unwrap the connection, but this ties me to a
> particular database pool. I am trying hard to write portable code.
>
> If I go down this path, I end up having to write a special case for each
> combination of database and pool supported, which is very painful to
> maintain.
>

If you happen to be using Spring to manage the declaration of your data
source, they have the NativeJdbcExtractor interface, with an implementation
for the most popular connection pools. That will give you access to the
native Connection object of your driver. I keep my code portable by
declaring both my connection pool and an appropriate NativeJdbcExtractor
together in an applicationContext file and then I just include the correct
context file for the runtime environment I'm working in. Since my code
always interacts only with the nativeJdbcExtractor, so long as the code it
executes on that native connection isn't actually database specific, the
code remains fully portable with the exception of the applicationContext
file, which can be fed into it at run time.

If you're not using spring, you can model a similar system of your own.

--sam

>
> > 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.
>
> Yes, as I mentioned it is possible to force open the wrapper objects and
> get the raw Connection. However this is terribly brittle even for the same
> pool (as the implementation may change) and completely unworkable if I want
> to
> support using different pools.
>
> >
> > I think, I may be wrong, but JDBC2 support setarrays, only create array
> may be
> > unsupported (it was introduced in 4).
>
> The setArray call is not particularly useful if I have no way to create the
> Array I must pass in as a parameter!
>
> >
> > 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.
> >
>
> Yes, I have explored temporary tables as an option. Unfortunately it seems
> that
> PostgreSQL takes n the order of hundreds of milliseconds to create a
> temporary
> table, which makes this approach even slower than just running the query a
> hundred
> times with a single parameter each time!
>
> I could look into casting to an array and using that, however this requires
> multiple
> roundtrips and feels much more like a hack than a real solution to me.
>
> So thank you much, but I don't think these solutions are workable in my
> case. I
> would like to avoid hacking around problems if I can :-)
>
>
> > 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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>