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:



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

Just to follow up, this problem is entirely due to the fact that:
a) Sybase fails to insert if a numeric exceeds the precision of the column (I believe Oracle would round up automatically)
b) Using an identity column means Hibernate does not do an executeUpdate(); rather they must get a ResultSet in order to grab the identity value and thus Hibernate doesn't notice that the insert failed.

I tried using a manually assigned PK and it correctly failed with a:
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

That isn't a solution for me, I'm still going to go with the Annotations Validation.

Edit: I think this is solveable. The Identity Insert could check the update count and barf if it is zero. I opened a JIRA issue: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2388