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.sqlM 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 {
