Mistake in selecting identity with H2/HSQLDB

Thanks for the patch, Peter...I don't see why we couldn't go ahead and apply it soon, but I'll leave it to Nick to review.

Does this pertain to a particular version of HSQLDB or H2? We have tests to cover this and they break for HSQLDB with this change (but not H2).

I did some research and I see that it breaks on HSQLDB. I also tried a few solutions, but the success between H2 and HSQLDB seems to be disjoint. I am not sure what the solution is, but here is a summary of ways to retrieve identity.

SELECT IDENTITY() from table;

• works on both H2 and HSQLDB but returns as many rows as there are in the table, which makes this a huge performance issue.

SELECT IDENTITY()

• works on H2 but HSQLDB complains about not having a FROM TABLE.

CALL IDENTITY()

• Supposedly works on both H2 and HSQLDB according to documentation, but I am getting a "ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: executeUpdate() cannot be used with this statement: CALL IDENTITY()" in HSQLDB and some kind of casting error in H2.

Here is a quick setup script for testing.

CREATE TABLE testing (
testing_id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY,
name varchar
);

INSERT INTO testing (name) VALUES ('one');
INSERT INTO testing (name) VALUES ('two');
INSERT INTO testing (name) VALUES ('three');

A small update: I think the reason I got "ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: executeUpdate() cannot be used with this statement: CALL IDENTITY()" from HSQLDB is because of how "CALL IDENTITY()" was classified as an update query and executed with executeUpdate, instead of executeQuery, as it should be.

As a suggestion, perhaps getGeneratedKeys() can be used instead, which should take care of any syntax difference between HSQLDB and H2.

Fixed in commit f69b992. I used Peter's suggestion of CALL IDENTITY(). I just fixed select? to accept all 'call' methods as well.