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