Checking the Result of FETCH

You can use the SQLSTATE variable to check the result of each FETCH statement. The database server sets the SQLSTATE variable after each SQL statement. If a row is returned successfully, the SQLSTATE variable contains the value 00000. If no row is found, the database server sets the SQLSTATE code to 02000, which indicates no data found, and the current row is unchanged. The following conditions set the SQLSTATE code to 02000, indicating no data found:

• The active set contains no rows.
• You issue a FETCH NEXT statement when the cursor points to the last row in the active set or points past it.
• You issue a FETCH PRIOR or FETCH PREVIOUS statement when the cursor points to the first row in the active set.
• You issue a FETCH RELATIVE n statement when no nth row exists in the active set.
• You issue a FETCH ABSOLUTE n statement when no nth row exists in the active set.

The database server copies the SQLSTATE code from the RETURNED_SQLSTATE field of the system-diagnostics area. You can use the GET DIAGNOSTICS statement to examine the RETURNED_SQLSTATEfield directly. The system-diagnostics area can also contain additional error information.

You can also use SQLCODE variable of the SQL Communications Area (sqlca) to determine the same results.

Related Information