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 is the name of the function the Ingres runtime system calls when an error occurs.
– event_routine is the name of the function the Ingres runtime system calls when an event is raised. message_routine is 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/BASIC:
! Main program
program error_trap
exec sql include sqlca
external integer error_func ! declare error handler
exec sql connect dbname
exec sql set_sql (errorhandler = error_func)
!
! esql will generate
! call iilqshsethandler (1, error_func)
!
. . .
end program
function integer error_func()
exec sql include sqlca
exec sql begin declare section
declare integer errnum
exec sql end declare section
exec sql inquire_sql (:errnum = errorno)
print 'error number is ' + str$(errnum)
end function
Last modified date: 01/30/2023