Обсуждение: jdbc lob and postgresql

Hello all,

I am contemplating to use postgresql for a product that requires to manipulate many blobs.
From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api?
Can I just execute a statement insert into (..) that has a blob and do a getBlob to retrieve it?
I really appreciate your time.
Thanks
- Ana

Anu Padki wrote on 12.12.2007 20:09:
> Hello all,
>
> I am contemplating to use postgresql for a product that requires to
> manipulate many blobs.
> From the documentation I am not clear if one has to use postgres api to
> insert/update/select blobs or one can use standard jdbc api?
> Can I just execute a statement insert into (..) that has a blob and do a
> getBlob to retrieve it?

Using a PreparedStatement with setBinaryStream() to insert the blob and
getBinaryStream() to read the BLOB works fine for me.

Thomas

On Thu, 13 Dec 2007, Thomas Kellerer wrote:

>> I am contemplating to use postgresql for a product that requires to
>> manipulate many blobs.
>> From the documentation I am not clear if one has to use postgres api to
>> insert/update/select blobs or one can use standard jdbc api?
>> Can I just execute a statement insert into (..) that has a blob and do a
>> getBlob to retrieve it?
>
> Using a PreparedStatement with setBinaryStream() to insert the blob and
> getBinaryStream() to read the BLOB works fine for me.
>

It depends how you want to handle binary data on the server side.
get/setBinaryStream only work with the bytea data type.  getBlob/setBlob
only work with real large objects (oid data type).  Both methods work,
it's just a tradeoff on how you want handle it on the server.  The API
downside of Blob is that only JDBC4 offers a portable way to create a Blob
(Connection.createBlob) and the pg driver does not implement that yet.

Kris Jurka

Kris Jurka, 13.12.2007 08:24:
>> Using a PreparedStatement with setBinaryStream() to insert the blob
>> and getBinaryStream() to read the BLOB works fine for me.
>>
>
> It depends how you want to handle binary data on the server side.
> get/setBinaryStream only work with the bytea data type.  getBlob/setBlob
> only work with real large objects (oid data type).  Both methods work,
> it's just a tradeoff on how you want handle it on the server.  The API
> downside of Blob is that only JDBC4 offers a portable way to create a
> Blob (Connection.createBlob) and the pg driver does not implement that yet.

I just looked through the manual but could not find an "oid data type".
I thought that oids were some kind of unique row "address" (at least
that's what http://www.postgresql.org/docs/8.2/static/datatype-oid.html
sates as far as I can tell)

Can you point me to the manual for these types of BLOBs are described?
The "Data Types" chapter does not list them as far as I can tell.

Background: I'm maintaining a SQL front end (Java/JDBC based) which
supports reading and writing of BLOBs and I would like to make sure that
I also support these "oid data type" BLOBs

Regards
Thomas

On Thu, 13 Dec 2007, Thomas Kellerer wrote:

> Can you point me to the manual for these types of BLOBs are described? The
> "Data Types" chapter does not list them as far as I can tell.
>

Apparently the only documentation appears to be in the client interfaces
section...

http://www.postgresql.org/docs/8.2/static/largeobjects.html

The JDBC documentation describes some of the tradeoffs between oid and
bytea data types, but doesn't describe the getBlob/setBlob API, only a PG
specific version.

http://jdbc.postgresql.org/documentation/82/binary-data.html

Kris Jurka