DB2 Version 9.7 for Linux, UNIX, and Windows

Specifying updatability, scrollability, and holdability for ResultSets in JDBC applications

You use special parameters in the Connection.prepareStatement or Connection.createStatement methods to specify the updatability, scrollability, and holdability of a ResultSet.

By default, ResultSet objects are not scrollable and not updatable. The default holdability depends on the data source, and can be determined from the DatabaseMetaData.getResultSetHoldability method. These steps change the scrollability, updatability, and holdability attributes for a ResultSet.
  1. If the SELECT statement that defines the ResultSet has no input parameters, invoke the createStatement method to create a Statement object. Otherwise, invoke the prepareStatement method to create a PreparedStatement object. You need to specify forms of the createStatement or prepareStatement methods that include the resultSetType, resultSetConcurrency, or resultSetHoldability parameters.
    The form of the createStatement method that supports scrollability and updatability is: 
    createStatement(int resultSetType, int resultSetConcurrency);
    The form of the createStatement method that supports scrollability, updatability, and holdability is: 
    createStatement(int resultSetType, int resultSetConcurrency,
 int resultSetHoldability);
    The form of the prepareStatement method that supports scrollability and updatability is: 
    prepareStatement(String sql, int resultSetType, 
 int resultSetConcurrency);
    The form of the prepareStatement method that supports scrollability, updatability, and holdability is: 
    prepareStatement(String sql, int resultSetType, 
 int resultSetConcurrency, int resultSetHoldability);
    Important: In a prepareStatement method invocation in which the first parameter is a CALL statement, you cannot specify the scrollability, updatability, or holdability of result sets that are returned from a stored procedure. Those characteristics are determined by the stored procedure code, when it declares the cursors for the result sets that are returned. If you use the prepareStatement method to prepare a CALL statement, and the prepareStatement call includes the scrollability, updatability, or holdability parameters, the IBM® Data Server Driver for JDBC and SQLJ does not use those parameter values. A prepareStatement method with scrollability, updatability, or holdability parameters applies only to preparation of SQL statements other than the CALL statement.

    The following table contains a list of valid values for resultSetType and resultSetConcurrency.

    Table 1. Valid combinations of resultSetType and resultSetConcurrency for ResultSets
    resultSetType value resultSetConcurrency value
    TYPE_FORWARD_ONLY CONCUR_READ_ONLY
    TYPE_FORWARD_ONLY CONCUR_UPDATABLE
    TYPE_SCROLL_INSENSITIVE CONCUR_READ_ONLY
    TYPE_SCROLL_SENSITIVE1.a CONCUR_READ_ONLY
    TYPE_SCROLL_SENSITIVE1.a CONCUR_UPDATABLE
    Note:
    1. This value does not apply to connections to IBM Informix®.

    resultSetHoldability has two possible values: HOLD_CURSORS_OVER_COMMIT and CLOSE_CURSORS_AT_COMMIT. Either of these values can be specified with any valid combination of resultSetConcurrency and resultSetHoldability. The value that you set overrides the default holdability for the connection.

    Restriction: If the ResultSet is scrollable, and the ResultSet is used to select columns from a table on a DB2® for Linux, UNIX, and Windows server, the SELECT list of the SELECT statement that defines the ResultSet cannot include columns with the following data types:
    • LONG VARCHAR
    • LONG VARGRAPHIC
    • BLOB
    • CLOB
    • XML
    • A distinct type that is based on any of the previous data types in this list
    • A structured type
  2. If the SELECT statement has input parameters, invoke setXXX methods to pass values to the input parameters.
  3. Invoke the executeQuery method to obtain the result table from the SELECT statement in a ResultSet object.
  4. For each row that you want to access:
    1. Position the cursor using one of the methods that are listed in the following table.
      Restriction: If resultSetType is TYPE_FORWARD_ONLY, only ResultSet.next is valid.
      Table 2. ResultSet methods for positioning a scrollable cursor
      Method Positions the cursor
      first4.a.i On the first row of the ResultSet
      last4.a.i On the last row of the ResultSet
      next4.a.ii On the next row of the ResultSet
      previous4.a.i,4.a.iii On the previous row of the ResultSet
      absolute(int n)4.a.i,4.a.iv If n>0, on row n of the ResultSet. If n<0, and m is the number of rows in the ResultSet, on row m+n+1 of the ResultSet.
      relative(int n)4.a.i,4.a.v,4.a.vi, If n>0, on the row that is n rows after the current row. If n<0, on the row that is n rows before the current row. If n=0, on the current row.
      afterLast4.a.i After the last row in the ResultSet
      beforeFirst4.a.i Before the first row in the ResultSet
      Notes:
      1. This method does not apply to connections to IBM Informix.
      2. If the cursor is before the first row of the ResultSet, this method positions the cursor on the first row.
      3. If the cursor is after the last row of the ResultSet, this method positions the cursor on the last row.
      4. If the absolute value of n is greater than the number of rows in the result set, this method positions the cursor after the last row if n is positive, or before the first row if n is negative.
      5. The cursor must be on a valid row of the ResultSet before you can use this method. If the cursor is before the first row or after the last row, the method throws an SQLException.
      6. Suppose that m is the number of rows in the ResultSet and x is the current row number in the ResultSet. If n>0 and x+n>m, the driver positions the cursor after the last row. If n<0 and x+n<1, the driver positions the cursor before the first row.
    2. If you need to know the current cursor position, use the getRow, isFirst, isLast, isBeforeFirst, or isAfterLast method to obtain this information.
    3. If you specified a resultSetType value of TYPE_SCROLL_SENSITIVE in step 1, and you need to see the latest values of the current row, invoke the refreshRow method.

      Recommendation: Because refreshing the rows of a ResultSet can have a detrimental effect on the performance of your applications, you should invoke refreshRow only when you need to see the latest data.

    4. Perform one or more of the following operations:
      • To retrieve data from each column of the current row of the ResultSet object, use getXXX methods.
      • To update the current row from the underlying table, use updateXXX methods to assign column values to the current row of the ResultSet. Then use updateRow to update the corresponding row of the underlying table. If you decide that you do not want to update the underlying table, invoke the cancelRowUpdates method instead of the updateRow method.

        The resultSetConcurrency value for the ResultSet must be CONCUR_UPDATABLE for you to use these methods.

      • To delete the current row from the underlying table, use the deleteRow method. Invoking deleteRow causes the driver to replace the current row of the ResultSet with a hole.

        The resultSetConcurrency value for the ResultSet must be CONCUR_UPDATABLE for you to use this method.

  5. Invoke the close method to close the ResultSet object.
  6. Invoke the close method to close the Statement or PreparedStatement object.
