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.
Syntax Notes:
• Because Micro Focus COBOL does not support a function pointer data type, you must write a short embedded SQL/C procedure to register your handler with the Ingres runtime system. For more information, see Include User-Defined Handlers in the Micro Focus RTS--UNIX in this chapter.
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);
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.
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.
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.
How to Declare User-Defined Handlers
The following examples show how to declare a user-defined handler in ESQL/COBOL:
UNIX:
IDENTIFICATION DIVISION.
PROGRAM-ID. TEST-PROG.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING_STORAGE SECTION.
WORKING_STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
...
PROCEDURE DIVISION.
BEGIN.
EXEC SQL CONNECT dbname END-EXEC.
* Call "C" routine to set error handler on.
* ErrProg will be called whenever an error occurs.
CALL "ErrTrap".
...
...
...
PROCEDURE DIVISION.
BEGIN.
EXEC SQL CONNECT dbname END-EXEC.
* Call "C" routine to set error handler on.
* ErrProg will be called whenever an error occurs.
CALL "ErrTrap".
...
...
* Suppress display of error number (don't call ErrProg) for next statement by
* turning error handler off.
EXEC SQL SET_SQL(ERRORHANDLER = 0) END-EXEC.
EXEC SQL .....
* Turn error handler back on. ErrProg will now be
* called again whenever an error occurs.
CALL "ErrTrap".
...
END PROGRAM TEST-PROG.
* turning error handler off.
EXEC SQL SET_SQL(ERRORHANDLER = 0) END-EXEC.
EXEC SQL .....
* Turn error handler back on. ErrProg will now be
* called again whenever an error occurs.
CALL "ErrTrap".
...
END PROGRAM TEST-PROG.
The following is an example of a user-defined error handler:
IDENTIFICATION DIVISION.
PROGRAM-ID. ErrProg.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROGRAM-ID. ErrProg.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END_EXEC.
01 errnum PIC S9(9) USAGE DISPLAY.
EXEC SQL END DECLARE SECTON END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL INQUIRE_SQL(:errnum = ERRORNO) END-EXEC.
DISPLAY "Errnum is " errnum.
END PROGRAM ErrProg.
01 errnum PIC S9(9) USAGE DISPLAY.
EXEC SQL END DECLARE SECTON END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL INQUIRE_SQL(:errnum = ERRORNO) END-EXEC.
DISPLAY "Errnum is " errnum.
END PROGRAM ErrProg.
The following example is an embedded SQL/C routine that declares ErrProg to the Ingres runtime system:
ErrTrap()
{
extern int ErrProg();
EXEC SQL SET_SQL (ERRORHANDLER = ErrProg);
}
{
extern int ErrProg();
EXEC SQL SET_SQL (ERRORHANDLER = ErrProg);
}
VMS:
IDENTIFICATION DIVISION.
PROGRAM-ID. Test-prog.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 error_func PIC S9(9) USAGE COMP VALUE EXTERNAL
ErrProg.
...
01 error_func PIC S9(9) USAGE COMP VALUE EXTERNAL
ErrProg.
...
PROCEDURE DIVISION
BEGIN.
EXEC SQL CONNECT dbname END-EXEC.
EXEC SQL SET_SQL (ERRORHANDLER = error_func)
END-EXEC.
. . .
END PROGRAM Test-prog.
IDENTIFICATION DIVISION.
PROGRAM-ID. ErrProg.
ENVIRONMENT DIVISION.
DATA DIVISION.
BEGIN.
EXEC SQL CONNECT dbname END-EXEC.
EXEC SQL SET_SQL (ERRORHANDLER = error_func)
END-EXEC.
. . .
END PROGRAM Test-prog.
IDENTIFICATION DIVISION.
PROGRAM-ID. ErrProg.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 errnum PIC S9(9) USAGE DISPLAY.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL INQUIRE_SQL (:errnum = ERRORNO) END-EXEC.
DISPLAY "Errnum is " errnum.
END PROGRAM ErrProg.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 errnum PIC S9(9) USAGE DISPLAY.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
EXEC SQL INQUIRE_SQL (:errnum = ERRORNO) END-EXEC.
DISPLAY "Errnum is " errnum.
END PROGRAM ErrProg.
Include User-Defined Handlers in the Micro Focus RTS--UNIX
You must follow the procedures below to include user-defined handlers in the new Micro Focus Runtime System (RTS) that you create. For a complete description of how to incorporate Ingres into the Micro Focus RTS, see How to Incorporate Ingres into the Micro Focus RTS--UNIX in this chapter.
To include user-defined handlers in the Micro Focus RTS
1. For each user-defined handler, build the object code as follows:
% esqlcbl errhandler.scb
% cob -x -c errhandler.cbl
% cob -x -c errhandler.cbl
% esqlcbl msghandler.scb
% cob -x -c msghandler.cbl
% cob -x -c msghandler.cbl
% esqlcbl evthandler.scb
% cob -x -c evthandler.cbl
% cob -x -c evthandler.cbl
2. Because Micro Focus COBOL does not support a Function Pointer data type, you must write a short embedded SQL/C procedure to register your user-defined handler with the Ingres Runtime System. This embedded SQL/C procedure only needs to declare the handler, and execute the appropriate set_sql statement. For example:
ErrTrap()
{
{
extern int ErrProg();
exec sql set_sql(errorhandler = ErrProg);
}
MsgTrap()
{
MsgTrap()
{
extern int MsgProg();
exec sql set_sql(messagehandler = MsgProg);
}
EvtTrap()
{
EvtTrap()
{
extern int EvtProg();
exec sql set_sql(dbeventhandler = EvtProg);
}
ErrProg, MsgProg and EvtProg are embedded SQL/COBOL programs that handle Ingres errors, database procedure messages and database events respectively.
3. Build the object code of the embedded SQL/C registration procedure, as follows:
% esqlc cproc.sc
% cc -c cproc.c
% cc -c cproc.c
where cproc.sc is the name of the file containing the procedure(s) that you wrote for Step 2.
4. Link the compiled handlers and the C registration procedure(s) into your RTS by modifying the COB command line to include the object files. Specify the object files before the list of system libraries, as follows:
cob -x -e "" -o ingrts
iimfdata.o iimflibq.o\
cproc.o \
errhandler.o msghandler.o evthandler.o\
$II_SYSTEM/ingres/lib/libingres.a\
-lc -lm
cproc.o is the name of the object file that Step 3 produces. It contains the C registration procedure(s) for the user-defined handlers.
5. Add COBOL CALL statements to your source program wherever you wish to set the handler. For example:
* To set the errorhandler on:
CALL "ErrTrap".
* To set the messagehandler on:
CALL "MsgTrap".
* To set the dbeventhandler on:
CALL "EvtTrap".
You may unset the user-defined handler directly from your embedded SQL/COBOL program with the SET_SQL statement:
exec sql set_sql (errorhandler = 0) end-exec.
exec sql set_sql (messagehandler = 0) end-exec.
exec sql set_sql (dbeventhandler = 0) end-exec.
exec sql set_sql (messagehandler = 0) end-exec.
exec sql set_sql (dbeventhandler = 0) end-exec.
Last modified date: 11/28/2023