db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Kevyn_Schnei...@administaff.com>
Subject RE: Atomicity of using IDENTITY_VAL_LOCAL()
Date Wed, 18 May 2005 14:38:03 GMT
What Mirit is saying is that your suggestion does not work.  I share in Mirit's experience.
 I've unsubscribed to every possible Derby subscription *twice* and still get the emails.
 
 
The unsubscribe feature is *broken*.
 
Kevyn

-----Original Message-----
From: Mamta Satoor [mailto:msatoor@gmail.com]
Sent: Wednesday, May 18, 2005 9:25 AM
To: Derby Development
Subject: Re: Atomicity of using IDENTITY_VAL_LOCAL()


Hi Mitri,
 
To unsubscribe, send an email to derby-dev-unsubscribe@db.apache.org or 
derby-dev-digest-unsubscribe@db.apache.org depending on how you are 
subscribed to the mailing list.  http://incubator.apache.org/derby/derby_mail.html 
has more information about various Derb mailing lists.
 
Mamta

 
On 5/18/05, Mirit Naim < MiritN@enigma.com> wrote: 

Hello everyone.
 
How can I remove myself from this mailing list? I tried mailing the "unsubscribe" address,
but I'm still here...
 
Thanks.


-----Original Message-----
From: Mamta Satoor [mailto:  <mailto:msatoor@gmail.com> msatoor@gmail.com]
Sent: Wednesday, May 18, 2005 4:39 PM
To: Derby Development
Subject: Re: Atomicity of using IDENTITY_VAL_LOCAL()


Hi Satheesh,
 
Sorry for both the gottchas.
 
Here is the new patch with right files.
 
thanks,
Mamta

 
On 5/17/05, Satheesh Bandaram < satheesh@sourcery.org  <mailto:satheesh@sourcery.org>
> wrote: 

Mamta, looks like this patch was sent to wrong alias... Should it be sent to DerbyDev? :-)


Also this patch seems to include a modification to 'java/client/org/apache/derby/client/am/ResultSet.java'
that I suspect should not be in the patch. If so, can you remove that and resubmit? 

Satheesh 

Mamta Satoor wrote:



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  <mailto: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








  _____  


Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql

===================================================================

--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql	(revision
170188)

+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql	(working
copy)

@@ -719,7 +719,25 @@

 -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()

 commit;

 values IDENTITY_VAL_LOCAL();

+-- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()

+values IDENTITY_VAL_LOCAL();

 drop table t1;

 drop table t2;

 

+-- A table with identity column has an insert trigger which inserts into another table 

+-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the 

+-- statement table and not for the table that got modified by the trigger

+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);

+create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values
(1);

+values IDENTITY_VAL_LOCAL();

+insert into t1 (c12) values (1);

+-- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. 

+-- It will not return 201 which got generated for t2 as a result of the trigger fire.

+values IDENTITY_VAL_LOCAL();

+select * from t1;

+select * from t2;

+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
170188)

+++ java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out	(working copy)

@@ -1431,8 +1431,46 @@

 1                              

 -------------------------------

 201                            

+ij(CONN2)> -- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()

+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)> -- A table with identity column has an insert trigger which inserts into another
table 

+-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the 

+-- statement table and not for the table that got modified by the trigger

+create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12
int);

+0 rows inserted/updated/deleted

+ij(CONN2)> create table t2 (c21 int generated always as identity (start with 201, increment
by 5), c22 int);

+0 rows inserted/updated/deleted

+ij(CONN2)> create trigger t1tr1 after insert on t1 for each row mode db2sql insert into
t2 (c22) values (1);

+0 rows inserted/updated/deleted

+ij(CONN2)> values IDENTITY_VAL_LOCAL();

+1                              

+-------------------------------

+201                            

+ij(CONN2)> insert into t1 (c12) values (1);

+1 row inserted/updated/deleted

+ij(CONN2)> -- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. 

+-- It will not return 201 which got generated for t2 as a result of the trigger fire.

