Error Handling with the Whenever Statement
The syntax of the whenever statement is as follows:
exec sql whenever condition action
condition
Specifies the error condition. Valid error condidtions are dbevent, sqlwarning, sqlerror, sqlmessage, and not found.
action
Specifies the action to be taken. Valid actions are continue, stop, goto a label or a line number, and call a BASIC subroutine.
For a detailed description of this statement, see the SQL Reference Guide.
The subroutine names qualifying the call action must be legal BASIC identifiers beginning with an alphabetic character or an underscore. If the subroutine name is an Embedded SQL reserved word, specify it in quotes. Note that the label or line number targeted by the goto 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 the preprocessor may generate the BASIC statement:
if (condition) then
goto label
end if
after an Embedded SQL statement. If the label is outside the scope of the current Embedded SQL statement, the BASIC compiler will generate an error.
The same scope rules apply to subroutine names used with the call action. However, the reserved subroutine name sqlprint, which prints errors or database procedure messages and then continues, is always in the scope of the program.
When a whenever statement specifies a call as the action, the target subroutine is called and, after its execution, control returns to the statement following the statement that caused the subroutine to be called. Consequently, after handling the whenever condition in the called subroutine, you may want to take some action, instead of merely returning from the BASIC subroutine.
The following example demonstrates use of the whenever statements 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.
10 ! Main error handling program
exec sql include sqlca
exec sql begin declare section
declare integer eno
declare string ename
declare byte eage
exec sql end declare section
exec sql declare empcsr cursor for &
select idno, name, age &
from employee
! An error when opening the personnel database will
! cause the error to be printed and the program to abort
exec sql whenever sqlerror stop
exec sql connect personnel
! Errors from here on will cause the program to clean up
exec sql whenever sqlerror call clean_up
exec sql open empcsr
print 'Some values from the "employee" table'
! When no more rows are fetched, close the csr
exec sql whenever not found goto Close_Csr
! The last executable Embedded SQL statement was an
! OPEN, so we know that the value of "sqlcode" cannot
! be SQLERROR or NOT FOUND.
while (sqlcode = 0)
exec sql fetch empcsr &
into :eno, :ename, :eage
! This "print" does not execute after the previous
! FETCH returns the NOT FOUND condition.
print eno, ename, eage
next
! From this point in the file onwards, ignore all
! errors. Also turn off the NOT FOUND condition,
! for consistency.
Close_Csr: EXEC SQL CLOSE empcsr
exec sql disconnect
end ! Db_Test
! Clean_Up: Error handling subroutine (print error and disconnect).
20 sub Clean_Up
exec sql include sqlca
exec sql begin declare section
declare string errmsg
exec sql end declare section
exec sql inquire_sql(:errmsg = errortext)
print 'aborting because of error'
print errmsg
exec sql disconnect
! Do not return to Db_Test
stop
end sub ! Clean_Up