Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Updating Result Sets

A concurrency type of `CONCUR_UPDATABLE` allows you to update rows in the result set, delete rows from the result set, or insert rows into the result set.

After you perform an `UPDATE` or `INSERT` operation in a result set, you propagate the changes to the database in a separate step that you can skip if you want to cancel the changes.

A `DELETE` operation in a result set, however, is immediately executed (but not necessarily committed) in the database as well.

For sample applications demonstrating this functionality, see "Inserting and Deleting Rows in a Result Set--ResultSet3.java" and "Updating Rows in a Result Set--ResultSet4.java".

Note: When using an updatable result set, it is typical to also make it scrollable. This allows you to position to any row that you want to change. With a forward-only updatable result set, you can change rows only as you iterate through them with the `next()` method.

### Performing a DELETE Operation in a Result Set

The result set `deleteRow()` method will delete the current row. Following is the method signature:

- `void deleteRow() throws SQLException`

  Important: Unlike `UPDATE` and `INSERT` operations in a result set, which require a separate step to propagate the changes to the database, a `DELETE` operation in a result set is immediately executed in the corresponding row in the database as well. Once you call `deleteRow()`, the changes will be made permanent with the next transaction `COMMIT` operation. Remember also that by default, the auto-commit flag is set to `true`. Therefore, unless you override this default, any `deleteRow()` operation will be executed and committed immediately.

Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except `beforeFirst()` and `afterLast()`, which do not go to a valid current row), and then delete that row, as in the following example (presuming a result set `rs`):

    ...
    rs.absolute(5);
    rs.deleteRow();
    ...

See "Positioning in a Scrollable Result Set" for information about the positioning methods.

Important: In a forward-only result set in Oracle8i release 8.1.6 and higher, the deleted row remains in the result set object even after it has been deleted from the database. In a scrollable result set, by contrast, a `DELETE` operation is evident in the local result set object--the row would no longer be in the result set after the `DELETE`. The row preceding the deleted row becomes the current row, and row numbers of subsequent rows are changed accordingly. Refer to "Seeing Internal Changes" for more information.

### Performing an UPDATE Operation in a Result Set

Performing a result set `UPDATE` operation requires two separate steps to first update the data in the result set and then copy the changes to the database.

Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except `beforeFirst()` and `afterLast()`, which do not go to a valid current row), and then update that row as desired.

See "Positioning in a Scrollable Result Set" for information about the positioning methods.

Here are the steps for updating a row in the result set and database:

1. Call the appropriate `updateXXX()` methods to update the data in the columns you want to change.

   With JDBC 2.0, a result set object has an `updateXXX()` method for each datatype, as with the `setXXX()` methods previously available for updating the database directly.

   Each of these methods takes an `int` for the column number or a string for the column name and then an item of the appropriate datatype to set the new value. Following are a couple of examples for a result set `rs`:

       rs.updateString(1, "mystring");
       rs.updateFloat(2, 10000.0f);

2. Call the `updateRow()` method to copy the changes to the database (or the `cancelRowUpdates()` method to cancel the changes).

   Once you call `updateRow()`, the changes are executed and will be made permanent with the next transaction `COMMIT` operation. Be aware that by default, the auto-commit flag is set to `true` so that any executed operation is committed immediately.

   If you choose to cancel the changes before copying them to the database, call the `cancelRowUpdates()` method instead. This will also revert to the original values for that row in the local result set object. Note that once you call the `updateRow()` method, the changes are written to the transaction and cannot be canceled unless you roll back the transaction (auto-commit must be disabled to allow a `ROLLBACK` operation).

   Positioning to a different row before calling `updateRow()` also cancels the changes and reverts to the original values in the result set.

   Before calling `updateRow()`, you can call the usual `getXXX()` methods to verify that the values have been updated correctly. These methods take an `int` column index or string column name as input. For example:

       float myfloat = rs.getFloat(2);
       ...process myfloat to see if it's appropriate...

   Note: In Oracle 8i release 8.1.6 and higher, result set `UPDATE` operations are visible in the local result set object for all result set types (forward-only, scroll-sensitive, and scroll-insensitive). Refer to "Seeing Internal Changes" for more information.

