From: Fred T. <fr...@us...> - 2010-04-27 08:36:16
|
Thanks, The basic issue here is the SQL standard DOES NOT have syntax for the identity value to be preallocated, nor retrieved after allocation. HSQLDB has two alternatives to your method as follows: With HSQLDB 1.8.x and later, you can use the SQL statement "CALL IDENTITY()" after an INSERT that generates an IDENTITY column value. This is per session so it is safe in a multiple connection scenario. With HSQLDB 2.x, you can use the JDBC getGeneratedKey method. Even your method can be improved. You can retrieve the next value for a SEQUENCE without creating a table. The statement NEXT VALUE FOR "generated_id_seq" can be used inside an INSERT statement INSERT INTO "mytable" ("id" ,"other" ) VALUES (NEXT VALUE FOR "generated_id_seq",?) You can also retrieve it directly CALL NEXT VALUE FOR "generated_id_seq" Fred On Mon, 26 Apr 2010 17:42 -0700, "gray" <gra...@ma...> wrote: > > Just added Hsqldb support to my http://ormlite.sourceforge.net/ ORMLite > package. Love to get some feedback on it. ORMLite is a simple ORM > framework that uses Java annotations. > > Here's the > http://ormlite.svn.sourceforge.net/viewvc/ormlite/ormlite/src/main/java/com/j256/ormlite/db/HsqldbDatabaseType.java > hsqldb-specific code I had to write. > > Because the JDBC I was using did not allow returning an auto-created > value, > I had to pre-allocate an auto-generated id using a sequence. Not being > able > to get the sequence nextval directly was a real hurdle. Is there a > better > way to do this without having to create a table just for that purpose? > > The package has to generate, for example, the following SQL to insert a > new > row: > > CREATE SEQUENCE "generatedid_id_seq" START WITH 1 > CREATE TABLE "generatedid_id_seq_table" (val integer) > INSERT INTO "generatedid_id_seq_table" VALUES (0) > CREATE TABLE "generatedid" ("id" INTEGER GENERATED BY DEFAULT AS > IDENTITY , > "other" VARCHAR(255) , PRIMARY KEY ("id") ) > SELECT NEXT VALUE FOR "generatedid_id_seq" FROM > "generatedid_id_seq_table" > INSERT INTO "generatedid" ("id" ,"other" ) VALUES (?,?) > > thanks, > gray > > -- > View this message in context: > http://old.nabble.com/added-hsqldb-support-to-ormlite-tp28370685p28370685.html > Sent from the HSQLDB - User mailing list archive at Nabble.com. > > > ------------------------------------------------------------------------------ > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > |