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. Client-server communication protocols of HCL OneDB™, such as SQLI and DRDA®, support SQLSTATE code values. For a list of these codes, and for information about how to get the message text, see Using the SQLSTATE Error Status Code. 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.

Fetching from Dynamic Cursors in SPL Routines

Use the FETCH statement in an SPL routine to retrieve the next row of the active set of a specified dynamic cursor into an ordered list of SPL variables that were declared in the same SPL routine.

Syntax

The syntax of the FETCH statement in SPL routines is a subset of the syntax that FETCH supports in routines. 


1  FETCH  cursor_id  INTO +  ,   output_var
Element Description Restrictions Syntax
cursor_id Name of a dynamic cursor Must be open and must have been declared in the same SPL routine Identifier
output_var An SPL variable to store a fetched value from the row Must have been declared locally or globally in the calling context, and must be of a data type compatible with the fetched column value Identifier

Just as in ESQL/C routines, the list of output variables must correspond in number, order, and data type with column values that the SQL statement associated with the rows returned by the specified cursor.

All SPL cursors are sequential cursors. Your UDR must include logic to detect the end of the active set of the cursor, because the NOTFOUND condition does not automatically raise an exception in SPL.

The built-in SQLCODE function, which can only be called from SPL routines, can return the status code of a FETCH operation.

All other restrictions of ESQL/C on FETCH statements that reference sequential Select or Function cursors also apply to FETCH operations in SPL.

The FETCH statement in SPL routines does not support the following ESQL/C features:
  • cursor names specified as host variables
  • positional specifications or positional keywords (which require scroll cursors)
  • the USING clause with descriptors or with sqlda pointers.
In the SPL language, indicator variables are not needed. If the FETCH operation retrieves a NULL value, the SPL variable that receives that fetched value is set to NULL.

The FETCH statement can reference only dynamic cursors that the DECLARE statement defined. The cursor_id cannot specify the name of a direct cursor that the FOREACH statement of SPL declared.