Re: JDBC driver's (non-)handling of InputStream:s

Peter Schuller wrote:

> Does the JDBC driver handle InputStream:s intelligently at all? If so, does it
> do so under all circumstances? In this case I am putting data into a column
> of type 'bytea' and am using PreparedStatement.setBinaryStream().

The short answer is no, it's not smart about InputStreams. It treats the
stream essentially the same as if you'd read the entire stream into a
byte array then called setBytes().

> The backend is PostgreSQL 7.4.1, and I am using the driver for 7.4.1
> (pg74.1jdbc3.jar). Running under JDK 1.4.2.
>
> Do I need to use some other type in the database in order for input streams to
> be handled properly? Do I have to use some PostgreSQL specific API? Does the
> JDBC driver need to be changed to support this?

It'll need non-trivial driver modifications. It's on my list of things
to do, but I doubt I'm going to get to any of the postgresql work on
that list for quite some time now :(

The "right way" to do it is to expand the driver's use of the V3
protocol to use the extended query protocol; then the stream can be
directly streamed to the backend without further
translation/escaping/etc using a binary Bind parameter. But there's some
infrastructure work to do before that can happen.

I think there was a patch floating around on the list some time ago that
got a similar effect by using the LO interface to stream the data to a
temporary LO, then inserted into the bytea from the LO. I can't find it
right now though; the archives.postgresql.org search page seems to be
broken again (it's returning 0 results for many searches -- e.g.
searching for "inputstream" in pgsql-jdbc in the last year returns 0
results which is obviously wrong).

You could use LOs instead of bytea if you have some flexibility in your
schema; the LO interface should stream nicely. It's nastier to deal with
LOs on the JDBC side, though: you'll need to drop the driver into a
previous-version-compatibility mode (7.1?) or use the LO manager
directly. Also LOs don't play well with autocommit.

-O