I was trying to add support for HSQL DB to a project that already uses Hibernate. The app makes use of cascading inserts, but with HSQL in place it kept throwing a foreign key constraint violation. So I turned on log4j debug output for:
- org.hibernate.sql (shows prepared statements)
- org.hibernate.type (shows the values bound to and retrieved from the statements)
The debug output demonstrated that Hibernate was correctly inserting the referenced row before the one with the foreign key association. So why the failure?
The debug output also showed that the value bound to the foreign key column was "0". That value is obtained by doing the INSERT and then, in HSQL's case, issuing a "call identity()" statement, which retrieves the value of the last identity generated
in the current connection.
I remembered that I'm also using c3p0 connection pooling, so I changed the max pool size to 1 for the HSQL DB configuration, and that fixed the problem of "call identity()" returning 0. It turns out HSQL DB
does not benefit from connection pooling anyway.
It remains to be seen what other problems that will cause...
