-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: PostgreSQL 7.3, Blob, and OID
PostPosted: Sat May 22, 2004 5:08 pm 
Newbie

Joined: Sat May 22, 2004 4:57 pm
Posts: 9
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


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 23, 2004 12:51 pm 
Senior
Senior

Joined: Tue Nov 25, 2003 9:35 am
Posts: 194
Location: San Francisco
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


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 23, 2004 3:01 pm 
Newbie

Joined: Sat May 22, 2004 4:57 pm
Posts: 9
sgwood wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 23, 2004 3:22 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 23, 2004 5:02 pm 
Newbie

Joined: Sat May 22, 2004 4:57 pm
Posts: 9
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).


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 23, 2004 5:04 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
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.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.