Page 1 of 1

Derby save blob/clob

PostPosted: Fri Apr 06, 2012 8:42 pm
by b0c1

I try to save a CLOB or Blob field from derby db.
It's say: SQL Error [20000] [XJ215]: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after


Re: Derby save blob/clob

PostPosted: Sat Apr 07, 2012 12:36 pm
by Serge

Short answer: you have to be in transactional mode to be able to operate LOBs in Derby. Just click on appropriate button on main toolbar and disable auto-commit.

Long answer: different databases handle BLOB/CLOB columns in a different ways. Some (like MySQL) uses plain binary data and return it along with other columns data, others uses LOB locators. In many databases (like DB2 or Derby) LOB locator is valid only within a single transaction. Once you perform commit/rollback it becomes invalid. In auto-commit mode comit is perfromed after each query, so after you execute manual query or open some table all LOB locators (you see them as "BLOB") are invalid.
The only way to work with them is to enable transactions.
In theory I can precache LOBs for all queries but usually it is not acceptable due to bad performance (as you know LOBs can be very-very big).