Postgres Pro
Downloads
Home   >   mailing lists

Thread: BLOBS

BLOBS

From
"Freddy Villalba Arias"
Date:

Hi,

 

I’m a newbie to PostgreSQL and I’d like to hear some “insight” info about the “bytea” data type (preferably from people that have actually used them). From what I’ve read so far (online docs), it doesn’t seem to be that similar to the BLOB data type specified by SQL standard (for instance, Oracle’s BLOB).

 

(1) Does the JDBC driver handle all the peculiarities described on the docs?

 

(2) Has somebody worked both with PostgreSQL and Oracle (independently)? Are there many differences between BLOBs through JDBC on either DBMS?

 

Thanks in advance.

Freddy.

Re: BLOBS

From
"David Wall"
Date:
> I'm a newbie to PostgreSQL and I'd like to hear some "insight" info about
the "bytea" data type (preferably from people that have actually used them).
> From what I've read so far (online docs), it doesn't seem
> to be that similar to the BLOB data type specified by SQL standard (for
instance, Oracle's BLOB).

I've used regular PG blobs and they don't use bytea, but use the LO
interface underneath and using datatypes of OID in the db.  The only issue
for us is that we have to schedule a daily job that runs 'vacuumlo' and our
backup scripts need the options to also backup the LOs.  It's worked fine.

> (2) Has somebody worked both with PostgreSQL and Oracle (independently)?
Are there many
> differences between BLOBs through JDBC on either DBMS?

We use both in our code.  To use the JDBC blob interfaces, we found we
needed to create a special class that implements the java.sql.Blob interface
and handles the data going in and out of the db.  I don't know why JDBC
didn't create some nice Blob wrappers for more traditional stuff, like one
that works with a file descriptor, another with a byte array, etc.

The only difference I recall between the two implementations was on
inserting a new blob.  In Oracle, we had to actually insert an empty_blob()
and then follow that up with an SQL UPDATE to set the blob data.  I'm not
sure why they need two SQL calls to insert a blob, but that's how it appears
to "work" in Oracle.  In PG, you can do the INSERT as expected by specifying
a '?' param in the prepared statement and then using the setBlob() call
passing in your object that implements the Blob interface.

David

Re: BLOBS

From
Jan de Visser
Date:
On April 26, 2004 11:54 am, David Wall wrote:
> n Oracle, we had to actually insert an empty_blob()
> and then follow that up with an SQL UPDATE to set the blob data.  I'm not
> sure why they need two SQL calls to insert a blob, but that's how it
> appears to "work" in Oracle.

OT for a postgresql list, but if you default the column to EMPTY_BLOB() you
don't have to do that:

  ...
  myBlob   BLOB DEFAULT EMPTY_BLOB(),
  ...

Oh, and have you had the cool one yet where you update a BLOB (or CLOB) with
less data then already in there and you end up with you new data plus the
'end' of the original BLOB? You need to reset w/ EMPTY_BLOB() prior to
updating a BLOB as well.

Oh, the suckiness of Oracle. Don't get me started.

JdV!!

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------