Collection Contents Index Introduction to cursors Working with cursors pdf/chap9.pdf

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.

Top of page  Availability of cursors

Not all interfaces provide support for all kinds of cursors.

Top of page  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.


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.

No warnings or errors in bulk operations mode    
These update warning and error conditions do not occur in bulk operations mode (-b database server command-line switch).

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.

Top of page  

Collection Contents Index Introduction to cursors Working with cursors pdf/chap9.pdf