Sample Programs
The programs in this section are examples of how to declare and use user-defined datahandlers in an ESQL/Ada program. There are examples of a handler program, a put handler program, a get handler program and a dynamic SQL handler program.
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;
Put Handler
This example shows how to read the long varchar chapter_text from a text file and insert it into the database a segment at a time:
function Put_Handler(info: Hdlr_Rec) return Integer is
exec sql begin declare section;
seg_buf: String(1..1000);
seg_len: Integer;
data_end: Integer;
exec sql end declare section;
process information passed in via the info
record...
open file ...
data_end := 0;
while (not end-of-file) loop
read segment of less than 1000 chars from
file into seg_buf...
if (end-of-file) then
data_end := 1;
end if;
exec sql put data (segment = :seg_buf,
segmentlength = :seg_len,
dataend = :data_end);
end loop;
...
close file ...
set info record to return appropriate values...
...
return 0;
end Put_Handler;
Get Handler
This example shows how to get the long varchar chapter_text from the database and write it to a text file:
function Get_Handler(info: Hdlr_Rec) return Integer is
exec sql begin declare section;
seg_buf: String(1..100);
seg_len: Integer;
data_end: Integer;
max_len: Integer;
exec sql end declare section;
...
process information passed in via the
info record....
open file...
-- Set a maximum segment length of 2000 bytes
data_end := 0;
while (data_end = 0) loop
exec sql get data (:seg_buf = segment,
:seg_len = segmentlength,
:data_end = dataend)
with maxlength = :max_len;
write segment to file ...
end loop;
. . .
set info record to return appropriate values...
. . .
return 0;
end Get_Handler;
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_PARAM structure described in the previous example:
with DataHdlrPkg; use DataHdlrPkg;
procedure Dynamic_hdlr
exec sql include sqlca;
exec sql include sqlda;
-- Do not declare the datahandlers nor the datahandler
-- argument to the ESQL preprocessor.
-- Declare argument to be passed to datahandler
hdlr_arg: Hdlr_Rec;
-- Declare SQLDA and IISQLHDLR
sqlda: IISQLDA(IISQ_MAX_COLS);
data_handler: IISQLHDLR;
col_num: Integer;
base_type: Integer;
-- Declare null indicator to ESQL
exec sql begin declare section;
indvar: Short_Integer;
stmt_buf: String(100);
exec sql end declare section;
...
-- Set the IISQLHDLR structure with the appropriate
-- datahandler and datahandler argument.
data_handler.sqlhdlr = Get_Handler'Address;
data_handler.sqlarg = hdlr_arg'Address;
-- Describe the statement 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.
for col_num in 1..sqlda.sqld loop
if (sqlda.sqlvar(col_num).sqltype > 0) then
base_type := sqlda.sqlvar(col_num).sqltype;
else
base_type := -sqlda.sqlvar(col_num).sqltype;
end if;
-- 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) then
sqlda.sqlvar(col_num).sqltype := IISQ_HDLR_TYPE;
sqlda.sqlvar(col_num).sqldata :=
data_handler'Address;
sqlda.sqlvar(col_num).sqlind := indvar'Address'
else
. . .
end if;
end loop;
-- 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 Dynamic_hdlr;