Creating Scrollable or Updatable Result Sets

Under JDBC 1.0, no special attention is required in creating and using a result set. A result set is produced automatically to store the results of a query, and no result set types or categories must be specified, because there is only one kind of result set available--forward-only/read-only. For example (given a connection object conn):

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

In using JDBC 2.0 result set enhancements, however, you may specify the result set type (for scrollability and sensitivity) and the concurrency type (for updatability) when you create a generic statement or prepare a prepared statement or callable statement that will execute a query.

(Note, however, that callable statements are intended to execute stored procedures and functions and rarely return a result set. Still, the callable statement class is a subclass of the prepared statement class and so inherits this functionality.)

This section discusses the creation of result sets to use JDBC 2.0 enhancements.

Specifying Result Set Scrollability and Updatability

Under JDBC 2.0, Connection classes have new createStatement(), prepareStatement(), and prepareCall() method signatures that take a result set type and a concurrency type as input:

• Statement createStatement
(int resultSetType, int resultSetConcurrency)

• PreparedStatement prepareStatement
(String sql, int resultSetType, int resultSetConcurrency)

• CallableStatement prepareCall
(String sql, int resultSetType, int resultSetConcurrency)

The statement objects created will have the intelligence to produce the appropriate kind of result sets.

You can specify one of the following static constant values for result set type:

• ResultSet.TYPE_FORWARD_ONLY

• ResultSet.TYPE_SCROLL_INSENSITIVE

• ResultSet.TYPE_SCROLL_SENSITIVE

  Note: See "Oracle Implementation of Scroll-Sensitive Result Sets" for information about possible performance impact.

And you can specify one of the following static constant values for concurrency type:

• ResultSet.CONCUR_READ_ONLY

• ResultSet.CONCUR_UPDATABLE

  Note: If you are using the Oracle JDBC drivers in a JDK 1.1.x environment , the static constants discussed here are part of the Oracle extensions, belonging only to the OracleResultSet class, which you must specify. For example: OracleResultSet.TYPE_SCROLL_SENSITIVE instead of: ResultSet.TYPE_SCROLL_SENSITIVE

After creating a Statement, PreparedStatement, or CallableStatement object, you can verify its result set type and concurrency type by calling the following methods on the statement object:

• int getResultSetType() throws SQLException

• int getResultSetConcurrency() throws SQLException

Example

Following is an example of a prepared statement object that specifies a scroll-sensitive and updatable result set for queries executed through that statement (where conn is a connection object):

...
PreparedStatement pstmt = conn.prepareStatement
         ("SELECT empno, sal FROM emp WHERE empno = ?", 
         ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

pstmt.setString(1, "28959");
ResultSet rs = pstmt.executeQuery();
...

Result Set Limitations and Downgrade Rules

Some types of result sets are not feasible for certain kinds of queries. If you specify an unfeasible result set type or concurrency type for the query you execute, the JDBC driver follows a set of rules to determine the best feasible types to use instead.

The actual result set type and concurrency type are determined when the statement is executed, with the driver issuing a SQLWarning on the statement object if the desired result set type or concurrency type is not feasible. The SQLWarning object will contain the reason why the requested type was not feasible. Check for warnings to verify whether you received the type of result set that you requested, or call the methods described in "Verifying Result Set Type and Concurrency Type".

Result Set Limitations

The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines will result in the JDBC driver choosing an alternative result set type or concurrency type.

To produce an updatable result set:

• A query can select from only a single table and cannot contain any join operations.

  In addition, for inserts to be feasible, the query must select all non-nullable columns and all columns that do not have a default value.

• A query cannot use "SELECT * ". (But see the workaround below.)

• A query must select table columns only. It cannot select derived columns or aggregates such as the SUM or MAX of a set of columns.

• A query cannot use ORDER BY.

To produce a scroll-sensitive result set:

• A query cannot use "SELECT * ". (But see the workaround below.)

• A query can select from only a single table.

• A query cannot use ORDER BY.

In fact, you cannot use ORDER BY for any result set where you will want to refetch rows. This applies to scroll-insensitive/updatable result sets as well as scroll-sensitive result sets. (See "Summary of New Methods for Result Set Enhancements" for general information about refetching.)

Workaround

As a workaround for the "SELECT *" limitation, you can use table aliases as in the following example:

SELECT t.* FROM TABLE t ...

Hint: There is a simple way to determine if your query will probably produce a scroll-sensitive or updatable result set: If you can legally add a ROWID column to the query list, then the query is probably suitable for either a scroll-sensitive or an updatable result set. (You can try this out using SQL*Plus, for example.)

Result Set Downgrade Rules

If the specified result set type or concurrency type is not feasible, the Oracle JDBC driver uses the following rules in choosing alternate types:

• If the specified result set type is TYPE_SCROLL_SENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_SCROLL_INSENSITIVE.

• If the specified (or downgraded) result set type is TYPE_SCROLL_INSENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_FORWARD_ONLY.

Furthermore:

• If the specified concurrency type is CONCUR_UPDATABLE, but the JDBC driver cannot fulfill that request, then the JDBC driver attempts a downgrade to CONCUR_READ_ONLY.

  Notes: Criteria that would prevent the JDBC driver from fulfilling the result set type specifications are listed in "Result Set Limitations". Any manipulations of the result set type and concurrency type by the JDBC driver are independent of each other.

Verifying Result Set Type and Concurrency Type

After a query has been executed, you can verify the result set type and concurrency type that the JDBC driver actually used, by calling methods on the result set object.

• int getType() throws SQLException

  This method returns an int value for the result set type used for the query. ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE are the possible values.

• int getConcurrency() throws SQLException

  This method returns an int value for the concurrency type used for the query. ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE are the possible values.

---