Derby save blob/clob

Posted:
Fri Apr 06, 2012 8:42 pm
by b0c1
Hi!
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
Thnx
b0c1
Re: Derby save blob/clob

Posted:
Sat Apr 07, 2012 12:36 pm
by Serge
Hi,
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).
Thanks.