Hi Paul, Following is what Derby's Reference Guide says about IDENTITY_VAL_LOCAL
IDENTITY_VAL_LOCAL Derby supports the IDENTITY_VAL_LOCAL function. Syntax: IDENTITY_VAL_LOCAL ( ) The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause. The IDENTITY_VAL_LOCAL function has no input parameters. The result is a DECIMAL(31,0), regardless of the actual data type of the corresponding identity column. The value returned by the IDENTITY_VAL_LOCAL function is the value assigned to the identity column of the table identified in the most recent single row INSERT statement.The INSERT statement must contain a VALUES clause on a table containing an identity column. The assigned value is an identity value generated by Derby. The function returns a null value when a single row INSERT statement with a VALUES clause has not been issued for a table containing an identity column. The result of the function is not affected by the following: • A single row INSERT statement with a VALUES clause for a table without an identity column • A multiple row INSERT statement with a VALUES clause • An INSERT statement with a fullselect So, as long as there is a single row INSERT statement with a VALUES clause on the table with the generated always column, this function should work fine for what you are trying to achieve. Also, there are quite a few test cases for this function in org.apache.derbyTesting.functionTests.tests.lang.autoincrement.sql to see some examples of this function. Hope this helps, Mamta On 5/12/05, Paul J. Lucas <[EMAIL PROTECTED]> wrote: > If I have a tables like: > > CREATE TABLE location ( > location_id INT NOT NULL PRIMARY KEY > GENERATED ALWAYS AS IDENTITY, > path VARCHAR(255) NOT NULL UNIQUE > ); > > CREATE TABLE image ( > image_id INT NOT NULL PRIMARY KEY > GENERATED ALWAYS AS IDENTITY, > file_name VARCHAR(64) NOT NULL, > location_id INT REFERENCES location, > date_time TIMESTAMP NOT NULL > ); > > I want to be able to insert a new location, obtain the > auto-generated location_id, and use that in an insert of a new > image. > > If I do the fisrt insert, then: > > SELECT IDENTITY_VAL_LOCAL() FROM location; > > then use that value for the second insert image.location_id, > will I be guaranteed to get the right value for location_id, > i.e., the one for the first insert and not some other insert > that may have happened in between my firt insert and the > SELECT? > > Pharsed another way, are the operations of: > > INSERT INTO location ... ; > SELECT IDENTITY_VAL_LOCAL() FROM location; > INSERT INTO image ... ; > > atomic so that the value I use in the second insert will be the > correct one? > > If not, how do I do what I want? > > When I was doing this sort of thing using Oracle, Oracle has > "sequences" that can be used for this purpose. I suppose that > for Derby I could have a seperate table that's just: > > CREATE TABLE location_id ( > next_id INT NOT NULL PRIMARY KEY > GENERATED ALWAYS AS IDENTITY, > junk INT > > ); > > then, before I do my first insert, do: > > INSERT INTO location_id (junk) VALUES (0); > SELECT IDENTITY_VAL_LOCAL() FROM location_id; > > then use *that* location_id for both of my inserts. But is this > necessary? > > Thanks. > > - Paul >
