Re: [Hsqldb-user] Recalling 2 IDENTITY()'s

[fredt <fr...@us...>]

The source of the issues that have been discussed regarding IDENTITY and 
SEQUENCE values is that in Standard SQL, these are not subject to 
transaction control, meaning the next value can be modified by another 
transaction.

You need temporary storage of the values that you have used. If you are 
writing the insertion sequence as SQL, then use a temporary table with a 
single row to store the values.

// create the temp table together with table1 etc.
CREATE TEMP TABLE TT (ID1 INT, ID2 INT);

//
DELETE FROM TT;
INSERT INTO table1 (id) VALUES (NULL);
INSERT INTO TT(ID1) VALUES(IDENTITY());
INSERT INTO table2 (id) VALUES (NULL);
UPDATE TT SET ID2=IDENTITY();
INSERT INTO table3 (id, table1id, table2id) SELECT CAST(NULL AS INT), ID1, 
ID2 FROM TT;

Fred

----- Original Message ----- 
From: "Richi Plana" <ri...@ri...>
To: "HSQLdb user discussions" <hsq...@li...>
Sent: 16 November 2007 23:32
Subject: Re: [Hsqldb-user] Recalling 2 IDENTITY()'s


On Fri, 2007-11-16 at 15:54 -0600, Dimitri Maziuk wrote:
> On Friday 16 November 2007 15:42:19 Richi Plana wrote:
> > Hi,
> >
> > I need to create a set of SQL statements where INSERTs will be made to
> > two databases that have an IDENTITY each as a PRIMARY KEY and use those
> > returned IDENTITY()'s in a third INSERT call.
> >
> > It would be something like this:
> >
> > INSERT INTO table1 (id) VALUES (NULL);
> > INSERT INTO table2 (id) VALUES (NULL);
> > INSERT INTO table3 (id, table1id, table2id) VALUES (NULL, IDENTITY(1),
> > IDENTITY(0));
>
> This is the main problem with IDENTITY vs SEQUENCE -- except that current 
> SQL
> standard added this same problem to sequences as well.

Could someone provide SQL examples of how I could achieve the previous
with sequences? I'm not sure how to use the SELECT NEXT VALUE syntax in
INSERTs like I described above.
--

Richi Plana


-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user