Hi,

I managed to get Blobs to work in Hibernate with PostgreSQL 7.3, but it required some tweaking.

PG 7.3 and the corresponding JDBC driver appears to support ResultSet.getBlob and PreparedStatement.setBlob with OID columns. I encountered two issues while trying to implement this:

- Hibernate's BlobType calls stmt.setBinaryStream instead of stmt.setBlob when the passed-in jvaa.sql.Blob is an instanceof net.sf.hibernate.Blob. setBinaryStream breaks with OID columns, though (error like "oidin: error in [[content]]") The fix I implemented was a new Type that extends BlobType, overriding the set method to call stmt.setBlob properly.

An alternative workaround is to make your own dummy java.sql.Blob implementation that won't match the instanceof test in BlobType. But I like being able to use the standard Hibernate.createBlob method at runtime.

- The PostgreSQLDialect class maps java.sql.Blob to a BYTEA column instead of OID. That only affects the DDL-creation tools and you can always replace the column by hand.

Would it make sense for Hibernate to incorporate either of these changes? Is there a good reason for BlobType to ever call setBinaryStream instead of setBlob? And should java.sql.Blob map to OID, since byte[] already maps to BYTEA?

-- Bill

Check out http://doc.postgresintl.com/jdbc/ch08.html. There are two schemes for BLOBs in Postgres. Hibernate is using the BYTEA approach.

In looking at the details of the OID approach, it seems there is a lot more work needed to use the Postgres JDBC API to handle deletes and other transaction integrity issues, above using setBLOB and getBLOB.

Check out http://archives.postgresql.org/pgsql-jdbc/2003-09/msg00105.php. It says that the JDBC driver does not really support SQL LOBs yet. The v7.5 driver is in development now, and will deal with LOBs properly.

To sum up my ramblings, I'd say they we should wait for the JDBC driver to do LOBs properly before changing Hibernate.


Sherman

Thanks for the links. Unfortunately, the BYTEA column type does not work with BlobType. I can save new objects (using Hibernate.createBlob()) but retrieving the objects results in an error ("Bad integer [content]"). The underlying ResultSet.getBlob() (called from BlobType) just doesn't work on BYTEA--getBinaryStream would probably work, though. (In this case BlobType's setBinaryStream call is a feature. :-))

Adding "?compatible=7.1" to the JDBC URL makes the insertion break instead ("ERROR: column "blob" is of type BYTEA but expression is of type integer").

Of course I could just change the column to a byte[] which works fine with BYTEA. But then you lose streaming, i.e., the whole binary content is pulled into memory at once. That isn't practical for large files, especially since 99% of the time we just want to pipe the LOB's InputStream directly to the servlet response's OutputStream without any processing.

Is there a better way to handle streaming binary data in PostgreSQL from Hibernate? There are obvious problems with OIDs (deletion is a biggie) but it seems oh-so-close to the way things are supposed to work.

Why not go and put some pressure on the postgres guys to fix this stuff. It is not our fault. If we could possibly fix this (which i don't think we can), then certainly the guys who write the postgres driver can fix it even more easily.

Thanks for the reply. It is clear that the PostgreSQL JDBC driver does not completely support the standard getBlob/setBlob methods, so I'll start that as a discussion on the pgsql-jdbc list.

Given that, I'm still not sure why BlobType would call stmt.setBinaryStream instead of stmt.setBlob. The Javadoc for PreparedStatement implies that setBinaryStream is to be used for SQL LONGVARBINARY columns, and setBlob for BLOB columns. So it seems like even a standards-compliant JDBC driver could prohibit the methods from being used interchangeably on different underlying SQL types (although many JDBC drivers are lenient in this regard).

A JDBC driver casts everything you setBlob() to its own implementation of java.sql.Blob (it's only an interface). You have to create a Blob object with the JDBC drivers class, not Hibernate.createBlob() etc. This is actually only for Oracle, which has problematic Blob support.