Step 3: Specifying what to do when the end of data is reached

 

The end-of-data condition occurs when the FETCH statement has retrieved the last row in the result table and your program issues a subsequent FETCH statement.

To find out when the end of the result table is reached, test the SQLCODE field for a value of 100 or test the SQLSTATE field for a value of '02000' (that is, end of data).

For example:

…
IF SQLCODE =100 GO TO DATA-NOT-FOUND.
 
or  
IF SQLSTATE ='02000' GO TO DATA-NOT-FOUND.

An alternative to this technique is to code the WHENEVER statement. Using WHENEVER NOT FOUND can result in a branch to another part of your program, where a CLOSE statement is issued. The WHENEVER statement looks like this:

EXEC SQL  WHENEVER NOT FOUND GO TO symbolic-address END-EXEC.

Your program should anticipate an end-of-data condition whenever a cursor is used to fetch a row, and should be prepared to handle this situation when it occurs.

When you are using a serial cursor and the end of data is reached, every subsequent FETCH statement returns the end-of-data condition. You cannot position the cursor on rows that are already processed. The CLOSE statement is the only operation that can be performed on the cursor.

When you are using a scrollable cursor and the end of data is reached, the result table can still process more data. You can position the cursor anywhere in the result table using a combination of the position options. You do not need to close the cursor when the end of data is reached.

 

Parent topic:

Examples: Using a cursor