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;