Mapping BLOBs in DB2 9 with Hibernate/JPA

Working with Hibernate and DB2 9, if you have a byte[] member in your entity which is supposed to be mapped to a BLOB column, you'll probably get:

ERROR [org.hibernate.util.JDBCExceptionReporter] - <DB2 SQL Error: SQLCODE=-301, SQLSTATE=07006, SQLERRMC=2, DRIVER=3.61.75>
ERROR [org.hibernate.event.def.AbstractFlushingEventListener] - <Could not synchronize database state with session>
org.hibernate.exception.SQLGrammarException: could not insert...

Which in general means wrong data type!

After throwing lots of hooks into the sea of Google & IBM, I came up with this one - thanks to Steve for his excellent fish

www.badlildog.com/words/

Which gave me a very good starting point. Noticing byte[] is mapped by hibernate to org.hibernate.type.

Materialized

BlobType,

I tried modifying the mapping by explicitly specifying the type as blob or binary (Hibernate specific)

@Type(type="blob")

or changing the member type to Byte[], InputStream or even java.sql.Blob (which is a bad practice anyway). Well, the exception was gone, but, for some reason, the actual binary data was not inserted to the DB!

So getting back to steve's post, the only solution wich worked for me was overriding the connection's progressive streaming behaviour by adding the following property to the connection url:

fullyMaterializeInputStreams=true;

Hope this will save you a couple of fishing hours

P.S. - Make sure you're using the latest DB2 JDBC driver, since the version shipped with your DB2 might contain some bugs...

Some resources and background:

publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.java.doc/doc/cjvjdlob.htm

publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm

publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.messages.sql.doc/doc/msql00301n.html

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com