Sample Programs
The programs in this section are examples of how to declare and use user-defined datahandlers in an ESQL/Fortran 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. 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.
C main program
C ***************
program handler
exec sql include sqlca
C Do not declare the datahandlers nor the datahandler
C argument to the ESQL pre-processor.
external Put_Handler
integer Put_Handler
external Put_Handler
integer Get_Handler
structure /hdlr_arg/
character*1000 argstr
integer argint
end structure
record /hdlr_arg/hdlarg
C Null indicator for datahandler must be declared
C to ESQL
exec sql begin declare section
integer*2 indvar
integer*4 chapter_num
exec sql end declare section
C INSERT a long varchar value chapter_text into the table book
C using the datahandler Put_Handler.
C The argument passed to the datahandler is the address of
C the record hdlarg.
. . .
exec sql insert into book (chapter_num, chapter_name,
chapter_text)
1 values (5, 'One Dark and Stormy Night',
2 Datahandler(Put_Handler(hdlarg)))
. . .
C Select the column chapter_num and the long varchar column
C chapter_text from the table book.
C The Datahandler (Get_Handler) will be invoked for each non-null
C value of column chapter_text retrieved. For null values the
C indicator variable will be set to "-1" and the datahandler will
C not be called.
...
exec sql select chapter_num, chapter_text into
1 :chapter_num,
2 datahandler(Get_Handler(hdlarg)):indvar from book
exec sql begin
process row ...
exec sql end
. . .
end
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:
C Put_Handler
C ***********
integer function Put_Handler(info)
structure /hdlr_arg/
character*100 argstr
integer*4 argint
end structure
record /hdlr_arg/ info
exec sql begin declare section
character*1000 segbuf
integer*4 seglen
integer*4 datend
exec sql end declare section
process information passed in via the info record ...
open file ...
datend = 0
do while not end-of-file
read segment of less than 1000 characters from file into segbuf . . .
if end-of-file then
datend = 1
end if
exec sql put data (segment = :segbuf,
1 segmentlength = :seglen, dataend = :datend)
end do
. . .
close file ...
set info record to return appropriate values ...
. . .
Put_Handler = 0
end
Get Handler
This example shows how to get the long varchar chapter_text from the database and write it to a text file:
C Get_Handler
C ***********
integer function Get_Handler(info)
structure /hdlr_arg/
character*100 argstr
integer argint
end structure
record /hdlr_arg/ info
exec sql begin declare section
character*2000 segbuf
integer*4 seglen
integer*4 datend
integer*4 maxlen
exec sql end declare section
process information passed in via the info record ...
open file ...
C Get a maximum segment length of 2000 bytes
maxlen = 2000
datend = 0
do while (datend .eq. 0)
C segmentlength: will contain the length of the segment retrieved.
C seg_buf: will contain a segment of the column chapter_text
C data_end: will be set to '1' when the entire value in
C chapter_text has been retrieved.
exec sql get data (:seqbuf = segment, :seglen =
1 segmentlength, :datend = dataend)
2 with maxlength= :maxlen
write segment to file ...
end do
...
set info record to return appropriate values ...
...
Get_Handler = 0
end
Dynamic SQL Handler
The following examples are of a dynamic SQL handler program that uses the SQLDA. 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.
UNIX:
C main program using SQLDA
C *************************
program dynamic_hdlr
exec sql include sqlca
exec sql include sqlda
C Do not declare the datahandlers nor the datahandler argument
C to the ESQL pre-processor.
external Put_Handler
integer*4 Put_Handler
external Get_Handler
integer*4 Get_Handler
C Declare argument to be passed to datahandler.
structure /hdlr_arg/
character*100 argstr
integer*4 argint
end structure
record /hdlr_arg/ hdlarg
C Declare SQLDA and IISQLHDLR
record /IISQLDA/ sqlda
common /sqlda_area/sqlda
record /IISQLHDLR/ dathdlr
integer base_type
C Declare null indicator to ESQL
exec sql begin declare section
integer*2 indvar
Character*100 stmt_buf
exec sql end declare section
. . .
C Set the IISQLHDLR structure with the appropriate datahandler
C and datahandler argument.
dathdlr.sqlhdlr = loc(Get_Handler)
dathdlr.sqlarg = loc(hdlarg)
C Describe the statement into the SQLDA.
stmt_buf = 'select * from book'.
exec sql prepare stmt from :stmt_buf
exec sql describe stmt into SQLDA
. . .
C Determine the base_type of the sqldata variables.
do 20, i = 1, sqlda.sqld
if (sqlda.sqlvar(i).sqltype .gt. 0) then
base_type = sqlda.sqlvar(i).sqltype
else
base_type = -sqlda.sqlvar(i).sqltype
end if
C Set the sqltype, sqldata and sqlind for each column
C The long varchar column chapter_text will be set to use a
C datahandler
if (base_type .eq. IISQ_LVCH_TYPE) then
sqlda.sqlvar(i).sqltype = IISQ_HDLR_TYPE
sqlda.sqlvar(i).sqldata = loc(dathdlr)
sqlda.sqlvar(i).sqlind = loc(indvar)
else
. . .
end if
20 continue
C The Datahandler (Get_Handler) will be invoked for each non-null
C value of column chapter_text retrieved. For null values the
C indicator variable will be set to "-1" and the datahandler
C will not be called.
. . .
exec sql execute immediate :stmt_buf using :sqlda
exec sql begin
process row...
exec sql end
. . .
end
VMS:
C main program using SQLDA
C *************************
program dynhdl
exec sql include sqlca
exec sql include sqlda
C Do not declare the datahandlers nor the datahandler argument
C to the ESQL pre-processor.
external Put_Handler
integer*4 Put_Handler
external Get_Handler
integer*4 Get_Handler
C Declare argument to be passed to datahandler.
structure /hdlr_arg/
character*100 argstr
integer*4 argint
end structure
record /hdlr_arg/ hdlarg
C Declare SQLDA and IISQLHDLR
record /IISQLDA/ sqlda
common /sqlda_area/sqlda
record /IISQLHDLR/ dathdlr
integer base_type
C Declare null indicator to ESQL
exec sql begin declare section
integer*2 indvar
Character*100 stmt_buf
exec sql end declare section
. . .
C Set the IISQLHDLR structure with the appropriate datahandler and
C datahandler argument.
dathdlr.sqlhdlr = %loc(Get_Handler)
dathdlr.sqlarg = %loc(hdlarg)
C Describe the statement into the SQLDA.
stmt_buf = 'select * from book'.
exec sql prepare stmt from :stmt_buf
exec sql describe stmt into SQLDA
. . .
C Determine the base_type of the sqldata variables.
do 20, i = 1, sqlda.sqld
if (sqlda.sqlvar(i).sqltype .gt. 0) then
base_type = sqlda.sqlvar(i).sqltype
else
base_type = -sqlda.sqlvar(i).sqltype
end if
C Set the sqltype, sqldata and sqlind for each column
C The long varchar column chapter_text will be set to use a
C datahandler
if (base_type .eq. IISQ_LVCH_TYPE) then
sqlda.sqlvar(i).sqltype = IISQ_HDLR_TYPE
sqlda.sqlvar(i).sqldata = %loc(dathdlr)
sqlda.sqlvar(i).sqlind = %loc(indvar)
else
. . .
end if
20 continue
C The Datahandler (Get_Handler) will be invoked for each non-null
C value of column chapter_text retrieved. For null values the
C indicator variable will be set to "-1" and the datahandler
C will not be called.
. . .
exec sql execute immediate :stmt_buf using :sqlda
exec sql begin
process row...
exec sql end
. . .
end
Windows:
C main program using SQLDA
C *************************
program dynhdl
exec sql include sqlca
exec sql include sqlda
C Do not declare the datahandlers nor the datahandler argument
C to the ESQL pre-processor.
external Put_Handler
integer*4 Put_Handler
external Get_Handler
integer*4 Get_Handler
C Declare argument to be passed to datahandler.
structure /hdlr_arg/
character*100 argstr
integer*4 argint
end structure
record /hdlr_arg/ hdlarg
C Declare SQLDA and IISQLHDLR
record /IISQLDA/ sqlda
common /sqlda_area/sqlda
record /IISQLHDLR/ dathdlr
integer base_type
C Declare null indicator to ESQL
exec sql begin declare section
integer*2 indvar
Character*100 stmt_buf
exec sql end declare section
. . .
C Set the IISQLHDLR structure with the appropriate datahandler and
C datahandler argument.
dathdlr.sqlhdlr = %loc(Get_Handler)
dathdlr.sqlarg = %loc(hdlarg)
C Describe the statement into the SQLDA.
stmt_buf = 'select * from book'.
exec sql prepare stmt from :stmt_buf
exec sql describe stmt into SQLDA
. . .
C Determine the base_type of the sqldata variables.
do 20, i = 1, sqlda.sqld
if (sqlda.sqlvar(i).sqltype .gt. 0) then
base_type = sqlda.sqlvar(i).sqltype
else
base_type = -sqlda.sqlvar(i).sqltype
end if
C Set the sqltype, sqldata and sqlind for each column
C The long varchar column chapter_text will be set to use a
C datahandler
if (base_type .eq. IISQ_LVCH_TYPE) then
sqlda.sqlvar(i).sqltype = IISQ_HDLR_TYPE
sqlda.sqlvar(i).sqldata = %loc(dathdlr)
sqlda.sqlvar(i).sqlind = %loc(indvar)
else
. . .
end if
20 continue
C The Datahandler (Get_Handler) will be invoked for each non-null
C value of column chapter_text retrieved. For null values the
C indicator variable will be set to "-1" and the datahandler
C will not be called.
. . .
exec sql execute immediate :stmt_buf using :sqlda
exec sql begin
process row...
exec sql end
. . .
end