Hi folks,

I am using hibernate 2.0 in a setup with hsql (1.7.3.1) and xdoclet (1.2.2) in an web-app running on Tomcat 4.0.6. The db is defined as a jndi ressource within Tomcat and starts up quite nicely. Querying works fine, saving a new object through hibernate doesn't however. Following is part of the errors stack trace from my log:

WARN 17:10:04 (JDBCExceptionReporter.java.logExceptions) - SQL Error: -11, SQLState: 37000
ERROR 17:10:04 (JDBCExceptionReporter.java.logExceptions) - Unexpected token: FOR in statement [select next_hi from hibernate_unique_key for update]
DEBUG 17:10:04 (JDBCTransaction.java.rollback) - rollback
DEBUG 17:10:04 (SessionImpl.java.afterTransactionCompletion) - transaction completion
DEBUG 17:10:04 (JDBCTransaction.java.toggleAutoCommit) - re-enabling autocommit
ERROR 17:10:04 (DBconnector.java.setObject) - setObject Fehler beim speichern!
net.sf.hibernate.exception.SQLGrammarException: Could not save object
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:792)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:747)

The problem seems to be the hibernate IdentifierProvider in combination with hsql. I have tried most of hibernate's identifier generators (sequence, identity, native, hilo...) allways with the same result. I obviously want to tell hibernate that hsqldb has to worry about unique keys but can't seem to make myself understood :(
Here is part of the definition from one of my mapping files:

<snip>
...
<class name="de.offis.myproject.model.User" table="user" dynamic-update="false" dynamic-insert="false">
<id name="id" column="id" type="long" unsaved-value="0" >
<generator class="identity"></generator>
</id>
...
</snip>

And here the resulting sql for this class:
create table user ( id integer generated by default as identity (start with 1), ... );

I wonder whether anyone has set this up successfully (I would hope so) and could share some insight on her/his configuration and tips & tricks.

TIA

Jan