1.1

Please be aware that even if changes caused by others are not visible in the result set, SQL operations, including
positioned updates, which access the current row will read and use the row data as it is in the database, not as it
is reflected in the result set.
Conflicting Operations
A conflict may occur in scrollable insensitive result sets if a row is updated/deleted by another committed
transaction, or if a row is updated by another statement in the same transaction. The row which the cursor is
positioned on is locked, however once it moves to another row, the lock may be released depending on transaction
isolation level. This means that rows in the scrollable insensitive result set may have been updated/deleted by
other transactions after they were fetched.
Because the result set is insensitive, it will not detect the changes made by others. When doing updates using
the result set, conflicting changes on the columns being changed will be overwritten.
Some conflicts may prevent the result set from doing updates/deletes:
• The row has been deleted after it was read into the result set: Scrollable insensitive result sets will give a warning
with SQLState 01001 .
•
The table has been compressed: Scrollable insensitive result sets will give a warning with SQLState 01001.
A compress conflict may happen if the cursor is held over a commit. This is because the table intent lock is
released on commit, and not reclaimed until the cursor moves to another row.
To avoid conflicts with other transactions, you may increase the transaction isolation level to repeatable read or
serializable. This makes the transaction hold locks on the rows that have been read until the transaction commits.
Inserting Rows with Updatable Result Sets
Updatable result sets can be used to insert rows to the table, by using ResultSet.insertRow().
When you insert a row, you must give a value to each column in the new row that does not allow a null value
and does not have a default value. If the inserted row satisfies the query predicate, it becomes visible in the result
set.
Example of using ResultSet.insertRow() to insert a row:
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery(
"SELECT firstname, lastname, workdept, bonus " +
"FROM employee");
uprs.moveToInsertRow();
uprs.updateString("FIRSTNAME", "Andreas");
uprs.updateString("LASTNAME", "Korneliussen");
uprs.updateInt("WORKDEPT", 123);
uprs.insertRow();
uprs.moveToCurrentRow();
Naming or Accessing the Name of a Cursor
There is no SQL language command to assign a name to a cursor. You can use the JDBC setCursorName
method to assign a name to a ResultSet that allows positioned updates and deletes.
You assign a name to a ResultSet with the setCursorName method of the Statement interface.
You assign the name to a cursor before executing the Statement that will generate it.
Statement s3 = conn.createStatement();
// name the statement so we can reference the result set
// it generates
s3.setCursorName("UPDATABLESTATEMENT");
// we will be able to use the following statement later
175
Using Result Sets and Cursors