Specifying the action that the rowset cursor is to take when it reaches the end of the data
Your program must be coded to recognize and handle an end-of-data condition whenever you use a rowset cursor to fetch rows.
About this task
To determine whether the program has retrieved the last row of data in the result table, test the SQLCODE field for a value of +100 or the SQLSTATE field for a value of '02000'. With a rowset cursor, these codes occur when a FETCH statement retrieves the last row in the result table. However, when the last row has been retrieved, the program must still process the rows in the last rowset through that last row. For an example of end-of-data processing for a rowset cursor, see Examples of fetching rows by using cursors.
- The contents of the SQLERRD(3) field in the SQLCA
- The contents of the ROW_COUNT item of GET DIAGNOSTICS
For information about GET DIAGNOSTICS, see Checking the execution of SQL statements by using the GET DIAGNOSTICS statement.
If you declare the cursor as dynamic scrollable, and SQLCODE has the value +100, you can continue with a FETCH statement until no more rows are retrieved. Additional fetches might retrieve more rows because a dynamic scrollable cursor is sensitive to updates by other application processes. For information about dynamic cursors, see Types of cursors.