Handler
This example assumes that the book table was created with the statement:
EXEC SQL CREATE TABLE BOOK
(CHAPTER_NUM INTEGER,
CHAPTER_NAME CHAR(50),
CHAPTER_TEXT LONG VARCHAR) END-EXEC.
This program inserts a row into the table book using the data handler PUT_HANDLER to transmit the value of column chapter_text from a text file to the database. Then it selects the column chapter_text from the table book using the data handler GET-HANDLER to retrieve the chapter_text column a segment at a time.
IDENTIFICATION DIVISION.
PROGRAM-ID. HANDLER-PROG.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
* Do not declare the data handlers nor the
* datahandler 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.
01 HDLR-ARG.
05 ARG-CHAR PIC X(100).
05 ARG-INT PIC S9(9) USAGE COMP.
* Argument passed through to the DATAHANDLER must be * of type POINTER.
01 ARG-ADDR USAGE POINTER.
* Null indicator for data handler must be declared to * ESQL.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CHAPTER_NUM S9(9) USAGE COMP.
01 IND-VAR S9(4) USAGE COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
* INSERT a long varchar value chapter_text into the
* table book using the datahandler PUT_HANDLER.
* The argument passed to the datahandler is a pointer to the record HDLR-ARG.
...
SET ARG-ADDR TO REFERENCE HDLR-ARG.
EXEC SQL INSERT INTO book (chapter_num,
chapter_name, chapter_text)
VALUES (5, 'One dark and stormy night',
DATAHANDLER (PUT-HANDLER (ARG-ADDR)))
END-EXEC.
...
* Select the column chapter_num and the long varchar * column chapter_text from the table book.
* The Datahandler (GET-HANDLER) will be invoked for each non-null value
* of column chapter_text retrieved. For null values the indicator variable
* will be set to "-1" and the datahandler will not be called. Again, the argument
* passed to the handler is a pointer to the record HDLR-ARG.
...
EXEC SQL SELECT chapter_num, chapter_text INTO
:CHAPTER_NUM,
DATAHANDLER (GET-HANDLER(ARG-ADDR)):IND-VAR
FROM book END-EXEC
EXEC SQL BEGIN END-EXEC
process row ...
EXEC SQL END END-EXEC.
...
END PROGRAM HANDLER-PROG.