The following code demonstrates how to retrieve all rows from the employee table in reverse order, and update the phone number for employee number "000010". The numbers to the right of selected statements correspond to the previously-described steps.
Figure 1. Using a scrollable cursor
String s;
String stmtsrc;
Connection con;
Statement stmt;
ResultSet rs;
…
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                           ResultSet.CONCUR_UPDATABLE);                  1 
                                        // Create a Statement object
                                        // for a scrollable, updatable
                                        // ResultSet
stmtsrc = "SELECT EMPNO, PHONENO FROM EMPLOYEE " +
          "FOR UPDATE OF PHONENO"; 
rs = stmt.executeQuery(stmtsrc);        // Create the ResultSet          3 
rs.afterLast();                         // Position the cursor at the end of
                                        // the ResultSet                 4a 
while (rs.previous()) {                 // Position the cursor backward
 s = rs.getString("EMPNO");             // Retrieve the employee number  4d 
                                        // (column 1 in the result
                                        // table)
 System.out.println("Employee number = " + s);
                                        // Print the column value
 if (s.compareTo("000010") == 0) {      // Look for employee 000010
  rs.updateString("PHONENO","4657");    // Update their phone number
  rs.updateRow();                       // Update the row
 }                                      
}
rs.close();                             // Close the ResultSet           5 
stmt.close();                           // Close the Statement           6 
Parent topic: Characteristics of a JDBC ResultSet under the IBM Data Server Driver for JDBC and SQLJ
Related tasks:
Retrieving data from tables using the Statement.executeQuery method