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/Pascal program. There are examples of 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.
program handler(input,output);
    exec sql include sqlca

-- Do not declare the data handlers nor the data handler argument
-- to the ESQL preprocessor

type

    String100 = packed array [1..100] of char;
        hdlr_rec = record
            argstr: String100;
            argint: Integer;

        end;

var
        hdlr_rec: hdlr_arg;

        exec sql begin declare section;
            indvar;      II_int2;
            seg_buf;     packed array [1...1000] of char;
            seg_len;     integer;
            data_end;    integer;
            max_len;     integer;
        exec sql end declare section;
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.
function Put_Handler(info: hdlr_rec) : Integer;

begin
        process information passed in via the info record...
        open file ...

data_end := 0;

        while (not end-of-file) do begin

                read segment from file into seg_buf...

            if (end-of-file) then begin
                data_end := 1;
            end;

            exec sql put data (segment = :seg_buf,
                    segmentlength = :seg_len,
                     dataend = :data_end);

end; {while}
. . .
close file...
set info record to return appropriate values...
....
Put_Handler := 0 {return value ignored}

end {Put Handler }
Get Handler
This user defined datahandler shows how an application can use the get data handler to enter a chapter of a book from a text file into a database.
function Get_Handler(info: hdlr_rec) :Integer;
begin
    ...
    process information passed in via the info record...
    open file ....
data_end := 0;
while (data_end = 0) do
begin
    exec sql get data (:seg_buf=segment,
            :seg_len = segmentlength,
             :data_end = dataend)
         with maxlength = :max_len;
        write segment to file...
end;
. . .
set info record to return appropriate values...
...
            Get_Handler := 0; {return value ignored }
    end;
begin
-- INSERT a long varchar value chapter_text into
-- the table book using the datahandler Put_Handler
-- The argument passed to the datahandler the record
-- hdlr_arg.
--
                ...
        ...
        exec sql insert into book (chapter_num, chapter_name, chapter_text)
                values (5, 'One Dark and Stormy Night',
                datahandler(Put_Handler(hdlr_arg)));
        ...

-- Select the long varchar column chapter_text from the table book.
-- The Datahandler (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 datahandler will not be called.
        ...
            ...
            exec sql select chapter_text into
                 datahandler(Get_Handler(hdlr)arg)):indvar
                 from book;
            exec sql begin;
                 process row....
            exec sql end;
            ...
end.
Dynamic SQL Handler
The following is an example of a dynamic SQL handler program:
program dynamic_hdlr(input,output):

        exec sql include sqlca;
        exec sql include sqlda;

-- Do not declare the data handlers nor the data handler argument
-- to the ESQL preprocessor

type
         String100 = packed array [1..100] of char;
         hdlr_rec = record
                argstr: String100;
                argint: Integer;
            endr;

var
            function Put_Handler(hdlr_arg: hdrlr_rec):
                     integer;external;
            function Get_Handler(hdlr_arg: hdlr_rec):
                    integer;external;
            hdlr_rec:     hdlr_arg;

-- Declare SQLDA and IISQLHDLR
         sqlda:        IIsqlda;
         data_handler:    IIsqlhdlr;
         base_type:    integer;
         col_num:    integer;

-- Declare null indicator to ESQL
        exec sql begin declare section;
            ind_var:    integer;
            stmt_buf:    String100;
        exec sql end declare section;

        .    .

begin

-- Set the IISQLHDLR structure with the appropriate datahandler and
-- datahandler argument.

    data_handler.sqlhdlr = iaddress(Get_Handler)
    data_handler.sqlarg = iaddress(hdlr)arg)

-- 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.

    col_num := 1;
    while (col_num <= sqlda.sqld) do begin
        with sqlda.sqlvar[col_num] do begin
                if (sqltype > 0) then
                    base_type := sqltype;
                else
                    base_type := -sqltype;

-- Set the sqltype, sqldata and sqlind for each column.
-- The Long Varchar Column chapter_text will be set to use a datahandler.

        if (base_type = IISQ_LVCH_TYPE) the
            sqltype   = IISQ_HDLR_TYPE;
            sqldata   = iaddress(data_handler_;
            sqlind    = iaddress(indvar);
        else
        . . .
     end;

    end;

-- The Datahandler (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 datahandler will not be called.

    ...

    exec sql execute immediate :stmt_buf using :SQLDA
    exec sql begin
         process row...
    
exec sql end;
    ...

end.
Last modified date: 01/30/2023