Result set updatability

The JDBC 3.0 API does not provide exact specifications for SQL queries that yield result sets that can be updated. Generally, queries that meet the following criteria can produce result sets that can be updated:
  • 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.
HCL OneDB™ 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, SERIAL8, or BIGSERIAL column in the table.
  3. The driver looks for the tables 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 a scrollable cursor that can be updated, see the UpdateCursor1.java, UpdateCursor2.java, and UpdateCursor3.java files.