##### Example

Following is an example of a result set `UPDATE` operation that is also copied to the database. The tenth row is updated. (The column number is used to specify column 1, and the column name--`sal`-- is used to specify column 2.)

    ...
    Statement stmt = conn.createStatement
             (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

    if (rs.absolute(10))        // (returns false if row does not exist)
    {
       rs.updateString(1, "28959");
       rs.updateFloat("sal", 100000.0f);
       rs.updateRow();
    }
    // Changes will be made permanent with the next COMMIT operation.
    ...

### Performing an INSERT Operation in a Result Set

Result set `INSERT` operations use what is called the result set insert-row, which is a staging area that holds the data for the inserted row until it is copied to the database. You must explicitly move to this row to write the data that will be inserted.

As with `UPDATE` operations, result set `INSERT` operations require separate steps to first write the data to the insert-row and then copy it to the database .

Following are the steps in executing a result set `INSERT` operation.

1. Move to the insert-row by calling the result set `moveToInsertRow()` method.

   Note: The result set will remember the current position prior to the `moveToInsertRow()` call. Afterward, you can go back to it with a `moveToCurrentRow()` call.

2. As with `UPDATE` operations, use the appropriate `updateXXX()` methods to write data to the columns. For example:

       rs.updateString(1, "mystring");
       rs.updateFloat(2, 10000.0f);

   (Note that you can specify a string for column name, instead of an integer for column number.)

   Important: Each column value in the insert-row is undefined until you call the `updateXXX()` method for that column. You must call this method and specify a non-null value for all non-nullable columns, or else attempting to copy the row into the database will result in a SQL exception. It is permissible, however, to not call `updateXXX()` for a nullable column. This will result in a value of `null`.

3. Copy the changes to the database by calling the result set `insertRow()` method.

   Once you call `insertRow()`, the insert is executed and will be made permanent with the next transaction `COMMIT` operation.

   Positioning to a different row before calling `insertRow()` cancels the insert and clears the insert-row.

   Before calling `insertRow()` you can call the usual `getXXX()` methods to verify that the values have been set correctly in the insert-row. These methods take an `int` column index or string column name as input. For example:

       float myfloat = rs.getFloat(2);
       ...process myfloat to see if it's appropriate...

   Note: In Oracle8i release 8.1.6 and higher, no result set type (neither scroll-sensitive, scroll-insensitive, nor forward-only) can see a row inserted by a result set `INSERT` operation. Refer to "Seeing Internal Changes" for more information.

##### Example

The following example performs a result set `INSERT` operation, moving to the insert-row, writing the data, copying the data into the database, and then returning to what was the current row prior to going to the insert-row. (The column number is used to specify column 1, and the column name--`sal`-- is used to specify column 2.)

    ...
    Statement stmt = conn.createStatement
             (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

    rs.moveToInsertRow();
    rs.updateString(1, "28959");
    rs.updateFloat("sal", 100000.0f);
    rs.insertRow();
    // Changes will be made permanent with the next COMMIT operation.
    rs.moveToCurrentRow();  // Go back to where we came from...
    ...

### Update Conflicts

It is important to be aware of the following facts regarding updatable result sets with the Oracle8i release 8.1.6 and higher JDBC drivers:

- The drivers do not enforce write locks for an updatable result set.

- The drivers do not check for conflicts with a result set `DELETE` or `UPDATE` operation.

A conflict will occur if you try to perform a `DELETE` or `UPDATE` operation on a row updated by another committed transaction.

The Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table. As long as the ROWID is still valid when a driver tries to send an `UPDATE` or `DELETE` operation to the database, the operation will be executed.

The driver will not report any changes made by another committed transaction. Any conflicts are silently ignored and your changes will overwrite the previous changes.

To avoid such conflicts, use the Oracle `FOR UPDATE` feature when executing the query that produces the result set. This will avoid conflicts, but will also prevent simultaneous access to the data. Only a single write lock can be held concurrently on a data item.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index