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:
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 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
...
end
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)
end
Last modified date: 11/28/2023