Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Updatable result sets and Mimer JDBC
Categories: JDBC, Programming Examples, SQL
Introduction

The JDBC API specifies a number of methods to update result sets. That is, an application may query the database server for a result set, and while reading the result, individual rows may be updated. An example of such a method is ResultSet.updateString.

As of version 9.2, Mimer SQL does not support ResultSet.updateString and similar methods. This document briefly describes how to accomplish the same thing using SQL-99 standard features.

Description

The alternative to using the updatable result set API methods is to use positioned updates. This is accomplished using SELECT FOR UPDATE and UPDATE WHERE CURRENT.

The idea is that one lets an additional Statement be connected to a resultset, and executing UPDATE WHERE CURRENT statement on that Statement. The ResultSet and the Statement object are connected by setting the cursor name of the result set to something known, and use this same cursor name in the UPDATE WHERE CURRENT statement.

For example, the below code example updates a row in an imaginary table.
String cursorName = "OUR_CURSOR";
Statement stmt = con.createStatement();
stmt.setCursorName(cursorName);
ResultSet rs = stmt.executeQuery("select NAME,PHONE_NUMBER from "+
"PHONE_NUMBERS for update of PHONE_NUMBER");
PreparedStatement pstmt = con.prepareStatement("update PHONE_NUMBERS "+
"set PHONE_NUMBER=? where current of "+cursorName");
while (...) {
rs.next();

if (weWantToUpdate) {
String newPhoneNumber = ...;
pstmt.setString(1,newPhoneNumber);
pstmt.execute();
}
}

Performance considerations

Using positioned update and deletes is an expensive way of updating a database. It should only be used in situations where a human user browse through a result set and in the process decides to make changes or delete single rows of data. In a batch scenario, or when an interactive application must update many rows, it is nearly always better to use regular UPDATE/DELETE:s.

Some if the characteristics leading to potential performance problems are:

  • A result set has to browsed sequentially in advance. If the application knows in advance which row to update or delete, creating the result set will in essence bypass many possible optimisation steps.

  • Each updated or deleted row requires one round-trip to the server.

  • Positioned update/deletes in manual commit mode may lead to performance problems if there are many updates. A lot of updates within the same transaction increases the risks of transaction conflicts as well as transaction write-sets may become large. The transactional aspects are further discussed below.

The advice is to use positioned update and deletes only when a human user is interactively making updates to a database.

Transaction considerations

When using a positioned update/delete the database programmer may choose between running in auto-commit transaction mode or manual commit mode. In many cases, manual commit mode is preferred since it gives the database programmer the control to decide for himself when data is secured in the database.

In this case, it is important to know that cursors are automatically closed at the end of a manual transaction. This includes the cursor we are updating (rs in the program example above).

A database programmer using positioned update or deletes therefore has to decide either to use auto-commit mode, or manual commit mode and let all updates belong to the same transaction. That is, we don’t commit until we are done with the entire result set.

If there are many updates and/or deletes this will make the transaction harder to complete, because of an increased risk of conflicts. The transaction write-set may also become very large which in turn leads to slightly more database overhead.

The advice is to prefer auto-commit mode when there will be many updates to the result set.

Benefits

Using positioned UPDATE/DELETE:s is the SQL-99 way of updating result sets. Many database interfaces, such as JDBC, define API equivalents to provide the same functionality.

Links

Some links to relevant pages in the Mimer 9.2 SQL Reference Manual:

The SELECT FOR UPDATE OF statement.

The DELETE WHERE CURRENT statement.

The UPDATE WHERE CURRENT statement.

Last updated: 2004-01-20

 

Powered by Mimer SQL

Powered by Mimer SQL