Was this helpful?
User-Defined Error, DBevent, and Message Handlers
You can use user-defined handlers to capture errors, messages, or events during the processing of a database statement. Use these handlers instead of the sql whenever statements with the SQLCA when you want to do the following:
Capture more than one error message on a single database statement
Capture more than one message from database procedures fired by rules
Trap errors, events, and messages as the DBMS raises them
If an event is raised when an error occurs during query execution, the WHENEVER mechanism detects only the error and defers acting on the event until the next database statement is executed.
User-defined handlers offer you flexibility. If, for example, you want to trap an error, you can code a user-defined handler to issue an inquire_sql to get the error number and error text of the current error. You can then switch sessions and log the error to a table in another session; however, you must switch back to the session from which the handler was called before returning from the handler. When the user handler returns, the original statement continues executing. User code in the handler cannot issue database statements for the session from which the handler was called.
The handler must be declared to return an integer. However, the preprocessor ignores the return value.
Syntax Notes:
The following syntax describes the three types of handlers:
exec sql set_sql (errorhandler   = error_routine|0)
exec sql set_sql (dbeventhandler = event_routine|0)
exec sql set_sql (messagehandler = message_routine|0)
Errorhandler, dbeventhandler, and messagehandler denote a user-defined handler to capture errors, events, and database messages respectively, as follows:
Specifies the name of the function the Ingres runtime system calls when an error occurs.
Specifies the name of the function the Ingres runtime system calls when an event is raised.
Specifies the name of the function the Ingres runtime system calls whenever a database procedure generates a message.
Errors that occur in the error handler itself do not cause the error handler to be re-invoked. You must use inquire_sql to handle or trap any errors that may occur in the handler.
Unlike regular variables, the handler must not be declared
in an ESQL declare section; therefore, do not use a colon before the handler argument. (However, you must declare the handler to the compiler.)
If you specify a zero (0) instead of a name, the zero will unset
the handler.
User-defined handlers are also described in the SQL Reference Guide.
How to Declare User-Defined Handlers
The following example shows how to declare a handler for use in the set_sql errorhandler statement for ESQL/Fortran:
program TestProg

     exec sql include sqlca

     external error_func
     integer error_func

         exec sql connect dbname
     exec sql set_sql (errorhandler = error_func)
                  program code

integer function error_func

         exec sql include sqlca
         exec sql begin declare section
                integer errnum
         exec sql end declare section
         exec sql inquire_sql (:errnum = ERRORNO)
     write (*,60) errnum
60       format ('Errnum is ', I)
Last modified date: 06/10/2024