PostgreSQL JDBC - Array support

Author

Array support

Thomas O'Dowd

2005-04-21, 9:23 am

Hi all,

Currently using the driver postgresql-8.0-310.jdbc3.jar which I
downloaded a while ago. Just tried to use it to store a Java String
array but it throws an unimplemented exception.

A snippet of code that throws the exception would be:

String[] strings = new String[] {"one", "two"};
con.prepareStatement("INSERT INTO a (str_array) VALUES (?)";
ps.setObject(1, strings, java.sql.Types.ARRAY);
ps.executeUpdate();

(forgive any syntax errors, its just an example)

The exception thrown is:

Caused by: org.postgresql.util.PSQLException: Unsupported Types value: 2,003
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. setObject(AbstractJd
bc2Statement.java:1621)
at org.postgresql.jdbc3. AbstractJdbc3Stateme
nt. setObject(AbstractJd
bc3Statement.java:1436)
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. setObject(AbstractJd
bc2Statement.java:1627)
at sun.reflect. NativeMethodAccessor
Impl.invoke0(Native Method)
... yada yada yada ...

According to the JDBC Spec, jdbc-3_0-fr-spec.pdf, the recommended way to
set an array is by using the ps.setObject() method. Is this easy to
implement? If someone can point me at some code, I can try implementing
it and sending in a patch. I haven't played with the driver code in
about 2 years. Unfortunately, I'm away on holidays for about a week
tomorrow, but if you have any ideas regarding implementation, I'll have
a go when I come back.

Should be possible to make it support multiple dimension arrays too I
guess. I haven't actually looked at the driver code yet. Oh I haven't
gotten around to testing ResultSet.getArray() yet, but I think I
remember reading somewhere that it was implemented.

Tom.

PS. I looked at trying to use PreparedStatement.setArray() but it takes
an java.sql.Array, which is just an Interface. I guess I could try
creating a class that implements Array and try passing that to
setArray() but I'm not even sure if that would work yet either. The
setObject() way suggested by the spec is definitely simpler.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Kris Jurka

2005-04-21, 8:24 pm

On Thu, 21 Apr 2005, Thomas O'Dowd wrote:

> [ I want to use setObject(1, new String[] {"a","b"}) ]
>
> According to the JDBC Spec, jdbc-3_0-fr-spec.pdf, the recommended way to
> set an array is by using the ps.setObject() method. Is this easy to
> implement? If someone can point me at some code, I can try implementing
> it and sending in a patch.

The difficulty is the code to do things like turn java types into their pg
representation doesn't really have a callable interface, it's all spread
out and embedded all over the place. Creating another copy would kind
of suck. As I see it this work is kind of lumped into a general task of
moving datatype read/write methods into accessible places that could be
used by other new things, for example COPY and binary parameter
send/receive.

> Should be possible to make it support multiple dimension arrays too I
> guess. I haven't actually looked at the driver code yet. Oh I haven't
> gotten around to testing ResultSet.getArray() yet, but I think I
> remember reading somewhere that it was implemented.

getArray() will return java.sql.Array which probably isn't what you'd
expect. getObject() should also return java.sql.Array, so there will
likely be some disconnect here.

> PS. I looked at trying to use PreparedStatement.setArray() but it takes
> an java.sql.Array, which is just an Interface. I guess I could try
> creating a class that implements Array and try passing that to
> setArray() but I'm not even sure if that would work yet either. The
> setObject() way suggested by the spec is definitely simpler.

That will work although there are some requirements for getBaseTypeName I
think, it must return the pg specific type name used.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Oliver Jowett

2005-04-21, 8:24 pm

Kris Jurka wrote:
>
> On Thu, 21 Apr 2005, Thomas O'Dowd wrote:

>
>
> That will work although there are some requirements for getBaseTypeName I
> think, it must return the pg specific type name used.

More importantly, toString() has to return the data formatted as the
server expects..

-O

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thomas O'Dowd

2005-04-28, 7:24 am

Thanks for the reply guys. I created a StringArray object which
implements the Array interface and overrides toString(). It seems to
work well for the moment.

I ran into 2 problems with it so far, the 2nd of which I need help with.

1. I tried to use the syntax ARRAY['one'] in the string returned by
toString(). This fails with the server error:

array value must start with "{" or dimension information

So it looks like that format is not supported by server side prepared
statements. Next I tried generating the other array syntax {"one"} and
this seemed to work well so no problem here for the moment.

2. How do you set an array to null? I tried:

PreparedStatement.setNull(index, java.sql.Types.ARRAY);

but it throws the exception

org.postgresql.util.PSQLException: Unknown Types value.
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. setNull(AbstractJdbc
2Statement.java:1003)

I think that should work, no? Had a brief look at the code in
jdbc2. AbstractJdbc2Stateme
nt and indeed it doesn't handle type Array nor
does Oid.ARRAY map to anything.

I also tried to get my StringArray.toString() method to return the
string "NULL", ie: return "NULL", when the string array object that I
want to save to the database is null. This fails with the exception.

array value must start with "{" or dimension information

I also tried return "{}" but this doesn't generate a null in the
database as its an empty array.

Q: How do I set an array column to null using a PreparedStatement?

Just in case it matters, I'm using 8.0.1 and
postgresql-8.0-310.jdbc3.jar with jdk1.5.0_02.

Tom.

On Fri, 2005-04-22 at 09:37 +1200, Oliver Jowett wrote:
> Kris Jurka wrote:
>
>
> More importantly, toString() has to return the data formatted as the
> server expects..
>
> -O

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-04-28, 9:23 am

On Thu, 28 Apr 2005, Thomas O'Dowd wrote:

> 1. I tried to use the syntax ARRAY['one'] in the string returned by
> toString(). This fails with the server error:
>
> array value must start with "{" or dimension information
>
> So it looks like that format is not supported by server side prepared
> statements.

Right, the ARRAY[] syntax is part of the parser which doesn't get invoked
on parameters, so you need to use the {} format.

> 2. How do you set an array to null? I tried:
> PreparedStatement.setNull(index, java.sql.Types.ARRAY);
> but it throws the exception
>
> org.postgresql.util.PSQLException: Unknown Types value.
> at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. setNull(AbstractJdbc
2Statement.java:1003)
>
> Q: How do I set an array column to null using a PreparedStatement?

This is indeed a bug, I've fixed this in the 8.0 + head branches. Updated
jar files are available here:

http://www.ejurka.com/pgsql/jars/tod/

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Thomas O'Dowd

2005-04-28, 11:24 am

Kris,

> Right, the ARRAY[] syntax is part of the parser which doesn't get invoked
> on parameters, so you need to use the {} format.

Gotcha.

>
> This is indeed a bug, I've fixed this in the 8.0 + head branches. Updated
> jar files are available here:
>
> http://www.ejurka.com/pgsql/jars/tod/

Just downloaded the new driver and tested it. It works a charm. Thanks
for tracking this down so promptly and making the jar available.

Best regards,

Tom.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Sponsored Links