Scrollable updatable result sets

Derby only supports scrollable insensitive result sets. To create a scrollable insensitive result set which is updatable, the statement has to be created with concurrency mode ResultSet.CONCUR_UPDATABLE and type ResultSet.TYPE_SCROLL_INSENSITIVE.

Example of using result set update methods to update a row:

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE");

  uprs.absolute(5); // update the fifth row
  int newBonus = uprs.getInt("BONUS") + 100;
  uprs.updateInt("BONUS", newBonus);
  uprs.updateRow();

Example of using ResultSet.deleteRow() to delete a row:

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE");
  
  uprs.last();
  uprs.relative(-5); // moves to the 5th from the last row
  uprs.deleteRow(); 

Visibility of changes

• Changes caused by other statements, triggers and other transactions (others) are considered as other changes, and are not visible in scrollable insensitive result sets.
• Own updates and deletes are visible in Derby's scrollable insensitive result sets.
  Note: Derby handles changes made using positioned updates and deletes as own changes, so when made via a result set's cursor such changes are also visible in that result set.
• Rows inserted to the table may become visible in the result set.
• ResultSet.rowDeleted() returns true if the row has been deleted using the cursor or result set. It does not detect deletes made by other statements or transactions. Note that the method will also work for result sets with concurrency CONCUR_READ_ONLY if the underlying result set is FOR UPDATE and a cursor was used to delete the row.
• ResultSet.rowUpdated() returns true if the row has been updated using the cursor or result set. It does not detect updates made by other statements or transactions. Note that the method will also work for result sets with concurrency CONCUR_READ_ONLY if the underlying result set is FOR UPDATE and a cursor was used to update the row.
• Note: Both ResultSet.rowUpdated() and ResultSet.rowDeleted() return true if the row first is updated and later deleted.

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.

Since 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 will make the transaction hold locks on the rows which have been read until it commits.