User's Guide
   PART 1. Working with Databases
     CHAPTER 9. Using SQL in Applications       

Types of cursor

You can use one of several kinds of cursor in Adaptive Server Anywhere. You must choose from among these cursor types when you declare the cursor.

• Unique cursors     When a cursor is declared unique, the query is forced to return all the columns required to uniquely identify each row. Often this means ensuring that all the columns in the primary key are returned. Any columns required but not specified are added to the result set.

• Read only cursors     A cursor declared as read only may not be used in an UPDATE (positioned) or a DELETE (positioned) operation.

• No scroll cursors     When a cursor is declared NO SCROLL, fetching operations are restricted to fetching the next row or the same row again.

• Dynamic scroll cursors     With dynamic scroll cursors you can carry out more flexible fetching operations. You can move backwards and forwards in the result set, or move to an absolute position.

• Scroll cursors     These are similar to dynamic scroll cursors, but behave differently when the rows in the cursor are modified or deleted after the first time the row is read. Scroll cursors have more predictable behavior when changes happen.

• Insensitive cursors     Also called static cursors in ODBC.

  A cursor declared insensitive has its membership fixed when it is opened; a temporary table is created with a copy of all the original rows. Fetching from an insensitive cursor does not see the effect of any other operation from a different cursor. It does see the effect of operations on the same cursor. Also, insensitive cursors are not affected by ROLLBACK or ROLLBACK TO SAVEPOINT; these are not operations on the cursor that change the cursor contents.

  It is easier to write an application using insensitive cursors, since you only have to worry about changes you make explicitly to the cursor. You do not have to worry about actions taken by other users or by other parts of your application.

  Insensitive cursors can be expensive if the cursor is on many rows.

  Availability of cursors

Not all interfaces provide support for all kinds of cursors.

• JDBC does not use cursors, although the ResultSet object does have a next method that allows you to scroll through the results of a query in the client application.

• ODBC supports all the kinds of cursors.

  ODBC provides a cursor type called a block cursor. When you use a block cursor, you can use SQLFetch or SQLExtendedFetch to fetch a block of rows, rather than a single row.

• Embedded SQL supports all available cursors.

• Sybase Open Client does not support scrollable (Scroll or Dynamic Scroll) cursors. Also, using updateable cursors that are not unique has a severe performance penalty.

  Choosing a cursor type

Each row fetched in a scroll cursor is remembered. If one of these rows is deleted, either by your program or by another connection, it creates a "hole" in the cursor. If you fetch the row at this "hole" with a SCROLL cursor, an error is returned indicating that there is no current row, and the cursor is left positioned on the "hole". In contrast, a dynamic scroll cursor just skips the "hole" and retrieves the next row. Scroll cursors remember row positions within a cursor, so that your application can be assured that these positions will not change.

Example 

For example, an application could remember that Cobb is the second row in the cursor for the following query:

SELECT emp_lname 
FROM employee

If the first employee (Whitney) is deleted while the scroll cursor is still open, a FETCH ABSOLUTE 2 will still position on Cobb while FETCH ABSOLUTE 1 will return an error. Similarly, if the cursor is on Cobb, FETCH PREVIOUS will return SQLE_NO_CURRENT_ROW.

In addition, a fetch on a SCROLL cursor returns a warning if the row has changed since it was last read. The warning only happens once; fetching the same row a third time will not produce the warning.

Similarly, an UPDATE (positioned) or DELETE (positioned) statement on a row that has been modified since it was last fetched returns an error. An application must fetch the row again before the UPDATE or DELETE is permitted.

An update to any column will cause the warning/error, even if the column is not referenced by the cursor. For example, a cursor on a query returning emp_lname would report the update even if only the salary column were modified.

More information is maintained about scroll cursors than dynamic scroll cursors. Dynamic scroll cursors are therefore more efficient and should be used unless the consistent behavior of scroll cursors is required.

There is no extra overhead for dynamic scroll cursors versus no scroll cursors.