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);
This program inserts a row into the book table using the data handler Put_Handler to transmit the value of column chapter_text from a text file to the database in segments. 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:
package DataHdlrPkg is
type Hdlr_Rec is
record
argstr: String(1..100);
argint: Integer; -- 4-byte integers
end record;
function Put_Handler(info: Hdlr_Rec) return Integer;
function Get_Handler(Info: Hdlr_Rec) return Integer;
pragma export_function(Put_Handler);
pragma export_function(Get_Handler);
end DataHdlrPkg;
with DataHdlrPkg; use DataHdlrPkg;
procedure handler is
exec sql include sqlca;
-- Do not declare the datahandlers nor the datahandler
-- argument to the ESQL preprocessor
hdlr_arg: Hdlr_Rec;
-- Null indicator for datahandler must be declared to
-- ESQL
--
exec sql begin declare section;
indvar: Short_integer;
chapter_num: Integer;
exec sql end declare section;
-- Insert a long varchar value chapter_text into the
-- table book using the datahandler Put_Handler
-- The argument passed to the datahandler is the
-- address of the record hdlr_arg
...
exec sql insert into book (chapter_num, chapter_name,
chapter_text)
values (5, 'One Dark and Stormy Night',
Datahandler(Put_handler(hdlr_arg)));
-- 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 the column chapter_text
-- retrieved. For null values the indicator
-- variable will be set to "-1" and the datahandler
-- will not be called.
...
exec sql select chapter_text into
:chapter_num,
datahandler (Get_Handler(hdlr_arg)) :indvar
from book;
exec sql begin;
process row ...
exec sql end;
...
end handler;