Dynamic SQL Handler
The following is an example of a dynamic SQL handler program. This program fragment shows the declaration and usage of a datahandler in a dynamic SQL program, using the SQLDA. It uses the datahandler GET-HANDLER and the HDLR-ARG structure.
IDENTIFICATION DIVISION.
PROGRAM-ID. DYNHDLR-PROG.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE SQLDA END-EXEC.
* Do not declare the data handlers nor the
* data handler argument to the ESQL preprocessor.
01 PUT-HANDLER PIC S9(9) USAGE COMP VALUE
EXTERNAL PUT-HANDLER.
01 GET-HANDLER PIC S9(9) USAGE COMP VALUE
EXTERNAL GET-HANDLER.
* Declare argument to be passed to datahandler.
01 HDLR-ARG.
05 ARG-CHAR PIC X(100).
05 ARG-INT PIC S9(9) USAGE COMP.
C Declare IISQLHDLR
01 IISQLHDLR EXTERNAL.
05 SQLARG USAGE POINTER.
05 SQLHDLR PIC S9(9) USAGE COMP.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 INDVAR PIC s9(4) USAGE COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
. . .
* Set the IISQLHDLR structure with the appropriate datahandler and
* datahandler argument.
MOVE GET-HANDLER TO SQLHDLR.
SET SQLARG TO REFERENCE HDLR-ARG.
* Describe the statement into the SQLDA.
STMT-BUF = "select * from book".
EXEC SQL PREPARE stmt FROM :STMT-BUF END-EXEC.
EXEC SQL DESCRIBE stmt INTO :SQLDA END-EXEC.
* Set the SQLDATA variables correctly.
PERFORM SETUP-COLUMN VARYING COL FROM 1 BY 1
UNTIL (COL > SQLD).
* The Datahandler (GET-HANDLER) will be invoked for
* each non-null value of column "chapter_text"
* retrieved. For null values the SQLIND will be set * to "-1" and the datahandler
* will not be called.
EXEC SQL EXECUTE IMMEDIATE :STMT-BUF USING
:SQLDA END-EXEC
EXEC SQL BEGIN END-EXEC
process row ...
EXEC SQL END END-EXEC
. . .
SETUP-COLUMN.
. . .
* The Describe statement will return 22 for long
* varchar and -22 for Nullable Long Varchar
IF (SQLTYPE(COL) = 22)
MOVE 46 TO SQLTYPE(COL)
SET SQLDATA(COL) TO REFERENCE IISQLHDLR
SET SQLIND(COL) TO REFERENCE INDVAR
ELSE
. . .
END-IF.
. . .
END PROGRAM DYNHLDR-PROG.