Example 2: SQL error handling with WHENEVER

This code shows a typical SQL error handling block. It uses WHENEVER ERROR CONTINUE before executing SQL statements, tests the SQLCA.SQLCODE register for errors after each SQL instruction, and resets the default exception handler with WHENEVER ERROR STOP after the set of SQL commands to be controlled:

MAIN
  DEFINE
      tabname VARCHAR(50),
      sqlstmt STRING,
      rowcount INTEGER

  # In the DATABASE statement, no error should occur...
  DATABASE stores 

  # But next SELECT may fail, if the user enters an invalid table name.
  WHENEVER ERROR CONTINUE
  PROMPT "Enter a table name:" FOR tabname 
  LET sqlstmt = "SELECT COUNT(*) FROM " || tabname 
  PREPARE s FROM sqlstmt 
  IF sqlca.sqlcode THEN
     DISPLAY "SQL Error occurred:", sqlca.sqlcode 
     EXIT PROGRAM 1
  END IF
  EXECUTE s INTO rowcount 
  IF sqlca.sqlcode THEN
     DISPLAY "SQL Error occurred:", sqlca.sqlcode 
     EXIT PROGRAM 1
  END IF
  WHENEVER ERROR STOP

  ... (more instructions, stopping the program in case of error)

END MAIN

Program output in case of invalid table name:

SQL Error occurred:      -217