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 {