Was this helpful?
Error Handling Using the SQLCA
User-defined error, message and dbevent handlers offer the most flexibility for handling errors, database procedure messages, and database events. For more information, see Advanced Processing in this chapter.
However, you can do error handling with the SQLCA implicitly by using whenever statements or explicitly by checking the contents of the SQLCA fields sqlcod, sqlerr(3), and sqlwrn(0).
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 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 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
Whenever Goto Action in Embedded SQL Blocks
An Embedded SQL block-structured statement is a statement delimited by the words begin and end. For example, the select loop and unloadtable loops are block-structured statements. You can terminate these statements only by the methods specified for their termination in the SQL Reference Guide. For example, the select loop is terminated either when all the rows in the database result table are processed or by an endselect statement. The unloadtable loop is terminated either when all the rows in the forms table field are processed or by an endloop statement.
Therefore, if you use a whenever statement with the goto action in an SQL block, you must avoid going to a label outside the block. Such a goto causes the block to be terminated without issuing the runtime calls necessary to clean up the information that controls the loop. (For the same reason, you must not issue a Fortran return or goto statement that causes control to leave or enter the middle of an SQL block.) The target label of the whenever goto statement should be a label in the block. If however, it is a label for a block of code that cleanly exits the program, you do not need to take such precautions.
The above information does not apply to error handling for database statements issued outside an SQL block or to explicit hard-coded error handling. For an example of hard-coded error handling, see The Table Editor Table Field Application in this chapter.
Explicit Error Handling
The program can also handle errors by inspecting values of the SQLCA at various points. For further details, see the SQL Reference Guide.
The following example is functionally the same as the previous example, except that the error handling is hard-coded in Fortran statements.
Example: Explicit error handling

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 Exit if database cannot be opened
              exec sql connect personnel
              if (sqlcod .lt. 0) then 
                    print *, 'Cannot access database' 
                    stop 
               end if

C Error if cannot open cursor
              exec sql open empcsr 
               if (sqlcod .lt. 0) call ClnUp('OPEN "empcsr"')

              print *, 'Some values from the "employee" table'
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 
C (condition 100) or an
C error

100          exec sql fetch empcsr 
            1           into :eno, :ename, :age
                     if (sqlcod .lt. 0) then 
                                   call ClnUp('FETCH "empcsr"')
C Do not print the last values twice
                     else if (sqlcod .ne. 100) then 
                                   print *, eno, ename, age 
                     end if
            if (sqlcod .eq. 0) goto 100
            exec sql close empcsr
             exec sql disconnect
      end

C ClnUp: Error handling subroutine 
C (print error and disconnect). 
C
            subroutine ClnUp(reason)

            exec sql include sqlca
            exec sql begin declare section 
                    character*(50) reason 
                    character*100 errmsg 
             exec sql end declare section
            print *, 'Aborting because of error in ', reason 
             exec sql inquire_sql (:errmsg=ERRORTEXT) 
             print *, errmsg
            exec sql disconnect

C Do not return to DbTest
            stop
            end
How to Determine the Number of Affected Rows
The SQLCA variable sqlerr(3) indicates how many rows were affected by the last row-affecting statement. (Note that in the SQL Reference Guide, this field is called sqlerrd(3).) The following program fragment, which deletes all employees whose employee numbers are greater than a given number, demonstrates how to use sqlerr.
Example: sqlerr usage
      subroutine DelRow(lbnum)

          exec sql include sqlca
          exec sql begin declare section 
                     integer lbnum 
      exec sql end declare section
      exec sql delete from employee 
     1    where eno > :lbnum

C Print the number of employees deleted
          print *, sqlerr(3), 'row(s) were deleted.'
          end
Last modified date: 08/28/2024