db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Wed, 18 May 2005 01:31:20 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Mamta, looks like this patch was sent to wrong alias... Should it be
sent to DerbyDev?<span class="moz-smiley-s1"><span> :-) </span></span><br>
<br>
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? <br>
<br>
Satheesh<br>
<br>
Mamta Satoor wrote:<br>
<blockquote cite="midd9619e4a05051419377d1c390f@mail.gmail.com"
 type="cite">
  <div>Hi,</div>
  <div>&nbsp;</div>
  <div>I have another small patch for trigger test for
IDENTITY_VAL_LOCAL. Can a committer please commit it for me?</div>
  <div>&nbsp;</div>
  <div>
  <div>********svn stat************</div>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql<br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out</div>
  <div>
  <div>*****************************</div>
  <div>&nbsp;</div>
  </div>
  <div>thanks,</div>
  <div>Mamta<br>
  <br>
&nbsp;</div>
  <div><span class="gmail_quote">On 5/13/05, <b
 class="gmail_sendername">Mamta Satoor</b> &lt;<a
 href="mailto:msatoor@gmail.com">msatoor@gmail.com</a>&gt; wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
1ex;">
    <div>Hi,</div>
    <div>&nbsp;</div>
    <div>I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL()
function, so there is the crucial *connection* dependency identified.</div>
    <div>&nbsp;</div>
    <div>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?</div>
    <div>&nbsp;</div>
    <div>********svn stat************</div>
    <div>M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql<br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out</div>
    <div>*****************************</div>
    <div>&nbsp;</div>
    <div>thanks,</div>
    <span class="sg">
    <div>Mamta<br>
    <br>
&nbsp;</div>
    </span>
    <div><span class="e" id="q_103d7cf9587cd741_2">
    <div><span class="gmail_quote">On 5/13/05, <b
 class="gmail_sendername">Daniel John Debrunner</b> &lt;<a
 onclick="return top.js.OpenExtLink(window,event,this)"
 href="mailto:djd@debrunners.com" target="_blank">djd@debrunners.com
    </a>&gt; wrote:</span>
    <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
1ex;">Mamta
Satoor wrote:<br>
      <br>
&gt; The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the
value that<br>
&gt; got into generated for _any_ table with identity column using
single row<br>
&gt; insert with values clause in the current transaction.<br>
      <br>
Except it doesn't behave like that, with respect to the *current<br>
transaction*. Derby's implementation returns the last identity value
for <br>
a single row INSERT statement within the same connection.<br>
See the example below, and note auto commit is true.<br>
      <br>
And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,<br>
that will just return the same value multiple times (once per row in
the <br>
table) and the value will be the last identity value for a single row<br>
INSERT statement within the same connection.<br>
      <br>
Dan.<br>
      <br>
ij&gt; connect 'jdbc:derby:foo;create=true';<br>
ij&gt; create table t (id int generated always as identity, d int); <br>
0 rows inserted/updated/deleted<br>
ij&gt; insert into t(d) values(88);<br>
1 row inserted/updated/deleted<br>
ij&gt; values IDENTITY_VAL_LOCAL();<br>
1<br>
-------------------------------<br>
1<br>
      <br>
1 row selected<br>
ij&gt; select * from t; <br>
ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |D<br>
-----------------------<br>
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|88<br>
      <br>
1 row selected<br>
ij&gt; values IDENTITY_VAL_LOCAL();<br>
1<br>
-------------------------------<br>
1<br>
      <br>
1 row selected<br>
      <br>
    </blockquote>
    </div>
    <br>
    </span></div>
    <br clear="all">
  </blockquote>
  </div>
  <br>
  <pre wrap="">
<hr size="4" width="90%">
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)&gt; -- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()
+values IDENTITY_VAL_LOCAL();
+1                              
+-------------------------------
+201                            
 ij(CONN2)&gt; drop table t1;
 0 rows inserted/updated/deleted
 ij(CONN2)&gt; drop table t2;
 0 rows inserted/updated/deleted
+ij(CONN2)&gt; -- 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)&gt; create table t2 (c21 int generated always as identity (start with 201,
increment by 5), c22 int);
+0 rows inserted/updated/deleted
+ij(CONN2)&gt; 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)&gt; values IDENTITY_VAL_LOCAL();
+1                              
+-------------------------------
+201                            
+ij(CONN2)&gt; insert into t1 (c12) values (1);
+1 row inserted/updated/deleted
+ij(CONN2)&gt; -- 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)&gt; select * from t1;
+C11        |C12        
+-----------------------
+101        |1          
+ij(CONN2)&gt; select * from t2;
+C21        |C22        
+-----------------------
+201        |1          
+ij(CONN2)&gt; drop table t1;
+0 rows inserted/updated/deleted
+ij(CONN2)&gt; drop table t2;
+0 rows inserted/updated/deleted
 ij(CONN2)&gt; 
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 &lt; 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 &lt; 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 &amp;&amp; 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 &lt;= 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 &amp;&amp;
                 !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 &amp;&amp; !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 {
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message