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 Ingres runtime system ignores the return value.
Note:
Windows and UNIX:
Use the following embedded SQL/C procedure to set the handlers:
exec sql set_sql (errorhandler = error_routine);
exec sql set_sql (messagehandler = message_routine);
exec sql set_sql (dbeventhandler = event_routine);
VMS:
Use the following embedded SQL/C procedure to set the handlers:
exec sql set_sql (errorhandler = error_routine) end-exec.
exec sql set_sql (messagehandler = message_routine) end-exec.
exec sql set_sql (dbeventhandler = event_routine) end-exec.
The handlers may be unset directly from your embedded SQL/COBOL program:
exec sql set_sql (errorhandler = 0) end-exec.
exec sql set_sql (messagehandler = 0) end-exec.
exec sql set_sql (dbeventhandler = 0) end-exec.
• Errorhandler, dbeventhandler, and messagehandler denote a user-defined handler to capture errors, events, and database messages respectively, as follows:
error_routine
Specifies the name of the function the Ingres runtime system calls when an error occurs.
event_routine
Specifies the name of the function the Ingres runtime system calls when an event is raised.
message_routine
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 reinvoked. You must use inquire_sql to handle or trap any errors that may occur in the handler.
• Unlike regular variables, the handler in the embedded SQL SET_SQL statement is not prefaced by a colon. The handler must not be declared in an embedded SQL declare section although you must declare the handler to the compiler.
• If you specify a zero (0) instead of a name, the zero will unset the user-defined handlers are also described in the SQL Reference Guide.