Example: Dynamic SQL Data Handler
The following example illustrates the use of data handlers in a dynamic SQL program. The sample table, big_table, was created with the following CREATE TABLE statement.
create table big_table
(object_id integer, big_col long varchar);
The dynamic program retrieves data from big_table.
The data handler routine, userdatahandler, accepts a structure composed of a (long varchar) character string and an integer (which represents an object ID). The data handler writes the object ID followed by the text of the large object to a file.
The logic for the data handler is shown in the following pseudocode:
userdatahandler(info)
hdlr_param pointer to info structure
{exec sql begin declare section;
char segbuf[1000];
int seglen;
int data_end;
exec sql end declare section;
data_end = 0
open file for writing
set arg_str field of info structure to filename
/* to pass back to main program */
write arg_int field to file /* id passed in
from main program */
loop while (data_end != 1)
exec sql get data
(:segbuf = segment, :dataend = dFataend)
with maxlength = 1000;
write segment to file
end loop
close file
}
The structures required for using data handlers in dynamic SQL programs are declared in the eqsqlda.h source file, which is included in your program by an INCLUDE SQLDA statement. The following (C-style) definitions pertain to the use of data handlers:
# define IISQ_LVCH_TYPE 22
# define IISQ_HDLR_TYPE 46
typedef struct sqlhdlr_
{
char *sqlarg;
int (*sqlhdlr)();
} IISQLHDLR;
The following definitions must be provided by the application program. In this example the header file, mydecls.h, contains the required definitions.
/* Define structure hdlr_param, which will be used to pass information to and receive information from the data handler. The data handler argument is a pointer to a structure of this type, which is declared in the main program.*/
typedef struct hdlr_arg_struct
{
char arg_str[100];
int arg_int;
} hdlr_param;
The following code illustrates the main program, which uses dynamic SQL to read the long varchar data from the sample table. This sample program sets up the SQLDA to handle the retrieval of two columns, one integer column and one long varchar column. The long varchar column is processed using a user-defined data handler.
exec sql include 'mydecls.h';
main()
{
/* declare the sqlda */
exec sql include sqlda;
declare host SQLDA: _sqlda
declare sqlda as pointer to host SQLDA _sqlda
exec sql begin declare section;
character stmt_buf[100];
short integer indicator1;
short integer indicator2;
exec sql end declare section;
integer userdatahandler()
integer i
/* Set the iisqhdlr structure; the data handler "userdatahandler" is invoked with a pointer to "hdlr_arg" */
iisqlhdlr data_handler;
/* Declare parameter to be passed to datahandler -- in this example a pointer to a hdlr_param -- a struct with one character string field and one integer field as defined in "mydecls.h". */
declare hdlr_param hdlr_arg
set the SQLDA’s sqln field to 2
copy "select object_id,big_col from big_table2" to the host language variable stmt_buf
i = 0
exec sql connect 'mydatabase';
set the sqlhdlr field to point to the userdatahandler routine
set the sqlarg field to point to arguments (hdlr_arg)
/* Set the first sqlvar structure to retrieve column "object_id".Because this column appears before the large object column in the target list, it IS retrieved prior to the large object column, and can be put into the hdlr_arg that is passed to the data handler. */
sqlvar[0].sqltype = IISQ_INT_TYPE
sqlvar[0].sqldata points to hdlr_arg.arg_int
sqlvar[0].sqlind points to indicator1
/* Set the second sqlvar structure to invoke a datahandler.the "sqltype" field must be set to iisq_hdlr_type.the "sqldata" field must be pointer to iisqlhdlr type. */
sqlvar[1].sqltype = IISQ_HDLR_TYPE
sqlvar[1].sqldata points to data_handler
sqlvar[1].sqlind points to indicator2
/* The data handler is called when the large object is retrieved. The data handler writes the object_id and large object to a file and returns the file name to the main program in the hdlr_arg struct. */
exec sql execute immediate :stmt_buf
using descriptor sqlda;
exec sql begin;
/* process the file created in the data handler */
call processfile(hdlr_arg)
exec sql end;
}