Error Handling Using the SQLCA
User-Defined Error, Message, and DBevent Handlers offer the most flexibility for handling errors, database procedure messages, and database events. For more information, see
Advanced Processing in this chapter.
However, you can do error handling with the SQLCA by using whenever statements or explicitly by checking the contents of the SQLCA fields SQLCODE, SQLERRD, and SQLWARN0.
Error Handling with the Whenever Statement
The syntax of the whenever statement is:
exec sql whenever condition action end-exec
condition
Specifies the error condition. Valid conditions are: sqlwarning, sqlerror, sqlmessage, dbevent, and not found.
action
Specifies the action to be taken. Valid actions are: continue, stop, goto a COBOL paragraph name, and call a COBOL paragraph name. The call action causes the preprocessor to generate a COBOL PERFORM statement for the specified paragraph name.
For a detailed description of the whenever statement, see the SQL Reference Guide.
If the paragraph name in a goto or call action is an embedded SQL reserved word, specify it in quotes. The paragraph name targeted by the goto or call action must be in the scope of all subsequent embedded SQL statements until another whenever statement is encountered for the same action. This is necessary because when the preprocessor interprets a whenever goto statement, it generates the COBOL statement:
IF (condition) THEN
GO TO paragraph_name
END-IF
after an embedded SQL statement. Similarly, in interpreting a whenever call statement, the preprocessor generates the COBOL statement:
IF (condition) THEN
PERFORM paragraph_name
END-IF
after subsequent embedded SQL statements. If the paragraph name is invalid, the COBOL compiler generates an error.
You can also use user‑defined handlers for error handling. For more information, see the SQL Reference Guide. Note that the reserved procedure sqlprint, which can substitute for a paragraph name in a whenever call statement, is always in the scope of the program.
When the condition specified for a call action occurs, control passes to the first statement in the named paragraph. After the last statement contained in the paragraph has been executed, control returns to the statement following the statement that caused the call to occur. Consequently, after handling the whenever condition in the called paragraph, you may want to take some action, instead of merely allowing execution to continue with the statement following the embedded SQL statement that generated the error.
The following example demonstrates use of the whenever statement in the context of printing some values from the employee table. The comments do not relate to the program but to the use of error handling.
Example: whenever statement usage
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 E-REC.
02 ENO PIC S9(8) USAGE DISPLAY.
02 FILLER PIC X(2) VALUE SPACES.
02 ENAME PIC X(20).
02 AGE PIC S9(4) USAGE DISPLAY.
01 ERRMSG PIC X(200).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT eno, ename, age
FROM employee
END-EXEC.
* An error when opening the "personnel" database will
* cause the error to be printed and the program to
* abort.
EXEC SQL WHENEVER SQLERROR STOP END-EXEC.
EXEC SQL CONNECT personnel END-EXEC.
* Errors from here on will cause the program to clean up
EXEC SQL WHENEVER SQLERROR
GOTO CLEAN-UP END-EXEC.
EXEC SQL OPEN empcsr END-EXEC.
DISPLAY "Some values from
the ""employee"" table".
* When no more rows are fetched, close the cursor
EXEC SQL WHENEVER NOT FOUND GOTO CLOSE-CSR
END-EXEC.
* The last statement was an OPEN, so we know that the
* value of SQLCODE cannot be SQLERROR or NOT FOUND.
* Loop is broken by NOT FOUND
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH empcsr
INTO :ENO, :ENAME, :AGE END-EXEC
* The DISPLAY does not execute after the previous FETCH * returns the NOT FOUND condition.
DISPLAY E-REC
END-PERFORM.
* From this point in the file onwards, ignore all
* errors. Also, turn off the NOT FOUND condition,
* for consistency.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
CLOSE-CSR.
EXEC SQL CLOSE empcsr END-EXEC.
EXEC SQL DISCONNECT END-EXEC.
STOP RUN.
CLEAN-UP.
EXEC SQL INQUIRE_SQL(:ERRMSG = ERRORTEXT)
END-EXEC.
DISPLAY "Aborting because of error".
DISPLAY ERRMSG.
EXEC SQL DISCONNECT END-EXEC.
STOP RUN.
Whenever Goto Action in Embedded SQL Blocks
The words begin and end delimit an embedded SQL block‑structured statement is a statement. For example, the select loop and the unloadtable loops are both block‑structured statements. You can only terminate these statements using the methods specified for the particular statement in the SQL Reference Guide. For example, the select loop is terminated either when all the rows in the database result table have been processed or by an endselect statement. The unloadtable loop is terminated either when all the rows in the forms table field have been processed or by an endloop statement.
Therefore, if you use a whenever statement with the goto action in an SQL block, you must avoid going to a paragraph outside the block. Such a goto causes the block to be terminated without issuing the runtime calls necessary to clean up the information that controls the loop. (For the same reason, you must not issue a COBOL GO TO statement that causes control to leave or enter the middle of an SQL block.) The target of the whenever goto statement must be a paragraph in the block. If, however, it is a paragraph containing a block of code that cleanly exits the program, you do not need to take the above precaution.
The above information does not apply to error handling for database statements issued outside an SQL block, or to explicit hard‑coded error handling
. For an example of hard‑coded error handling, see
The Table Editor Table Field Application in this chapter.
Explicit Error Handling
Programs can also handle errors by inspecting values in the SQLCA structure at various points. For additional information, see the SQL Reference Guide.
The following example is functionally the same as the previous example, except that the error handling is hard-coded in COBOL statements.
Example: Explicit error handling
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 E-REC.
02 ENO PIC S9(8) USAGE DISPLAY.
02 ENAME PIC X(20).
02 AGE PIC S9(4) USAGE DISPLAY.
01 NOT-FOUND PIC S9(4) USAGE COMP VALUE 100.
01 REASON PIC X(14).
01 ERRMSG PIC X(100).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT eno, ename, age
FROM employee
END-EXEC.
* Exit if database cannot be opened
EXEC SQL CONNECT personnel END-EXEC.
IF SQLCODE < 0 THEN
DISPLAY "Cannot access database"
STOP RUN.
* Error if cannot open cursor
EXEC SQL OPEN empcsr END-EXEC.
IF SQLCODE < 0 THEN
MOVE "OPEN ""empcsr""" TO REASON
PERFORM CLEAN-UP.
DISPLAY "Some values from the ""employee"" table".
* The last statement was an OPEN, so we know that the
* value of SQLCODE cannot be SQLERROR or NOTFOUND.
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH empcsr
INTO :ENO, :ENAME, :AGE
END-EXEC.
IF SQLCODE < 0 THEN
MOVE "FETCH ""empcsr""" TO REASON
PERFORM CLEAN-UP
* Do not print the last values twice
ELSE
IF SQLCODE NOT = NOT-FOUND THEN
DISPLAY E-REC
END-IF
END-IF
END-PERFORM.
EXEC SQL CLOSE empcsr END-EXEC.
EXEC SQL DISCONNECT END-EXEC.
STOP RUN.
CLEAN-UP.
* Error handling paragraph
DISPLAY "Aborting because of error in " REASON.
EXEC SQL INQUIRE_SQL(:ERRMSG = ERRORTEXT) END-EXEC.
DISPLAY ERRMSG.
EXEC SQL DISCONNECT END-EXEC.
STOP RUN.
How to Determine the Number of Affected Rows
The SQLCA variable SQLERRD(3) indicates how many rows were affected by the last insert, update, or delete statement. The following program fragment, which deletes all employees whose employee numbers are greater than a given number, demonstrates how SQLERRD is used:
Example: SQLERRD usage
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION.
01 LOWER-BOUND-NUM PIC S9(8) USAGE COMP.
EXEC SQL END DECLARE SECTION.
01 SQLERRD-DISP PIC Z9(4) USAGE DISPLAY.
PROCEDURE DIVISION.
BEGIN.
...
EXEC SQL DELETE FROM employee
WHERE eno > :LOWER-BOUND-NUM
END-EXEC.
* Print the number of employees deleted
MOVE SQLERRD(3) TO SQLERRD-DISP.
DISPLAY SQLERRD-DISP " rows were deleted."
...