Thread: JDBC Blob helper class & streaming uploaded data into PG

Does anybody have a JDBC Blob helper class so we can use the
setBlob()/getBlob() calls in JDBC for PG 8.3?  It would be a class that
implements the java.sql.Blob interface.

Does anybody have any code samples of how I'd go about reading a file in
blocks, compressing the block and then streaming that block into a Blob
(LO)?  We are starting to process very large files uploaded in a web app
and so it's no longer possible to keep it all in memory before it's
stored in PG.

The pseudo-code is something like:

while ( hasMoreHttpPostData )
{
  read chunk from HTTP POST
  compress chunk
  write chunk to DB
}

If possible, we'd like to use a streaming cipher (for encryption) and
GZIP to write the compressed data and a Blob to store to the database.

Do these APIs support writing chunks, or must we actually loop through
each byte (which seems painfully slow).

Thanks,
David

On Wed, 4 Feb 2009, David Wall wrote:

> Does anybody have a JDBC Blob helper class so we can use the
> setBlob()/getBlob() calls in JDBC for PG 8.3?  It would be a class that
> implements the java.sql.Blob interface.

You really ought to use the driver's Blob implementation.  Right now
creating a new Blob isn't terribly straightforward.  In theory the JDBC 4
method Connection.createBlob should be used, but that has not been
implemented in the postgresql driver yet.

The best way to do this at the moment is to insert a row with an empty
blob, retrieve that blob and then write data into it.

CREATE TABLE test (id int, bigdata oid);

INSERT INTO test VALUES (1, lo_creat(-1));

ResultSet rs = stmt.executeQuery("SELECT bigdata FROM test WHERE id = 1");
rs.next();
Blob blob = rs.getBlob(1);
OutputStream out = blob.setBinaryStream(1);
// from java.util.zip. to compress the data.
GZIPOutputStream gz = new GZIPOutputStream(out);
while (!done) {
     gz.write(your data);
}
gz.close();
rs.close();

Kris Jurka

>
>> Does anybody have a JDBC Blob helper class so we can use the
>> setBlob()/getBlob() calls in JDBC for PG 8.3?  It would be a class
>> that implements the java.sql.Blob interface.
>
> You really ought to use the driver's Blob implementation.  Right now
> creating a new Blob isn't terribly straightforward.  In theory the
> JDBC 4 method Connection.createBlob should be used, but that has not
> been implemented in the postgresql driver yet.
>
> The best way to do this at the moment is to insert a row with an empty
> blob, retrieve that blob and then write data into it.
>
> CREATE TABLE test (id int, bigdata oid);
>
> INSERT INTO test VALUES (1, lo_creat(-1));
>
> ResultSet rs = stmt.executeQuery("SELECT bigdata FROM test WHERE id =
> 1");
> rs.next();
> Blob blob = rs.getBlob(1);
> OutputStream out = blob.setBinaryStream(1);
> // from java.util.zip. to compress the data.
> GZIPOutputStream gz = new GZIPOutputStream(out);
> while (!done) {
>     gz.write(your data);
> }
> gz.close();
> rs.close();
>
> Kris Jurka

Thanks, Kris.  Interesting the create empty blob and then update is used
since Oracle seems to require something similar, though we don't do much
with Oracle any more, I do recall using the EMPTY_BLOB() function and
then updating it after getting an Oracle specific Blob class.

Yeah, that's why we need a Blob class that implements streaming through
the java.sql.Blob interface.  We have our own and use simple code like
the following to INSERT (and it works for UPDATE, too):

            YoByteBlob ybb = new YoByteBlob(encryptedCompressedData);
            stmt.setBlob(1, ybb);

When SELECTING, we can use:
            java.sql.Blob dbBlob = rs.getBlob(1);
            YoByteBlob ybb = new YoByteBlob(dbBlob);

These work for us now, but our version only supports "all in memory"
byte arrays and no streaming.  I could share YoByteBlob if anybody was
interested, but we'll need to do more work to make it all work so we can
stream the data in and out.

David