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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | kooifei | 2009-02-06 04:22:25 | Need help installing/setting up JDBC on Solaris 10 |
Previous Message | Kris Jurka | 2009-02-05 23:33:22 | Re: JDBC Blob helper class & streaming uploaded data into PG |