db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Fri, 13 May 2005 19:20:22 GMT
Mamta Satoor wrote:

> The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value that
> got into generated for _any_ table with identity column using single row
> insert with values clause in the current transaction. 

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected





Mime
View raw message