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 conditions are dbevent, sqlwarning, sqlerror, sqlmessage, and not found.
action
Specifies the action to take. Valid actions are continue, stop, goto a label, and call a Fortran subroutine.
For a detailed description of this statement, see the SQL Reference Guide.
In Fortran, all subroutine names must be legal Fortran identifiers, beginning with an alphabetic character. In VMS, you can also use an underscore. If the subroutine name is an Embedded SQL reserved word, specify it in quotes. All labels specified in a whenever goto action must be legal statement numbers. Note that Embedded SQL reserves statement numbers 7000 through 12000.
The label targeted by the goto action must be within 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 Fortran statement:
if (condition) goto label
after an Embedded SQL statement. If the label is an invalid statement number, the Fortran compiler generates an error.
The same scope rules apply to subroutine names used with the call action as to label numbers used with the goto action. However, the reserved subroutine name sqlprint, which prints errors or database procedure messages and then continues, is always within 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 issuing a Fortran return statement. The Fortran return statement causes the program to continue execution with the statement following the Embedded SQL statement that generated the error.
The following example demonstrates the 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.
Example: whenever statement usage
C
C Main error handling program
C
program DbTest
exec sql include sqlca
exec sql begin declare section
integer*2 eno
character*20 ename
integer*1 eage
exec sql end declare section
exec sql declare empcsr cursor for
1 select eno, ename, age
2 from employee
C An error when opening the "personnel" database will
C cause the error to be printed and the program
C to abort.
exec sql whenever sqlerror stop
exec sql connect personnel
C Errors from here on will cause the program to clean up
exec sql whenever sqlerror call ClnUp
exec sql open empcsr
print *, 'Some values from the "employee" table'
C When no more rows are fetched, close the cursor
exec sql whenever not found goto 200
C The last executable Embedded SQL statement was
C an OPEN, so we know that the value of "sqlcod"
C cannot be SQLERROR or NOT FOUND.
C The following loop is broken by NOT FOUND
exec sql fetch empcsr
1 into :eno, :ename, :age
C This "print" does not execute after the previous
C FETCH returns the NOT FOUND condition.
print *, eno, ename, age
if (sqlcod .eq. 0) goto 100
C From this point in the file onwards, ignore all
C errors.
C Also turn off the NOT FOUND condition, for
C consistency.
exec sql whenever sqlerror continue
exec sql whenever not found continue
200 exec sql close empcsr
exec sql disconnect
end
C
C ClnUp: Error handling subroutine (print error
C and disconnect).
C
subroutine ClnUp
exec sql include sqlca
exec sql begin declare section
character*100 errmsg
exec sql end declare section
exec sql inquire_sql (:errmsg=ERRORTEXT)
print *, 'Aborting because of error'
print *, errmsg
exec sql disconnect
C Do not return to DbTest
stop
end