Postgresql, JDBC, and streaming BLOBs

Question

I am trying to retrieve a blob from a postgres database using the jdbc drivers. It is too big to have in memory so I want to stream it as a download. I tried using the getBinaryStream method on ResultSet, but it turns out that this method actually reads it all into memory, so doesn't work for large file.

Apparently, one can use the getBlob method on the resultset and the presumeably get the inputstream from the blob and go from there, but that is where I run into my problem.

PreparedStatement ps = con.prepareStatement("select data from file_data WHERE ID = ?");
ps.setLong(1,file.fileData.id)
ResultSet rs = ps.executeQuery()
if(rs.next()){
        rs.getBlob("data")

That is the code I'm running. When it gets to that last line it throw out an error that I cannot make sense of...

org.postgresql.util.PSQLException: Bad value for type long : xxxxxx

"xxxxxx" then is the contents of the file. You can imagine that gets quite long, but not really the point.

I'm stuck here. Does anyone have any ideas on what is going on? Heck I'll even take alternative methods for streaming large blobs as a download.

user_136954 · Answer

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 1
janko=# 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 TABLE
janko=# INSERT INTO blobtest2 VALUES ('somebinary', E'\336\255\276\357\336\255\276\357');
INSERT 0 1
janko=# 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.Driver
groovy:000> db = DriverManager.getConnection("jdbc:postgresql:janko", "janko", "qwertz");
===> org.postgresql.jdbc4.Jdbc4Connection@3a0b2c64
groovy:000> ps = db.prepareStatement("SELECT image FROM blobtest2 WHERE name = ?");
===> SELECT image FROM blobtest2 WHERE name = ?
groovy:000> ps.setString(1, "somebinary")
===> null
groovy:000> rs = ps.executeQuery()
===> org.postgresql.jdbc4.Jdbc4ResultSet@66f9104a
groovy:000> rs.next()
===> true
groovy: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)
        ...

user_251345 · Answer

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.

user_14907397 · Answer

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;
}

Postgresql, JDBC, and streaming BLOBs

3 Answers