Chapter 18: Cursors: Accessing Data

Checking the cursor status

Adaptive Server returns a status value after each fetch. You can access the value through the global variables @@sqlstatus, @@fetch_status, or @@cursor_rows. @@fetch_status and @@cursor_rows are supported only in Adaptive Server version 15.0 and later.

Table 18-1 lists @@sqlstatus values and their meanings:

**Table 18-1: @@sqlstatus values**
Value	Meaning
0	Successful completion of the fetch statement.
1	The fetch statement resulted in an error.
2	There is no more data in the result set. This warning can occur if the current cursor position is on the last row in the result set and the client submits a fetch statement for that cursor.

Table 18-2 lists @@fetch_status values and meanings:

**Table 18-2: @@fetch_status values**
Value	Meaning
0	fetch operation successful
-1	fetch operation unsuccessful
-2	value reserved for future use

The following example determines the @@sqlstatus for the currently open authors_crsr cursor:

select @@sqlstatus

---------
        0
 
(1 row affected)

The following example determines the @@fetch_status for the currently open authors_crsr cursor:

select @@fetch_status

---------
        0
 
(1 row affected)

Only a fetch statement can set @@sqlstatus and @@fetch_status. Other statements have no effect on @@sqlstatus.

@@cursor_rows indicates the number of rows in the cursor result set that were last opened and fetched.

**Table 18-3: @@cursor_rows values**
Value	Meaning
-1	May indicate one of the following: The cursor is dynamic; since a dynamic cursor reflects all changes, the number of rows that qualify for the cursor is constantly changing. You can never state definitively that all qualified rows are retrieved. The cursor is semi_sensitive and scrollable, but the scrolling worktable is not yet populated. The number of rows that qualify the cursor is thus unknown.
0	No cursors have been opened, no rows are qualified fro the last opened cursor, or the last opened cursor is closed or deallocated.
n	The last opened or fetched cursor result set is fully populated; the value returned (n) is the total number of rows in the cursor result set.

View this book as PDF