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:
Use the following syntax to specify 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);
• The 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, you must not declare the handler 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/C.
Example: Declaring a handler
exec sql include sqlca;
main()
{
int error_func();
exec sql connect dbname;
exec sql set_sql (errorhandler = error_func);
. .
}
int
error_func()
{
exec sql begin declare section;
int errnum;
exec sql end declare section;
exec sql inquire_sql (:errnum = ERRORNO);
printf ("Error number is %d", errnum);
return 0;
}
If you are using ANSI C function prototypes, declare the handler function prototype as follows:
int error_funct(void);
where the handler is defined as follows:
int error_funct(void)
{
...
}
Last modified date: 04/03/2024