Re: JDBC question: Creating new arrays
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
Import Notes
Reply to msg id not found: JoeTomcat'smessageof13Nov2002110020-0800Reference msg id not found: 1037214022.1472.6.camel@linux
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
Import Notes
Reply to msg id not found: JoeTomcat'smessageof13Nov2002181005-0800Reference msg id not found: 1037214022.1472.6.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?
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...
Show quoted text
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)