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

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
interceptsit and fails with an AbstractMethodError as the Connection did not specify that interface method when C3P0
wascompiled (against the JDBC 2 API).  It is possible to break through this barrier with reflective magic, but I don't
likethis 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
anarray 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
rightthing" and internally convert this into the SQL Array.  This means that the driver does the work so client code
doesnot 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
aroundAbstractJdbc2Statement.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
JDBCspec 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
coulddo it much faster than I. 

Thanks much for any input,
Steven

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@gmail.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

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 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@gmail.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

On Jun 3, 2011, at 3:02 PM, Samuel Gendler wrote:

> > 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
Connectionobject of your driver.  I keep my code portable by declaring both my connection pool and an appropriate
NativeJdbcExtractortogether in an applicationContext file and then I just include the correct context file for the
runtimeenvironment I'm working in.  Since my code always interacts only with the nativeJdbcExtractor, so long as the
codeit executes on that native connection isn't actually database specific, the code remains fully portable with the
exceptionof 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.

Wow, that is very clever how they do it!  Basically they call
connection.getMetaData().getConnection() which most pools do not intercept, giving you access to
the connection via only the public APIs.  And you do not even need to use Spring if you just do that.

I can definitely do this for my uses, thank you.

That said I still think it is an interesting feature to consider, as ultimately this is still
a hack (even if an elegant one) and I have whipped up a small proof of concept:


diff -r e313d386bcf0 org/postgresql/jdbc2/AbstractJdbc2Statement.java
--- a/org/postgresql/jdbc2/AbstractJdbc2Statement.java  Fri Jun 03 13:21:43 2011 -0700
+++ b/org/postgresql/jdbc2/AbstractJdbc2Statement.java  Fri Jun 03 13:57:52 2011 -0700
@@ -1731,7 +1796,9 @@
                     throw new PSQLException(GT.tr("Cannot cast an instance of {0} to type {1}", new
Object[]{in.getClass().getName(),"Types.CLOB"}),PSQLState.INVALID_PARAMETER_TYPE); 
                 break;
             case Types.ARRAY:
-                if (in instanceof Array)
+                if (in instanceof Object[])
+                    setArray(parameterIndex, getPGConnection().createArrayOf(determineSQLType((Object[]) in),
(Object[])in)); 
+                else 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); 
@@ -1750,6 +1817,38 @@
         }
     }

+    private String determineSQLType(Object[] array) {
+        Class<?> componentType = array.getClass().getComponentType();
+        if (componentType.equals(byte.class) || componentType.equals(Byte.class))
+            return "tinyint";
+        if (componentType.equals(char.class) || componentType.equals(Character.class))
+            return "varchar";
+        if (componentType.equals(short.class) || componentType.equals(Short.class))
+            return "smallint";
+        if (componentType.equals(int.class) || componentType.equals(Integer.class))
+            return "int";
+        if (componentType.equals(long.class) || componentType.equals(Long.class))
+            return "bigint";
+        if (componentType.equals(float.class) || componentType.equals(Float.class))
+            return "float";
+        if (componentType.equals(double.class) || componentType.equals(Double.class))
+            return "double";
+        if (componentType.equals(boolean.class) || componentType.equals(Boolean.class))
+            return "bit";
+        if (componentType.equals(String.class))
+            return "varchar";
+        if (componentType.equals(byte[].class))
+            return "varbinary";
+        if (componentType.equals(Date.class))
+            return "date";
+        if (componentType.equals(Time.class))
+            return "time";
+        if (componentType.equals(Timestamp.class))
+            return "timestamp";
+        // Cross your fingers and hope the default works...
+        return "varchar";
+    }
+

On 4 June 2011 08:04, Steven Schlansker <stevenschlansker@gmail.com> wrote:

> It looks like adding support for such a fix to the Postgres driver would be extremely easy.  In particular looking
aroundAbstractJdbc2Statement.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
theJDBC spec but it at least has some traction with H2... 

How do you determine the type of the (server-side) array elements?
i.e. what is the typename that setObject would pass to createArrayOf()
when it encountered an Object[]?

Oliver

On Jun 3, 2011, at 3:47 PM, Oliver Jowett wrote:

> On 4 June 2011 08:04, Steven Schlansker <stevenschlansker@gmail.com> wrote:
>
>> It looks like adding support for such a fix to the Postgres driver would be extremely easy.  In particular looking
aroundAbstractJdbc2Statement.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
theJDBC spec but it at least has some traction with H2... 
>
> How do you determine the type of the (server-side) array elements?
> i.e. what is the typename that setObject would pass to createArrayOf()
> when it encountered an Object[]?

The patch I put in another branch of this thread does basic type introspection.  It handles all the primitive types,
wrappedtypes, and a few SQL types (i.e. Date, Time, Timestamp, byte[]) 

On 4 June 2011 10:42, Steven Schlansker <stevenschlansker@gmail.com> wrote:

> +    private String determineSQLType(Object[] array) {
> +        Class<?> componentType = array.getClass().getComponentType();
> +        if (componentType.equals(byte.class) || componentType.equals(Byte.class))
> +            return "tinyint";

Note that the test vs. byte, int, etc will never fire, because arrays
of primitive types are not instanceof Object[]

Can we do anything better when given, say, an Object[] (not Integer[])
that contains only Integer instances?
Not sure if you can do anything sensible here, since an Object[] could
also contain a heterogeneous mix of types.

There seems to be some duplication between determineSQLType() and the
switch in setObject().

Oliver