You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2005/05/18 15:38:59 UTC
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 <sa...@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 <ms...@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 {
>
>
>