Was this helpful?
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
Last modified date: 11/28/2023