Check Cursor Status

SAP ASE 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 SAP ASE version 15.0 and later.

This table lists @@sqlstatus values and their meanings:

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.

This table lists @@fetch_status values and meanings:

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.

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 definitively state that all qualified rows are retrieved.

  • The cursor is semisensitive 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 has been fully populated; the value returned (n) is the total number of rows in the cursor result set.