Re: Array support

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.AbstractJdbc2Statement.setNull(AbstractJdbc2Statement.java:1003)

I think that should work, no? Had a brief look at the code in
jdbc2.AbstractJdbc2Statement 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:
> >
> > On Thu, 21 Apr 2005, Thomas O'Dowd wrote:
>
> >>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.
>
> More importantly, toString() has to return the data formatted as the
> server expects..
>
> -O