Was this helpful?
Error Handling Using the SQLCA
Error handling with the SQLCA can be done implicitly by using whenever statements or explicitly by checking the contents of the SQLCA fields sqlcode, sqlerrd(2), and sqlwarn0.
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
The Whenever Goto Action in Embedded SQL Blocks
An Embedded SQL block-structured statement is a statement delimited by the begin and end clauses. For example, the select loop and the unloadtable loop are both block-structured statements. These statements can only be terminated by the methods specified for the particular statement in the SQL Reference Guide. For example, the select loop is terminated either when all the rows in the database result table have been processed or by an endselect statement, and the unloadtable loop is terminated either when all the rows in the forms table field have been 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 would cause 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 BASIC exit or goto statement that causes control to leave or enter 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, the above precautions need not be taken.
The above information does not apply to error handling for database statements issued outside an SQL block nor 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 BASIC statements.
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
        ! Exit if database cannot be opened
        exec sql connect personnel
        if (sqlcode < 0) then
                print 'Cannot access database'
                stop
        end if
! Error if cannot open cursor
        exec sql open empcsr
        if (sqlcode < 0) then
                call Clean_Up('OPEN "empcsr"')
        end if
        print 'Some values from the "employee" table'
! The last executable Embedded SQL statement was an OPEN, so we know
! that the value of "sqlcode" cannot be SQLERROR or NOT FOUND
! The following loop is broken by NOT FOUND condition 100) or an error
        while (sqlcode = 0)
             exec sql fetch empcsr &
                into :eno, :ename, :eage
        if (sqlcode < 0) then
            call Clean_Up('FETCH "empcsr"')

! Do not print the last values twice
        else
            if (sqlcode <> 100) then
                print eno, ename, eage
            end if
        end if
    next
    exec sql close empcsr
    exec sql disconnect

end
! Clean_Up: Error handling subroutine (print error and disconnect).

20 sub Clean_Up(string reason)

        exec sql include sqlca
        exec sql begin declare section
            declare string errmsg
        exec sql end declare section
        print 'aborting because of error in', reason
        exec sql inquire_sql (:errmsg = errortext)
        print errmsg

        exec sql disconnect
! Do not return to main program
     stop

end sub ! clean_up
How to Determine the Number of Affected Rows
The SQLCA variable sqlerrd(2) indicates how many rows were affected by the last row-affecting statement. Note that this variable is referenced by sqlerrd(2) rather than sqlerrd(3) as in other languages, because BASIC allocates sqlerrd elements 0 through 5.
The following program fragment, which deletes all employees whose employee numbers are greater than a given number, demonstrates how to use sqlerrd:
sub delete_rows(integer lower_bound_num)

    exec sql include sqlca
    exec sql begin declare section
        declare integer low_eno
    exec sql end declare section
! Use Embedded SQL variable in DELETE statement
    low_eno = lower_bound_num
    exec sql delete from employee &
        where eno :low_eno
! Print the number of employees deleted
    print sqlerrd(2), 'row(s) were deleted.'
end sub ! Delete_Rows
Last modified date: 08/28/2024