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/C program. Program examples include a handler, a put handler, a get handler and a dynamic SQL handler.
If you precompile these examples using the prototypes flag (for ANSI C style function declarations), you must declare the functions using a generic pointer argument. For example:
int Put_Handler(void *hdlr_arg)
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.
/*
** For this example the argument to the datahandlers
** will be a pointer to a HDLR_PARAM structure.
*/
 typedef struct hdlr_arg_struct
{
     char *arg_str;
     int arg_int;
 } HDLR_PARAM;
main()
{
/* Do not declare the datahandlers or the datahandler argument to the ESQL
** preprocessor. The argument passed to a datahandler must be a pointer.
*/
         int Put_Handler();
         int Get_Handler();
    HDLR_PARAM hdlr_arg;
    /*
    ** The indicator variable must be declared to ESQL.
    */
         exec sql begin declare section;
                 short indvar;
                 int chapter_num;
         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 structure 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_num, chapter_text into
   :chapter_num,
      datahandler(Get_Handler(&hdlr_arg)):indvar
           from book;
 exec sql begin;
      process row...
 exec sql end;
    . . .
}
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.
Int
Put_Handler(hdlr_arg)
 HDLR_PARAM *hdlr_arg;
 {
    /*
    ** Host variables in the put data statement must
    ** be declared to the ESQL preprocessor
    */
    exec sql begin declare section;
        char seg_buf[1000];
        int seg_len;
        int data_end;
    exec sql declare section;
    int more_data;
    open file...
    data_end = 0;
    more_data = 1;
    while (more_data == 1)
    {
    read segment of less than 1000 chars from
    file into seg_buf...
    if (end_of_file)
    {
        data_end = 1;
        more_data = 0;
     }
    seg_len = number_of_bytes_read;
    exec sql put data (segment = :seg_buf,
                        segmentlength = :seg_len,
                        dataend = :data_end);
    };
    . .
    close file...
    set hdlr_arg fields to return appropriate
        values...
    . . .
 }
Get Handler
This example shows how to get the long varchar chapter_text from the database and write it to a text file.
Get_Handler(hdlr_arg)
 HDLR_PARAM *hdlr_arg;
 {
    /* Host variables in the get data statement must
    ** be declared to the ESQL preprocessor
    */
    exec sql begin declare section;
        char seg_buf[2000];
        int seg_len;
        int data_end;
        int max_len;
    exec sql end declare section;
    . . .
    process information passed in via the
         hdlr_arg...
    open file...
    /* Get a maximum segment length of 2000 bytes. */
    max_len = 2000;
    data_end = 0;
    while (data_end == 0)
    {
    /*
    ** segmentlength: will contain the length of the
    ** segment retrieved
    ** seg_buf: will contain a segment of the column
    ** chapter_text
    ** data_end: will be set to '1' when the entire
    ** value in chapter_text has been retrieved
    */
    exec sql get data (:seg_buf = segment,
                       :seg_len = segmentlength,
                       :data_end = dataend)
                        with maxlength = :max_len;
    write segment to a file...
    }
    . . .
    set hdlr_arg fields to return appropriate
         values...
    . .
}
Dynamic SQL Handler
This example shows how to declare and use a data handler in a dynamic SQL program, using the SQLDA. It uses the data handler Get_Handler() and the HDLR_PARAM structure described in the previous example.
main()
{
    exec sql include sqlda;
    /* Declare the SQLDA and IISQLHDLR structures */
    IISQLDA _sqlda;
    IISQLDA *sqlda = &_sqlda;
    IISQLHDLR datahdlr_struct;
    /*
    ** Do not declare the datahandlers or the
    ** datahandler argument to the ESQL preprocessor
    */
    int Get_Handler();
    HDLR_PARAM hdlr_arg;
    int base_type;
    int col_num;
    /* Declare null indicator to ESQL */
    exec sql begin declare section;
        short indvar;
        char stmt_buf[100];
    exec sql end declare section;
    . . .
    /*
    ** Set the IISQLHDLR structure with the appropriate
    ** datahandler and datahandler argument.
    */
    datahdlr_struct.sqlarg = &hdlr_arg;
    datahdlr_struct.sqlhdlr = Get_Handler;
    sqlda->sqln = IISQ_MAX_COLS;
    /* Describe the statement into the SQLDA */
    strcpy(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 = 0; col_num < sqlda->sqln;
         col_num++)
    {
        base_type = abs(sqlda-
            >sqlvar[col_num].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)
        {
           sqlda->sqlvar[col_num].sqltype =-IISQ_HDLR_TYPE
           sqlda->sqlvar[col_num].sqldata =
                           (char *)&datahdlr_struct;
           sqlda->sqlvar[col_num].sqlind = &indvar;
        }
        Else
           . . .
    }
    /*
    ** 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 rows...
    exec sql end;
    . . .
Last modified date: 11/09/2022