Writing an SQL Program › SQL Status Checking and Error Handling › Checking Specific Errors

Checking Specific Errors

When To Do It

In certain situations the program should check for specific errors before directing processing to a generalized error routine.

This guide discusses when to code specific error-checking logic in Chapter 4, Data Manipulation with SQL, and other chapters that present SQL programming techniques.

How to Do It

You write a conditional program statement to check for a specific SQL error following the SQL statement. The conditional statement must also account for all other errors if the test for the specific error fails:

EXEC SQL
  INSERT
    INTO DIVISION
    VALUES (:DIVISION-CODE,
            :DIVISION-NAME,
            :DIV-HEAD-ID)
END-EXEC.

IF SQLSTATE = '23501' PERFORM EXISTING-DIVISION
ELSE IF SQLCODE < 0 GOTO ERROR-ROUTINE.

Using WHENEVER SQLERROR

To perform specific error checking after the program has issued a WHENEVER SQLERROR statement, you can:

• Override the previous WHENEVER statement before issuing the SQL statement:

  EXEC SQL
    WHENEVER SQLERROR CONTINUE
  END-EXEC.
  
  EXEC SQL
    INSERT
   .
   .
   .
  END-EXEC.
  
  IF SQLSTATE = '23501' PERFORM EXISTING-DIVISION
  ELSE IF SQLCODE < 0 GOTO ERROR-ROUTINE.
  
  EXEC SQL
    WHENEVER SQLERROR GOTO ERROR-ROUTINE
  END-EXEC.
  

• Place the specific error-handling logic in the generalized routine:

  ERROR-ROUTINE.
  
  IF SQLSTATE = '23501' PERFORM EXISTING-DIVISION.
   .
   .
   .