Sample Programs
The programs in this section are examples of how to declare and use user-defined data handlers in an ESQL/Pascal program. There are examples of a handler program, a put handler program, a get handler program and a dynamic SQL handler program.
Handler
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. Then it selects the column chapter_text from the table book using the data handler Get_Handler to process each row returned.
program handler(input,output);
exec sql include sqlca
-- Do not declare the data handlers nor the data handler argument
-- to the ESQL preprocessor
type
String100 = packed array [1..100] of char;
hdlr_rec = record
argstr: String100;
argint: Integer;
end;
var
hdlr_rec: hdlr_arg;
exec sql begin declare section;
indvar; II_int2;
seg_buf; packed array [1...1000] of char;
seg_len; integer;
data_end; integer;
max_len; integer;
exec sql end declare section;
Put Handler
This user defined handler shows how an application can use the put data handler to enter a chapter of a book from a text file into a database.
function Put_Handler(info: hdlr_rec) : Integer;
begin
process information passed in via the info record...
open file ...
data_end := 0;
while (not end-of-file) do begin
read segment from file into seg_buf...
if (end-of-file) then begin
data_end := 1;
end;
exec sql put data (segment = :seg_buf,
segmentlength = :seg_len,
dataend = :data_end);
end; {while}
. . .
close file...
set info record to return appropriate values...
....
Put_Handler := 0 {return value ignored}
end {Put Handler }
Get Handler
This user defined datahandler shows how an application can use the get data handler to enter a chapter of a book from a text file into a database.
function Get_Handler(info: hdlr_rec) :Integer;
begin
...
process information passed in via the info record...
open file ....
data_end := 0;
while (data_end = 0) do
begin
exec sql get data (:seg_buf=segment,
:seg_len = segmentlength,
:data_end = dataend)
with maxlength = :max_len;
write segment to file...
end;
. . .
set info record to return appropriate values...
...
Get_Handler := 0; {return value ignored }
end;
begin
-- INSERT a long varchar value chapter_text into
-- the table book using the datahandler Put_Handler
-- The argument passed to the datahandler 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 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.
...
...
exec sql select chapter_text into
datahandler(Get_Handler(hdlr)arg)):indvar
from book;
exec sql begin;
process row....
exec sql end;
...
end.
Dynamic SQL Handler
The following is an example of a dynamic SQL handler program:
program dynamic_hdlr(input,output):
exec sql include sqlca;
exec sql include sqlda;
-- Do not declare the data handlers nor the data handler argument
-- to the ESQL preprocessor
type
String100 = packed array [1..100] of char;
hdlr_rec = record
argstr: String100;
argint: Integer;
endr;
var
function Put_Handler(hdlr_arg: hdrlr_rec):
integer;external;
function Get_Handler(hdlr_arg: hdlr_rec):
integer;external;
hdlr_rec: hdlr_arg;
-- Declare SQLDA and IISQLHDLR
sqlda: IIsqlda;
data_handler: IIsqlhdlr;
base_type: integer;
col_num: integer;
-- Declare null indicator to ESQL
exec sql begin declare section;
ind_var: integer;
stmt_buf: String100;
exec sql end declare section;
. .
begin
-- Set the IISQLHDLR structure with the appropriate datahandler and
-- datahandler argument.
data_handler.sqlhdlr = iaddress(Get_Handler)
data_handler.sqlarg = iaddress(hdlr)arg)
-- Describe the statment into the SQLDA.
stmt_buf = 'select * from book'.
exec sql prepare stmt from :stmt_buf;
exec sql describe stmt into SQLDA;
. . .
-- Determine the base_type of the SQLDATA variables.
col_num := 1;
while (col_num <= sqlda.sqld) do begin
with sqlda.sqlvar[col_num] do begin
if (sqltype > 0) then
base_type := sqltype;
else
base_type := -sqltype;
-- Set the sqltype, sqldata and sqlind for each column.
-- The Long Varchar Column chapter_text will be set to use a datahandler.
if (base_type = IISQ_LVCH_TYPE) the
sqltype = IISQ_HDLR_TYPE;
sqldata = iaddress(data_handler_;
sqlind = iaddress(indvar);
else
. . .
end;
end;
-- 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.
...
exec sql execute immediate :stmt_buf using :SQLDA
exec sql begin
process row...
exec sql end;
...
end.
Last modified date: 01/30/2023