The following variables are used to validate the successful execution of stored procedures:
▪ SQLSUCCESS: The return value of the latest EXEC SQL statement that is executed inside a procedure body is stored into the variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, the value 1 is stored into SQLSUCCESS. After a failed SQL statement, a value 0 is stored into SQLSUCCESS.
The value of SQLSUCCESS can be used, for instance, to determine when the cursor has reached the end of the result set as shown in the following example: EXEC SQL FETCH sel_tab; -- loop as long as last statement in loop is successful WHILE SQLSUCCESS LOOP -- do something with the results, for example, return a row EXEC SQL FETCH sel_tab; END LOOP
▪ SQLERRNUM: The SQLERRNUM variable contains the error code of the latest SQL statement executed. It is automatically generated for every procedure. After successful execution, SQLERRNUM contains zero (0).
▪ SQLERRSTR: The SQLERRSTR variable contains the error string from the last failed SQL statement.
▪ SQLROWCOUNT: After the execution of UPDATE, INSERT and DELETE statements, an additional variable is available to check the result of the statement. The variable SQLROWCOUNT contains the number of rows affected by the last statement.
▪ SQLERROR: To generate user errors from procedures, you can use the SQLERROR variable to return the error (that caused the statement to fail) to the calling application by using the following syntax: RETURN SQLERROR 'error string' RETURN SQLERROR char_variable
The error is returned in the following format:
User error: error_string
▪ SQLERROR OF cursorname: To return the actual error that caused the statement to fail to the calling application, use the following syntax: EXEC SQL PREPARE cursornamesql_statement; EXEC SQL EXECUTE cursorname; IF NOT SQLSUCCESS THEN RETURN SQLERROR OF cursorname; END IF END IF
Processing stops immediately when this statement is executed and the procedure return code is SQLERROR. The actual database error can be returned by using the SQLError function:
The generic error handling method for a procedure can be declared by using the following statement in the stored procedure:
When this statement is included in a stored procedure, all return values of executed SQL statements are checked for errors. If a statement execution returns an error, the procedure is automatically aborted and SQLERROR code of the last cursor is returned. Optionally the transaction can also be rolled back.
The statement should be included before any EXEC SQL statements and directly following the DECLARE section.
The following example is a complete procedure that returns all table names from the SYS_TABLES system table that start with "SYS":
"CREATE PROCEDURE sys_tabs RETURNS (tab VARCHAR) BEGIN -- abort on errors EXEC SQL WHENEVER SQLERROR ROLLBACK, ABORT; -- prepare the cursor EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE 'SYS%'; -- execute the cursor EXEC SQL EXECUTE sel_tables INTO (tab); -- loop through rows EXEC SQL FETCH sel_tables; WHILE sqlsuccess LOOP RETURN ROW; EXEC SQL FETCH sel_tables; END LOOP -- close and drop the used cursors EXEC SQL CLOSE sel_tables; EXEC SQL DROP sel_tables; END";