JDBC adaptor issue

Hi all,

I've been trying to get PostgreSQL to work with Apple's
WebObjects application server. WebObjects uses JDBC as an
interface to back-end databases, translating between SQL and a
pure object model.

I had a problem with incorrect SQL being generated and sent to
the PostgreSQL back end. After some work, I tracked it down. I
have a fix, but the fix has ramifications for the way that
others use PostgreSQL, so I decided to post here and see what
people think.

It turns out that WebObjects uses the
PreparedStatement.setCharacterStream method in order to set the
values of some character parameters in prepared statements, and
thus the generated SQL. It's not at all clear why it does this
for some parameters but not others; the reason doesn't seem to
have anything to do with the declared length of the parameters.
This seems odd, because setCharacterStream is a very
high-overhead operation, but in any case, that's what it does.

The PostgreSQL JDBC driver, however, makes the assumption that
any JDBC client class that's using the set/get...stream methods
wants to exchange information with a field that's been
explicitly typed as a BLOB. It therefore does what PostgreSQL
requires: it creates a new object containing the data, then uses
the object ID of the new object as the value to stuff into the
query. This has the effect of generating queries like

SELECT ...
WHERE some_text_field = 57909 ...

57909 is an object ID. The comparison doesn't work because
some_text_field is an ordinary char or varchar, not a BLOB.

It's kind of hard to figure out the "right" solution to this
problem. I've patched the PostgreSQL JDBC implementation of
PreparedStatement.setCharacterStream to treat any stream smaller
than 8190 bytes as a string. I chose 8190 because of the old
limit of 8192 bytes per tuple in versions prior to 7.1, so this
change is least likely to cause compatibility problems with
systems using setCharacterStream the way that the PostgreSQL
developers anticipated. I can provide the patch to anyone who
needs it.

The WebObjects use of JDBC is in line with the JDBC 2.0
specification; that spec does not place any restrictions on the
types of fields that can be accessed via get/set...stream.
Whether it's a good use is a different question, of course, but
it's still legal. My little kludge with an 8190-byte "switch" to
the old behavior really can't be the last word.

I was hoping that someone could look at the PostgreSQL back end
to see if there's any reason to keep the 8190-byte limiting
behavior in the JDBC driver. The limit needs to be removed so
that character streams and strings are symmetric in order to
comply with JDBC 2.0. The effect of switching will simply be the
possibility that the back end will have to deal with very long
(>8k) quoted strings. I got the impression from reading TOAST
project documents that all such limitations had been removed,
but I wanted to check before submitting my patch for inclusion
in the distribution.

Thanks,
-- Bruce

--------------------------------------------------------------------------
Bruce Toback Tel: (602) 996-8601| My candle burns at both ends;
OPT, Inc. (800) 858-4507| It will not last the night;
11801 N. Tatum Blvd. Ste. 142 | But ah, my foes, and oh, my
friends -
Phoenix AZ 85028 | It gives a lovely light.
btoback(at)optc(dot)com | -- Edna St. Vincent Millay