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