Henry’s Blog

A co-worker of mine just spent a reasonable amount of time debugging an issue with Hibernate / Sybase in our application which I think has the potential to occur in other apps, so I thought I’d drop a mail out about it.

Summary: If you are using Hibernate against Sybase (jConnect driver) with an IdentityGenerator, it’s possible to ‘persist’ an entity that contains BigDecimals without the entity actually being persisted and without an exception being thrown, possibly resulting in lost data.

Symptoms: Missing data; ‘persistent’ entity instances having a numeric identity of 0; NonUniqueObjectException if persisting multiple instances at the same time.

Links: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2388
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2830
http://www.sybase.com/detail?id=1037380#BABHCIBG

Remedy: Always scale BigDecimals before persisting

When using an IdentityGenerator, Hibernate will send the INSERT and SELECT statements as a single multi-line statement. If you try to insert an object with a mapped BigDecimal whose scale exceeds that configured for its column in the database, the Sybase driver will not insert the data; crucially it also will not raise a SQLException. Instead it signals that nothing was inserted with an updateCount of 0; the trailing select @@identity statement gets executed normally, and returns 0 as nothing was inserted.

As there was no SQLException, Hibernate doesn’t identify this as an error state, and just assigns 0 as the ID of the ‘persisted’ object; no exception reaches your code, and you may now have a problem; no error from Hibernate for a saveOrUpdate operation would normally suggest that the object actually made it into the database, after all. If you persist multiple entities in sequence which trigger this failure state, you will receive a NonUniqueObjectException, as Hibernate will not allow multiple instances of an object with the same ID (0) per session.

It’s a pretty painful error, and something to bear in mind if you work with a lot of BigDecimals and Hibernate; the ultimate error is with the developer for not scaling the BigDecimal before persistence, but without Exceptions, this sort of thing can sneak through unit tests.