Postgresql, JDBC, and streaming BLOBs

My guess is, that you have mixed up OID and BYTEA style blobs. Large binary objects are stored indirecty with OID columns in Postgres. The actual file data is stored somewhere outside the database table by Postgres. The column just contains an object identifier that is associated internally with the blob. For instance:

janko=# CREATE TABLE blobtest1 (name CHAR(30), image OID);CREATE TABLE                                              janko=# INSERT INTO blobtest1 VALUES ('stackoverflow', lo_import('/tmp/stackoverflow-logo.png'));INSERT 0 1janko=# SELECT * FROM blobtest1;              name              | image--------------------------------+------- stackoverflow                  | 16389(1 row)

If you use the ResultSet#getBlob(String) method, than an OID style column is expected. getBlob reads the data from the column and converts it to a Long. Then it tries to read the associated binary data from its internal storage.

On the other hand, with BYTEA you can place small pieces of binary data directly in your DB. For instance:

janko=# CREATE TABLE blobtest2 (name CHAR(30), image BYTEA);CREATE TABLEjanko=# INSERT INTO blobtest2 VALUES ('somebinary', E'\\336\\255\\276\\357\\336\\255\\276\\357');INSERT 0 1janko=# SELECT * FROM blobtest2;              name              |              image--------------------------------+---------------------------------- somebinary                     | \336\255\276\357\336\255\276\357(1 row)

Here, the data column directly contains the binary data. If you try to use getBlob on such a column, the data will still be interpreted as an OID but obviously it will not fit into a Long. Let's try this on the database, we just created:

groovy:000> import java.sql.*===> [import java.sql.*]groovy:000> Class.forName("org.postgresql.Driver");===> class org.postgresql.Drivergroovy:000> db = DriverManager.getConnection("jdbc:postgresql:janko", "janko", "qwertz");===> org.postgresql.jdbc4.Jdbc4Connection@3a0b2c64groovy:000> ps = db.prepareStatement("SELECT image FROM blobtest2 WHERE name = ?");===> SELECT image FROM blobtest2 WHERE name = ?groovy:000> ps.setString(1, "somebinary")===> nullgroovy:000> rs = ps.executeQuery()===> org.postgresql.jdbc4.Jdbc4ResultSet@66f9104agroovy:000> rs.next()===> truegroovy:000> rs.getBlob("image")ERROR org.postgresql.util.PSQLException: Bad value for type long : \336\255\276\357\336\255\276\357        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong (AbstractJdbc2ResultSet.java:2796)        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong (AbstractJdbc2ResultSet.java:2019)        at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob (Jdbc4ResultSet.java:52)        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob (AbstractJdbc2ResultSet.java:335)        at groovysh_evaluate.run (groovysh_evaluate:3)        ...

Would the PostgreSQL docs for "Storing Binary Data" help?

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

The section titled "Retrieving the image from the Large Object", its at the bottom of the page, might help.

byte [] b = null;while (m_ResultSet.next()) {    for (int i = 1; i <= m_ResultSet.getMetaData().getColumnCount(); i++) {        b =  m_ResultSet.getBytes(i);    }}String str = "";for (byte i : b){    str+=(char)i;}