db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Wed, 18 May 2005 14:25:20 GMT
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: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> 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 > 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