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 <[EMAIL PROTECTED]> 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
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (revision 169878)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (working copy)
@@ -691,3 +691,35 @@
drop table t1;
drop table t2;
drop table t3;
+
+-- test IDENTITY_VAL_LOCAL function with 2 different connections
+-- connection one
+connect 'wombat' as conn1;
+create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
+create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
+-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn1
+values IDENTITY_VAL_LOCAL();
+commit;
+-- connection two
+connect 'wombat' as conn2;
+-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn2
+values IDENTITY_VAL_LOCAL();
+insert into t2 (c22) values (1);
+-- IDENTITY_VAL_LOCAL() will return 201 because there was single row insert into table t2 with identity column on this connection conn2
+values IDENTITY_VAL_LOCAL();
+set connection conn1;
+-- IDENTITY_VAL_LOCAL() will continue to return NULL because no single row insert into table with identity column yet on this connection conn1
+values IDENTITY_VAL_LOCAL();
+insert into t1 (c12) values (1);
+-- IDENTITY_VAL_LOCAL() will return 101 because there was single row insert into table t1 with identity column on this connection conn1
+values IDENTITY_VAL_LOCAL();
+set connection conn2;
+-- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1
+values IDENTITY_VAL_LOCAL();
+-- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()
+commit;
+values IDENTITY_VAL_LOCAL();
+drop table t1;
+drop table t2;
+
+
Index: java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (revision 169878)
+++ java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (working copy)
@@ -1379,4 +1379,60 @@
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
-ij>
+ij> -- test IDENTITY_VAL_LOCAL function with 2 different connections
+-- connection one
+connect 'wombat' as conn1;
+ij(CONN1)> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
+0 rows inserted/updated/deleted
+ij(CONN1)> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
+0 rows inserted/updated/deleted
+ij(CONN1)> -- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn1
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+NULL
+ij(CONN1)> commit;
+ij(CONN1)> -- connection two
+connect 'wombat' as conn2;
+ij(CONN2)> -- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn2
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+NULL
+ij(CONN2)> insert into t2 (c22) values (1);
+1 row inserted/updated/deleted
+ij(CONN2)> -- IDENTITY_VAL_LOCAL() will return 201 because there was single row insert into table t2 with identity column on this connection conn2
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+201
+ij(CONN2)> set connection conn1;
+ij(CONN1)> -- IDENTITY_VAL_LOCAL() will continue to return NULL because no single row insert into table with identity column yet on this connection conn1
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+NULL
+ij(CONN1)> insert into t1 (c12) values (1);
+1 row inserted/updated/deleted
+ij(CONN1)> -- IDENTITY_VAL_LOCAL() will return 101 because there was single row insert into table t1 with identity column on this connection conn1
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+101
+ij(CONN1)> set connection conn2;
+ij(CONN2)> -- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1
+values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+201
+ij(CONN2)> -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()
+commit;
+ij(CONN2)> values IDENTITY_VAL_LOCAL();
+1
+-------------------------------
+201
+ij(CONN2)> drop table t1;
+0 rows inserted/updated/deleted
+ij(CONN2)> drop table t2;
+0 rows inserted/updated/deleted
+ij(CONN2)>
