Updatable Cursors

After defining a cursor using declare cursor, the SAP ASE server determines whether the cursor is updatable or read-only.

If:

  • A cursor is updatable – you can update or delete rows through the cursor; that is, use cursor_name to do a position update or delete.

  • A cursor is read-only – you cannot use cursor_name to perform a position update or delete.

Use the for update or for read only clause to explicitly define a cursor as updatable or read-only. You cannot define an updatable cursor if its select_statement contains one of the following constructs:

  • distinct option

  • group by clause

  • Aggregate function

  • Subquery

  • union operator

  • at isolation read uncommitted clause

If you do not specify either the for update or the read only clause, the SAP ASE server checks to see whether the cursor is updatable.

The SAP ASE server also defines a cursor as read-only if you declare a language- or server-type cursor that includes an order by clause as part of its select_statement. The SAP ASE server handles updates differently for client- and execute-type cursors, thereby eliminating this restriction.

When using updatable cursors and allpages locking:

  • If you do not specify a column_name_list with the for update clause, all the specified columns in the query are updatable. The SAP ASE server attempts to use unique indexes for updatable cursors when scanning the base table. For cursors, the SAP ASE server considers an index containing an IDENTITY column to be unique, even if it is not so declared.

  • If you do not specify the for update clause, the SAP ASE server chooses any unique index, although it can also use other indexes or table scans if no unique index exists for the specified table columns. However, when you specify the for update clause, the SAP ASE server must use a unique index defined for one or more of the columns to scan the base table. If none exists, it returns an error.

  • In most cases, include only columns to be updated in the column_name_list of the for update clause. If the table has only one unique index, you do not need to include its column in the for update column_name_list; the SAP ASE server finds it when it performs the cursor scan. If the table has more than one unique index, do not include any of them in the for update column_name_list.

    This allows the SAP ASE server to use that unique index for its cursor scan, which helps prevent an update anomaly called the Halloween problem. Another way to prevent the Halloween problem is to create tables with the unique auto_identity index database option. See the System Administration Guide.

    The Halloween problem occurs when a client updates a column of a cursor result set row that defines the order in which the rows are returned from the base tables. For example, if the SAP ASE server accesses a base table using an index, and the index key is updated by the client, the updated index row can move within the index and be read again by the cursor. This is a result of an updatable cursor only logically creating a cursor result set. The cursor result set is actually the base tables that derive the cursor.

If you specify the read only option, you cannot update the cursor result set using the cursor name to perform update or delete.

Parent topic: declare cursor