Using Scroll Cursors

The scroll cursors feature of IBM Informix JDBC Driver follows the Sun Microsystems JDBC 3.0 specification, with these exceptions:

• Scroll sensitivity
• Client-side scrolling
• Result set updatability

Scroll Sensitivity

The Informix database server implementation of scroll cursors places the rows fetched in a temporary table. If another process changes a row in the original table (assuming the row is not locked) and the row is fetched again, the changes are not visible to the client.

This behavior is similar to the SCROLL_INSENSITIVE description in the JDBC 3.0 specification. IBM Informix JDBC Driver does not support SCROLL_SENSITIVE cursors. To see updated rows, your client application must close and reopen the cursor.

Client-Side Scrolling

The JDBC specification implies that the scrolling can happen on the client-side result set. IBM Informix JDBC Driver supports the scrolling of the result set only to the extent that the database server supports scrolling.

Result Set Updatability

The JDBC 3.0 API from Sun Microsystems does not provide exact specifications for SQL queries that yield updatable result sets. Generally, queries that meet the following criteria can produce updatable result sets:

• The query references only a single table in the database.
• The query does not contain any JOIN operations.
• The query selects the primary key of the table it references.
• Every value expression in the select list must consist of a column specification, and no column specification can appear more than once.
• The WHERE clause of the table expression cannot include a subquery.

IBM Informix JDBC Driver relaxes the primary key requirement, because the driver performs the following operations:

1. The driver looks for a column called ROWID.
2. The driver looks for a SERIAL or SERIAL8 column in the table.
3. The driver looks for the table's primary key in the system catalogs.

If none of these is provided, the driver returns an error.

When you delete a row in a result set, the ResultSet.absolute() method is affected, because the positions of the rows change after the delete.

When the query contains a SERIAL column and the data is duplicated in more than one row, execution of updateRow() or deleteRow() affects all the rows containing that data.

The ScrollCursor.java example file shows how to retrieve a result set with a scroll cursor. For examples of how to use an updatable scrollable cursor, see the UpdateCursor1.java, UpdateCursor2.java, and UpdateCursor3.java files.