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
