Making cursors updatable

You can update or delete a row returned by a cursor if the cursor is updatable. If the cursor is read-only, you cannot update or delete it. By default, Adaptive Server attempts to determine whether a cursor is can be updated before designating it as read-only.

You can explicitly specify whether a cursor is read-only by using the read only or update keywords in the declare statement. Specifying a cursor as read-only ensures that Adaptive Server performs the positioned updates correctly. Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.

All scrollable cursors and all insensitive cursors are read-only.

The following example defines an updatable result set for the pubs_crsr cursor:

declare pubs_crsr cursor
for select pub_name, city, state
from publishers
for update of city, state

The above example includes all the rows from the publishers table, but it explicitly defines only the city and state columns as updatable.

Unless you plan to update or delete rows through a cursor, declare a cursor as read-only. If you do not explicitly specify read only or update, the semi_sensitive nonscrollable cursor is implicitly updatable when the select statement does not contain any of the following constructs:

• distinct option

• group by clause

• Aggregate function

• Subquery

• union operator

• at isolation read uncommitted clause