Skip site navigation (1) Skip section navigation (2)

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

From: David Wall <d(dot)wall(at)computer(dot)org>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: JDBC Blob helper class & streaming uploaded data into PG
Date: 2009-02-05 23:57:40
Message-ID: 498B7CF4.2000501@computer.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
>
>> 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




In response to

pgsql-jdbc by date

Next:From: kooifeiDate: 2009-02-06 04:22:25
Subject: Need help installing/setting up JDBC on Solaris 10
Previous:From: Kris JurkaDate: 2009-02-05 23:33:22
Subject: Re: JDBC Blob helper class & streaming uploaded data into PG

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group