This is more a cautionary tale than anything else. It surprised me.
Setup
Hibernate 3.2.1.GA, annotations.
Sybase jConnect 6.05 JDBC driver
Sybase ASE 15 database
The Story
I've been investigating a very strange problem where I get an assertion error at some point in my code due to "duplicate objects" in the session. It turns out the problem is due to an insert failing but no exception occurring.
This is because Sybase reports certain numeric errors as SQLWarnings instead of SQLExceptions. Hibernate appears not to check for those warnings and thus thinks the insert succeeded.
Here is the scenario:
Using Hibernate annotations, I have an entity with a BigDecimal field called "cost". It is defined in the database as NUMERIC(10,4). I use an IDENTITY column as the PK.
I try and insert a value that has more than 4 decimal places, e.g. "123.123456". The record is not inserted but no exception occurs. The PK value is returned as "0".
If this happens more than once, I end up with two objects in the session with the same PK of "0". This is a problem.
Reason
The problem is that Sybase doesn't throw SQLException when a numeric error occurs. They mention it here:
http://www.sybase.com/detail?id=1037380#BABHCIBG
They suggested that one has to handle the warning. In my case I found these two warnings had occurred:
Code:
SQLState: 01ZZZ, Error Code: 3624, Message: Truncation error occurred.
SQLState: 01ZZZ, Error Code: 3621, Message: Command has been aborted.
These warnings come from the ResultSet. There are no warnings attached to the connection. No warnings were logged by Hibernate.
Workaround
I'm going to use annotations to define validation on my Entity to match the database. Then the insert won't even be attempted if the precision is wrong.
Solution
It would be great if Hibernate could detect warnings on the resultset and log them (at least). Especially in org.hibernate.id.IdentityGenerator$InsertSelectDelegate