Checking 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 19-1 lists @@sqlstatus values and their meanings:

Table 19-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 19-2 lists @@fetch_status values and meanings:

Table 19-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. “@@cursor_rows values” lists @@cursor_rows values and their meanings.

Table 19-3: @@cursor_rows values

Value

Meaning

-1

Indicates 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 for the result set is unknown.

0

No cursors have been opened, no rows are qualified from 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.