+values IDENTITY_VAL_LOCAL();

+1                              

+-------------------------------

+101                            

+ij(CONN2)> select * from t1;

+C11        |C12        

+-----------------------

+101        |1          

+ij(CONN2)> select * from t2;

+C21        |C22        

+-----------------------

+201        |1          

+ij(CONN2)> drop table t1;

+0 rows inserted/updated/deleted

+ij(CONN2)> drop table t2;

+0 rows inserted/updated/deleted

 ij(CONN2)> 

Index: java/client/org/apache/derby/client/am/ResultSet.java

===================================================================

--- java/client/org/apache/derby/client/am/ResultSet.java	(revision 170188)

+++ java/client/org/apache/derby/client/am/ResultSet.java	(working copy)

@@ -2369,6 +2369,10 @@

                 agent_.logWriter_.traceEntry(this, "updateRow");

             }

             updateRowX();

+            //the cursor is not positioned on the updated row after updateRow.

+            //User needs to issue ResultSet.next to reposition the ResultSet

+            //on a valid row

+            isValidCursorPosition_ = false;

         }

     }

 

@@ -2379,32 +2383,55 @@

                     "row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.");

         }

 

-        // No-op if none of the columns were updated and updateRow() is called, just return.

+        // If no updateXXX has been called on this ResultSet object, then

+        // updatedColumns_ will be null and hence no action required

         if (updatedColumns_ == null) {

             return;

         }

 

-        if (preparedStatementForUpdate_ == null) {

-            getPreparedStatementForUpdate();

+        // updateXXX has been called on this ResultSet object, but check if it

+        // has been called on the current row. If no column got updated on this

+        // current row, then just return.

+        boolean didAnyColumnGetUpdated = false;

+        for (int i=0; i < updatedColumns_.length; i++) {

+            if (columnUpdated_[i]) {

+                didAnyColumnGetUpdated = true;

+                break;

+            }

         }

+        if (didAnyColumnGetUpdated == false) 

+            return;

 

+        // User might not be updating all the updatable columns selected in the

+        // select sql and hence every updateRow on the same ResultSet can be

+        // potentially different than the previous one. Because of that, we

+        // should get a new prepared statement to do updates every time

+        getPreparedStatementForUpdate();

+

         // build the inputs array for the prepared statement for update

+        int paramNumber = 0;

         for (int i = 0; i < updatedColumns_.length; i++) {

             if (resultSetMetaData_.sqlxUpdatable_[i] == 1) {

+                // Since user may choose not to update all the columns in the

+                // select list, check first if the column has been updated

+                if (columnUpdated_[i] == false)

+                    continue;

+                paramNumber++;

+

                 // column is updated either if the updatedColumns_ entry is not null,

                 // or if the updatedColumns_ entry is null, but columnUpdated_ boolean is

                 // set to true, which means columns is updated to a null.

                 if (updatedColumns_[i] != null ||

                         (updatedColumns_[i] == null && columnUpdated_[i])) {

-                    preparedStatementForUpdate_.setInput(i + 1, updatedColumns_[i]);

+                    preparedStatementForUpdate_.setInput(paramNumber, updatedColumns_[i]);

                 } else {

                     // Check if the original column is null.  Calling CrossConverters.setObject
on a null

                     // column causes "Data Conversion" Exception.

                     Object originalObj = getObject(i + 1);

                     if (originalObj == null) {

-                        preparedStatementForUpdate_.setInput(i + 1, null);

+                        preparedStatementForUpdate_.setInput(paramNumber, null);

                     } else {

-                        preparedStatementForUpdate_.setInput(i + 1, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i],
originalObj));

+                        preparedStatementForUpdate_.setInput(paramNumber, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i],
originalObj));

                     }

                 }

             }

@@ -2431,6 +2458,10 @@

                 agent_.logWriter_.traceEntry(this, "deleteRow");

             }

             deleteRowX();

