I noticed a few small oddities in the way identity columns behave with HSQLDB, just thought I'd post them to see if anyone else has seen these particular behaviors:

* If the type of the id is 'long' and the generator class is 'native', SchemaExport tool will try to create a table with an IDENTITY column of type 'BIGINT'. HSQLDB doesn't seem to like this, so a worked around it by changing the data type to 'int'.

Didn't work:
<id name="thingId" type="long">
<generator class="native"/>
</id>

Worked:
<id name="thingId" type="int">
<generator class="native"/>
</id>

* After fixing the above error, Hibernate was throwing an exception at run time when committing a transaction containing a new object. The error message indicates that the id property was 'modified' from '0' to '0', a little confusing, as that indicates nothing has changed. The cause seems to be the fact that the data type of the property in the persisted object is different than that of the descriptor (long vs. int in this case).

Changing the type of the property in the persisted object fixed the problem.

Use a later HSQLDB, ie. the one that ships with Hibernate.

I was not able to find hsqldb in the binary distributions of Hibernate 2.0.3 or 2.1 beta 6, maybe I have missed something. I'll check in CVS. Anyway, I tried switching to HSQLDB 1.7.1a, which is the latest version from the HSQLDB site, but this did not change the behavior.

The descriptor has the following element:

<id name="thingId" type="long" unsaved-value="-2000" >
<generator class="native"/>
</id>

The persisted object is:

public class Thing
{
private long thingId;

public long getThingId() ....
public void setThingId(long thingId) ....
}

This generates the following DDL:

create table THINGS (
thingId BIGINT NOT NULL IDENTITY,
... blah blah blah ....
);

HSQLDB apparently doesn't like the way the 'thingId' column is defined, and responds with:

Wrong data type: THINGID in statement [ create table ... blah blah blah ...

Well, I guess hsqldb.jar is not in the binary distributions so that threw me off slightly. SchemaExport and my simple unit test work fine with the version of HSQLDB in the hibernate2/lib directory.

ah heh yeah my bad ... it doesn't actually ship with hibernate does it?

Nope, it sure doesn't. :) I just triple checked to make sure.

What magic version of HSQLDB is in CVS, exactly? I was surprised when the version recommended on the HSQLDB page didn't work correctly. I'll do some more digging, as the version that ships (for real) with Jboss 2.3.1 doesn't work either.

I think its a version marked with an "M", iirc.

I got the same problem. Davis, Did u get it solved by getting a newer version. Can you tell me the version of hibernate which solved the problem.

This is a hsqldb problem, use the version of hsqldb in the /jdbc folder in the Hibernate cvs/source distribution. Will that ever get fixed in the hsqldb original distribution?

No, I built HSQLDB from CVS and it worked fine.

Probably the "some bugs fixed" in the release notes of hsqldb 1.7.2 RC5