Hi,
 
I have another small patch for trigger test for IDENTITY_VAL_LOCAL. Can a committer please commit it for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Mamta Satoor <msatoor@gmail.com> wrote:
Hi,
 
I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so there is the crucial *connection* dependency identified.
 
Also, I have added one more subtest to autoincrement.sql which tests the return value of this function for 2 different connections. Can someone commit the patch for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Daniel John Debrunner <djd@debrunners.com > 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