I could not see where this is explictly defined in the docs. I have seen this cause problems with applications using SQL Server databases and so it is probably worth spelling out.
-- Jeremy
Daniel John Debrunner wrote:
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
