How You Can Use the DBMSErrorPrinting Attribute

Another method of handling errors is to call a general error-handling procedure each time an error is detected. For example, the script for the check_out frame in the Videos application calls an error handling procedure after each database statement to detect and handle any DBMS errors. The following code fragment from the check_out frame script calls the error handler:

/* Update video information */
update v_video
set last_checkout =
    :checkout_form.checkout[i].date_out,
    no_checkouts = no_checkouts + 1
where vid_no = :checkout_form.checkout[i].vid_no;

if error_handler(frm = CurFrame, commit_evt =
    'Commit')
    != ER_OK then
        resume;
endif;
...

The if statement in the example calls the error-handling procedure. This procedure first checks to see if there was a DBMS error. If none occurred, it returns ER_OK to the calling program. If an error occurred and the error was not a deadlock error, then the routine displays an error message that includes the generic and local DBMS error number. If the error was a deadlock, the procedure sends an event back to the application to retry the DBMS statement.

/*
** Name: error_handler -- Detects and handles DBMS
** errors.
**
** Description:
** This procedure first checks if an error occurred
** in the previous DBMS statement. If so, it pops up
** an error message. If the error was deadlock or
** another error for which retry is appropriate, it
** sends an event back to the calling
** frame to retry the DBMS statement.
** No need to roll back the transaction in either
** case because the main_control frame set the
** application's database session to roll back
** automatically for all DBMS errors.
**

** Called By:
** check_out frame.
**
** Parameters:
** frm          Frame which called this procedure.
** commit_evt   Event sent to calling frame when
**              there is a deadlock to retry DBMS
**              statements.
**
** Returns:
** DBSession.ErrorNumber set by previous DBMS
** statement;
** ER_OK (0) if success
*/

procedure error_handler
(
    frm = FrameExec,            /* Calling frame */
    commit_evt = varchar(32),   /* Commit event for
                                ** retries if
                                ** a deadlock
                                ** occurred. */
) =
declare
    err_no = integer not null   /* Error number */
enddeclare

begin
    err_no = CurProcedure.DBSession.ErrorNumber;
    if err_no = 49000 then
    /* We have encountered a deadlock or log
    **  full. */
        message 'Deadlock error occurred while
        updating the database.';
        frm.SendUserEvent(commit_evt);
    elseif err_no != ER_OK then
        message 'Error occurred while making changes
        in the database.' +
        dbms_error_message();
    endif;

    return err_no;
end;

Note: The error-handling procedure calls another procedure, dbms_error_message, when a non-deadlock DBMS error occurs. This called procedure returns the generic and local error numbers. For the code for the dbms_error_message procedure, see How You Can Use the ErrorNumber and DBMSError Attributes (see How You Can Use the ErrorNumber and DBMSError Attributes).