SEO

I am using postgres 7.4 with an 8.1 JDBC driver. I am considering
whether I should use postgres arrays, and a key issue is JDBC support.
I found this comment about the 7.4 driver from Oliver Jowett
(http://archives.postgresql.org/pgsql...msg00141.php):

The driver does not really support setArray() / updateArray() for
arbitary Array implementations. I had a patch to fix this ages ago but
it has rotted since then, and was never really the right way to do it
anyway.

The 8.1 release notes for dev307 say this, which seems to imply
better support:

Fix setArray when using the v3 protocol. We can't bind arrays as text,
we need the correct type. This gets the array type from the
Array.getBaseType method. This is a fragile means that depends on a
specific Array implementation, but we already depend on a very
specific implementation (toString() returning a correctly formatted pg
array) that we can live with this. (oliver)

I'm not sure I understand this. It sounds like the array is converted
to a string. Is the string then just pasted into the query? What does
this to to the use of server-side prepared statements? I'd like to
avoid parsing the "same" statement repeatedly due to the use of
arrays.

Or is the string then somehow used to bind an array to the prepared
statement?

I don't have to use postgres array, and if the driver will introduce
lots of string manipulation, I'll probably do something else.

Jack Orenstein



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

On Tue, 28 Mar 2006, jao@geophile.com wrote:

> I'm not sure I understand this. It sounds like the array is converted
> to a string. Is the string then just pasted into the query? What does
> this to to the use of server-side prepared statements? I'd like to
> avoid parsing the "same" statement repeatedly due to the use of
> arrays.
>
> Or is the string then somehow used to bind an array to the prepared
> statement?

Yes. The conversion to string is for the bind parameter. Basically
setArray takes a java.sql.Array not something like int[]. We also require
a very specific implementation of the java.sql.Array interface to be
passed to the setArray function. The requirements are that getBaseType
return the correct type value and that calling toString returns the data
in the format that the postgresql server expects for a bind variable.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match