6. Embedded SQL for BASIC : Advanced Processing : Sample Programs : Dynamic SQL Handler
 
Share this page                  
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