+            //the cursor is not positioned on the deleted row after deleteRow.

+            //User needs to issue ResultSet.next to reposition the ResultSet

+            //on a valid row

+            isValidCursorPosition_ = false;

         }

     }

 

@@ -2502,6 +2533,11 @@

                         "row or if this ResultSet object has a concurrency of CONCUR_READ_ONLY.");

             }

 

+            // if not on a valid row, then do not accept cancelRowUpdates call

+            if (!isValidCursorPosition_) 

+                throw new SqlException(agent_.logWriter_, "Invalid operation" +

+                        "cancelRowUpdates at current cursor position.");

+

             // if updateRow() has already been called, then cancelRowUpdates should have

             // no effect.  updateRowCalled_ is reset to false as soon as the cursor moves
to a new row.

             if (!updateRowCalled_) {

@@ -3041,8 +3077,6 @@

         boolean foundOneUpdatedColumnAlready = false;

         String updateString = "UPDATE " + getTableName() + " SET ";

 

-        // The update tablename ... where current of cursorname for Cloudscape has

-        // to provide columns as columnname1 = ?, columnname2 = ?

         for (column = 1; column <= resultSetMetaData_.columns_; column++) {

             if (columnUpdated_[column - 1]) {

                 if (foundOneUpdatedColumnAlready) {

@@ -3069,8 +3103,8 @@

     private String buildDeleteString() throws SqlException {

         String deleteString = "DELETE FROM ";

 

-        // build the update string using the server's cursor name

-        deleteString += (getTableName() + " WHERE CURRENT OF " + getServerCursorName());

+        // build the delete string using the server's cursor name

+        deleteString += (getTableName() + " WHERE CURRENT OF \"" + getServerCursorName()
+ "\"");

 

         if (isRowsetCursor_) {

             deleteString += " FOR ROW ? OF ROWSET";

@@ -3084,12 +3118,15 @@

         if (resultSetMetaData_.sqlxRdbnam_[0] != null &&

                 !resultSetMetaData_.sqlxRdbnam_[0].equals(""))      // catalog

         {

-            tableName += resultSetMetaData_.sqlxRdbnam_[0].trim() + ".";

+            tableName += "\"" + resultSetMetaData_.sqlxRdbnam_[0] + "\".";

         }

-        if (!resultSetMetaData_.sqlxSchema_[0].equals("")) {

-            tableName += resultSetMetaData_.sqlxSchema_[0].trim() + ".";

+        //dervied column like select 2 from t1, has null schema and table name

+        if (resultSetMetaData_.sqlxSchema_[0] != null && !resultSetMetaData_.sqlxSchema_[0].equals(""))
{

+            tableName += "\"" + resultSetMetaData_.sqlxSchema_[0] + "\".";

         }

-        tableName += resultSetMetaData_.sqlxBasename_[0].trim();

+        if (resultSetMetaData_.sqlxBasename_[0] != null) {

+            tableName += "\"" + resultSetMetaData_.sqlxBasename_[0] + "\"";

+        }

         return tableName;

     }

 

@@ -3157,6 +3194,11 @@

         if (resultSetMetaData_.sqlxUpdatable_ == null || resultSetMetaData_.sqlxUpdatable_[column
- 1] != 1) {

             throw new SqlException(agent_.logWriter_, "Column not updatable");

         }

+

+        //if not on a valid row, then do not accept updateXXX calls

+        if (!isValidCursorPosition_)

+            throw new SqlException(agent_.logWriter_, "Invalid operation to " +

+                    "update at current cursor position");

     }

 

     final void checkForValidColumnIndex(int column) throws SqlException {

  



--------------------------------------------------------------------------------------------------------------------------------------------------------------

This email and any files transmitted with it are confidential and intended solely for the
use of the individual or entity to whom
they are addressed. If you have received this email in error please notify the originator
of the message. 
Scanning of this message is performed by SurfControl E-mail Filter software in conjunction
with virus detection software.




Mime
View raw message