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