Thread: JDBC question: Creating new arrays

It seems that there is a difficulty in JDBC with creating arrays.  I
have an array of ints that I want to store in PG.  If I try code like
this:

int[] intArray = { .... };
PreparedStatement ps = db.prepareStatement(...);
ps.setObject(1, intArray);

it doesn't work.  There is a PreparedStatement.setArray() method, but it
takes a java.sql.Array as an argument, and yet the Array interface
doesn't define any constructors and there doesn't seem to be any factory
for it either!!!!

Help!  What do I do here?  Can I fix this by moving over to the new
javax.sql packages?  Do I need to use some non-portable code?

Thanks!

Joe Tomcat <tomcat@mobile.mp> writes:

> It seems that there is a difficulty in JDBC with creating arrays.  I
> have an array of ints that I want to store in PG.  If I try code like
> this:
>
> int[] intArray = { .... };
> PreparedStatement ps = db.prepareStatement(...);
> ps.setObject(1, intArray);
>
> it doesn't work.  There is a PreparedStatement.setArray() method, but it
> takes a java.sql.Array as an argument, and yet the Array interface
> doesn't define any constructors and there doesn't seem to be any factory
> for it either!!!!

Then you probably need to wrap your Java array in an object that
implements java.sql.Array so that the JDBC driver can talk to it.
Shouldn't be hard.

-Doug

On Tue, 2002-11-12 at 17:39, Doug McNaught wrote:
> Then you probably need to wrap your Java array in an object that
> implements java.sql.Array so that the JDBC driver can talk to it.
> Shouldn't be hard.

That still doesn't make it driver-independent, does it?

Anyway, I found a simple solution that works easily with Postgres: The
way PreparedStatement.setArray(Array) works is that it actually gets
translated to PreparedStatement.setString(Array.toString()).  The
Array.toString() method is very simple; it just makes a string that
looks like '{484,282,945}' (for an int[]) so I just turned my int[] into
such a string, and called PreparedStatement.setString().  This is a bit
of a hack, but it seems that there is no db-independent way to do this,
so I have no other options.  If we need to move to some other db, this
shouldn't be hard to modify as needed.

There is one other problem, though:  If I have an array with no
elements, then this operation:

Array array = resultSet.getArray(3);
Object o = array.getArray();

throws a Bad Integer exception.  This seems like it must be a bug in the
JDBC.  To get around it, I put the o = array.getArray() inside a try
block, and if throws an exception, I know that the array is
zero-length.  This is clunky and it violates the principle of "Only use
exceptions for exceptional conditions" and probably has some performance
problems.  It seems that array.getArray() should always be able to
return properly because that should be a class invariant.

Any suggestions on this?

Joe Tomcat <tomcat@mobile.mp> writes:

> On Tue, 2002-11-12 at 17:39, Doug McNaught wrote:
> > Then you probably need to wrap your Java array in an object that
> > implements java.sql.Array so that the JDBC driver can talk to it.
> > Shouldn't be hard.
>
> That still doesn't make it driver-independent, does it?

How not?  Implementing java.sql.Array is the JDBC standard way to do
this, though it seems to be a ridiculous amount of work.  Whereas:

> Anyway, I found a simple solution that works easily with Postgres: The
> way PreparedStatement.setArray(Array) works is that it actually gets
> translated to PreparedStatement.setString(Array.toString()).  The
> Array.toString() method is very simple; it just makes a string that
> looks like '{484,282,945}' (for an int[]) so I just turned my int[] into
> such a string, and called PreparedStatement.setString().  This is a bit
> of a hack, but it seems that there is no db-independent way to do this,
> so I have no other options.  If we need to move to some other db, this
> shouldn't be hard to modify as needed.

This is definitely not driver-independent.  ;)

> There is one other problem, though:  If I have an array with no
> elements, then this operation:
>
> Array array = resultSet.getArray(3);
> Object o = array.getArray();
>
> throws a Bad Integer exception.

This does sound like a bug.

-Doug

Joe,

I can't reproduce this error (empty array causing errors).  What version
of the driver are you using?  Have you tried the 7.3beta builds?  If you
can reproduce with the latest code, please send a simple java test case.

thanks,
--Barry


Joe Tomcat wrote:
> On Tue, 2002-11-12 at 17:39, Doug McNaught wrote:
>
>>Then you probably need to wrap your Java array in an object that
>>implements java.sql.Array so that the JDBC driver can talk to it.
>>Shouldn't be hard.
>
>
> That still doesn't make it driver-independent, does it?
>
> Anyway, I found a simple solution that works easily with Postgres: The
> way PreparedStatement.setArray(Array) works is that it actually gets
> translated to PreparedStatement.setString(Array.toString()).  The
> Array.toString() method is very simple; it just makes a string that
> looks like '{484,282,945}' (for an int[]) so I just turned my int[] into
> such a string, and called PreparedStatement.setString().  This is a bit
> of a hack, but it seems that there is no db-independent way to do this,
> so I have no other options.  If we need to move to some other db, this
> shouldn't be hard to modify as needed.
>
> There is one other problem, though:  If I have an array with no
> elements, then this operation:
>
> Array array = resultSet.getArray(3);
> Object o = array.getArray();
>
> throws a Bad Integer exception.  This seems like it must be a bug in the
> JDBC.  To get around it, I put the o = array.getArray() inside a try
> block, and if throws an exception, I know that the array is
> zero-length.  This is clunky and it violates the principle of "Only use
> exceptions for exceptional conditions" and probably has some performance
> problems.  It seems that array.getArray() should always be able to
> return properly because that should be a class invariant.
>
> Any suggestions on this?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Actually storing an array in an RDBMS table column is a feature in
Postgresql that does not exists in most other database. So, in other words,
it is not driver-independence, it is database dependent.

"Joe Tomcat" <tomcat@mobile.mp> wrote in message
news:1037239808.1318.466.camel@linux...
> On Tue, 2002-11-12 at 17:39, Doug McNaught wrote:
> > Then you probably need to wrap your Java array in an object that
> > implements java.sql.Array so that the JDBC driver can talk to it.
> > Shouldn't be hard.
>
> That still doesn't make it driver-independent, does it?
>
> Anyway, I found a simple solution that works easily with Postgres: The
> way PreparedStatement.setArray(Array) works is that it actually gets
> translated to PreparedStatement.setString(Array.toString()).  The
> Array.toString() method is very simple; it just makes a string that
> looks like '{484,282,945}' (for an int[]) so I just turned my int[] into
> such a string, and called PreparedStatement.setString().  This is a bit
> of a hack, but it seems that there is no db-independent way to do this,
> so I have no other options.  If we need to move to some other db, this
> shouldn't be hard to modify as needed.
>
> There is one other problem, though:  If I have an array with no
> elements, then this operation:
>
> Array array = resultSet.getArray(3);
> Object o = array.getArray();
>
> throws a Bad Integer exception.  This seems like it must be a bug in the
> JDBC.  To get around it, I put the o = array.getArray() inside a try
> block, and if throws an exception, I know that the array is
> zero-length.  This is clunky and it violates the principle of "Only use
> exceptions for exceptional conditions" and probably has some performance
> problems.  It seems that array.getArray() should always be able to
> return properly because that should be a class invariant.
>
> Any suggestions on this?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)