Was this helpful?
Sample Programs
The programs in this section are examples of how to declare and use user-defined datahandlers in an ESQL/Ada 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 in segments. 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:
package DataHdlrPkg is
    type Hdlr_Rec is
    record
      argstr:   String(1..100);
      argint:   Integer;  -- 4-byte integers
    end record;

    function Put_Handler(info: Hdlr_Rec) return Integer;
    function Get_Handler(Info: Hdlr_Rec) return Integer;
    pragma export_function(Put_Handler);
    pragma export_function(Get_Handler);

end DataHdlrPkg;

with DataHdlrPkg;      use DataHdlrPkg;

procedure handler is
    exec sql include sqlca;

-- Do not declare the datahandlers nor the datahandler
-- argument to the ESQL preprocessor
    hdlr_arg: Hdlr_Rec;

-- Null indicator for datahandler must be declared to
-- ESQL

--
    exec sql begin declare section;
      indvar:     Short_integer;
      chapter_num: Integer;
    exec sql end declare section;

-- Insert a long varchar value chapter_text into the
-- table book using the datahandler Put_Handler
-- The argument passed to the datahandler is the
-- address of 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 column chapter_num and the long varchar
-- column chapter_text from the table book.
-- The datahandler (Get_Handler) will be invoked
-- for each non-null value of the 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
        :chapter_num,
        datahandler (Get_Handler(hdlr_arg)) :indvar
        from book;

    exec sql begin;
        process row ...
    exec sql end;
    ...

end handler;
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:
function Put_Handler(info: Hdlr_Rec) return Integer is
    exec sql begin declare section;
            seg_buf:   String(1..1000);
            seg_len:   Integer;
            data_end:  Integer;
    exec sql end declare section;

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

    data_end := 0;

    while (not end-of-file) loop
            read segment of less than 1000 chars from
           file into seg_buf...

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

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

    end loop;

    ...
    close file ...
    set info record to return appropriate values...
    ...
    return 0;
end Put_Handler;
Get Handler
This example shows how to get the long varchar chapter_text from the database and write it to a text file:
function Get_Handler(info: Hdlr_Rec) return Integer is
    exec sql begin declare section;
            seg_buf:      String(1..100);
            seg_len:      Integer;
            data_end:     Integer;
            max_len:      Integer;
    exec sql end declare section;

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

  -- Set a maximum segment length of 2000 bytes
  data_end := 0;

    while (data_end = 0) loop
            exec sql get data (:seg_buf = segment,
                          :seg_len = segmentlength,
                           :data_end = dataend)
                         with maxlength = :max_len;

        write segment to file ...
    end loop;

      . . .
      set info record to return appropriate values...

      . . .

      return 0;
end Get_Handler;
Dynamic SQL Handler
The following is an example of a dynamic SQL handler program. 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:
with DataHdlrPkg;      use DataHdlrPkg;

procedure Dynamic_hdlr
        exec sql include sqlca;
        exec sql include sqlda;

-- Do not declare the datahandlers nor the datahandler
-- argument to the ESQL preprocessor.

-- Declare argument to be passed to datahandler
        hdlr_arg:     Hdlr_Rec;

-- Declare SQLDA and IISQLHDLR
    
     sqlda:   IISQLDA(IISQ_MAX_COLS);
     data_handler: IISQLHDLR;

        col_num:      Integer;
        base_type:    Integer;

-- Declare null indicator to ESQL
        exec sql begin declare section;
              indvar:   Short_Integer;
                stmt_buf: String(100);
        exec sql end declare section;

        ...

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

        data_handler.sqlhdlr = Get_Handler'Address;
        data_handler.sqlarg  = hdlr_arg'Address;

-- Describe the statement 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.
        for col_num in 1..sqlda.sqld loop
            if (sqlda.sqlvar(col_num).sqltype > 0) then
                base_type := sqlda.sqlvar(col_num).sqltype;

            else
                base_type := -sqlda.sqlvar(col_num).sqltype;
            end if;

-- 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) then
                 sqlda.sqlvar(col_num).sqltype := IISQ_HDLR_TYPE;
                 sqlda.sqlvar(col_num).sqldata :=
                            data_handler'Address;
                 sqlda.sqlvar(col_num).sqlind  := indvar'Address'
            else
            . . .

            end if;

        end loop;

-- 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 Dynamic_hdlr;
Last modified date: 11/09/2022