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:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[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 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.
