Sample Programs
The programs in this section are examples of how to declare and use user-defined data handlers in an ESQL/BASIC program. Examples include 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.
!main program
!*****************
program handler
exec sql include sqlca
! Do not declare the data handlers nor the data handler
! argument to the ESQL preprocessor
external integer Put_Handler
external integer Get_Handler
record hdlr_arg
string argstr
integer argint
end record hdlr_arg
declare hdlr_arg hdlarg
! Null indicator for data handler must be declared to ESQL
exec sql begin declare section
word indvar
exec sql end declare section
! INSERT a long varchar value chapter_text into the
! table book using the data handler put_handler. The
! argument passed to the data handler the record hdlarg.
. . .
exec sql insert into book (chapter_name, chapter_text) &
values (5, 'One Dark and Stormy Night',
data handler(Put_Handler(hdlarg)))
! SELECT the long varchar column chapter_text from
! The data handler (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 data handler will not be called.
...
exec sql select chapter)text into &
data handler(get_handler(hdlarg)):indvar from book
exec sql begin
process row...
exec sql end
...
end program
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.
! Put_handler
! ************
100 function integer Put_handler(hdlr_arg info)
record hdlr_arg
string argstr
integer argint
end record hdlr_arg
exec sql begin declare section;
declare sting segbuf
declare integer seglen
declare integer datend
exec sql end declare sections
process information passed in via the info record
open file.....
datend = 0
while not end-of-file
read segment from file into segbuf...
if (end-of-file) then
datend = 1
end if
exec sql put data (segment = :segbuf, &
segmentlength = :seglen, dataend = :datend)
next
...
close file...
set info record to return appropriate values...
..
Put_handler = 0
end function
Get Handler
This user-defined data handler shows how an application can use the get data handler to enter a chapter of a book from a text file into a database.
! Get_Handler
! *************
200 integer function Get_Handler(hdlr_arg info)
record hdlr_arg
string argstr
integer argint
end record hdlr_arg
exec sql begin declare section
declare string segbuf
declare integer seglen
declare integer datend
declare integer maxlen
exec sql end declare section
...
process information passed in via the
info record...
open file....
datend = 0
while (datend = 0)
exec sql get data (:segbuf = segment,&
:seglen = segmentlenght, & :datend = dataend) &
with maxlength = :maxlen
write segment to file
next
...
set info record to return appropriate values...
...
Get_Handler = 0
end function
Dynamic SQL Handler
The following is an example of a dynamic SQL handler program:
! main program using SQLDA
! ***************************
program dynamic_hdlr
exec sql include sqlca
exec sql include sqlda
! Do not declare the data handlers nor the data handler
! argument to the ESQL preprocessor
external integerPut_Handler
external integerGet_Handler
! Declare argument to be passed to data handler
record hdlr_arg
string argstr
integer argint
end record hdlr_arg
! Declare SQLDA and IISQLHDLR
common (sqlda_area) IISQLDA sqlda
common (result_area) num_store nums(IISQ_MAX_COLS), &
char_store chars
declare IISQLHDLR data_handler
declare hdlr_arg hdlarg
declare integer base_type
! Declare null indicator to ESQL
exec sql begin declare section
word indvar
string (100) stmt_buf
integer i
exec sql end declare section
...
! Set the IISQLHDLR structure with the appropriate
! data handler and data handler argument.
data_handler::sqlhdlr = loc(Get_Handler)
data_handler::sqlarg = loc(hdlarg)
! 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
while ( i < sqlda::sqld)
i = i + 1
if (sqlda::sqlvar(i)::sqltype > 0) then
base_type = sqlda::sqlvar(I)::sqltype
else
base_type = -sqlda::sqlvar(i)::sqltype
end if
! Set the sqltype, sqldata and sqlind for each column
! The long varchar column chapter_text will be set to
! use a data handler
if (base_type = IISQ_LVCH_TYPE) then
sqlda::sqlvar(i)::sqltype = IISQ_HDLR_TYPE
sqlda::sqlvar(i)::sqldata = loc(data_handler)
sqlda::sqlvar(i)::sqlind = loc(indvar)
else
. . .
end if
next
! The Data handler (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 data handler will not be called
...
exec sql execute immediate :stmt_buf using :SQLDA
exec sql begin
process row...
exec sql end
...
end program