Updatable Cursors

Cursors are read-only by default. For a cursor to be updatable, you must specify SELECT . . . FOR UPDATE. Use FOR UPDATE only when you will be modifying rows to avoid excessive locking of tables.

• Requirements for Updatable Cursors
• Naming or Accessing the Name of a Cursor
• Extended Updatable Cursor Example

Only specific SELECT statements--simple accesses of a single table--allow you to update or delete rows as you step through them.

For more information, see "SELECT statement" and "FOR UPDATE clause" in the Derby Reference Manual.

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
//to access the current row of the cursor
PreparedStatement ps2 = conn.prepareStatement(
     "UPDATE FlightBookings SET number_seats = ? " +
        "WHERE CURRENT OF UPDATABLESTATEMENT");

More typically, you do not assign a name to the cursor, but let the system generate one for you automatically. You can determine the system-generated cursor name of a ResultSet generated by a SELECT statement using the ResultSet class's getCursorName method.

PreparedStatement ps2 = conn.prepareStatement(
    "UPDATE employee SET bonus = ? WHERE CURRENT OF "+
        Updatable.getCursorName());

String URL = "jdbc:derby:sample";
// autocommit must be turned off for updatable cursors
conn.setAutoCommit(false);
Statement s3 = conn.createStatement();
// name the statement so we can reference the result set
// it generates
s3.setCursorName("UPDATABLESTATEMENT");
// Updatable statements have some requirements
// for example, select must be on a single table
ResultSet Updatable = s3.executeQuery(
    "SELECT firstnme, lastname, workdept, bonus" +
    "FROM employee FOR UPDATE of bonus");
// we need a separate statement to do the
// update PreparedStatement
PreparedStatement ps2 = conn.prepareStatement("UPDATE employee " +
// we could use the cursor name known by the system,
// as the following line shows
//"SET bonus = ? WHERE CURRENT OF " + Updatable.getCursorName());
// but we already know the cursor name
"SET bonus = ? WHERE CURRENT OF UPDATABLESTATEMENT"); 
String theDept="E21";
while (Updatable.next()) {
    String firstnme = Updatable.getString("FIRSTNME");
    String lastName = Updatable.getString("LASTNAME");
    String workDept = Updatable.getString("WORKDEPT");
    BigDecimal bonus = Updatable.getBigDecimal("BONUS");
    if (workDept.equals(theDept)) {
    // if the current row meets our criteria,
    // update the updatable column in the row
        ps2.setBigDecimal(1, bonus.add(new BigDecimal(250)));
        ps2.executeUpdate();
        System.out.println("Updating bonus in employee" +
        " table for employee " + firstnme +
        ", department " + theDept );
    } 
    }
Updatable.close();
s3.close();
ps2.close();
conn.commit();