|
|
|
To read and write long varchar and long byte columns (referred to as large objects), create routines called data handlers. Data handlers use get data and put data statements to read and write segments of large object data. To invoke a data handler, specify the datahandler clause in an insert, update, fetch, or select statement. When the query is executed, the data handler routine is invoked to read or write the column.
In embedded SQL programs, use the datahandler clause in place of a variable or expression. For example, you can specify a data handler in a where clause; the syntax of the datahandler clause is as follows:
datahandler(handler_routine([handler_arg]))[:indicator_var]
The following table lists the parameters for the datahandler clause:
Parameter |
Description |
|---|---|
handler_routine |
Pointer to the data handler routine. Must be a valid pointer. An invalid pointer results in a runtime error. |
handler_arg |
Optional pointer to an argument to be passed to the data handler routine. The argument does not have to be declared in the declare section of the program. |
indicator_var |
Optional indicator variable; for details about indicator variables, see Indicator Variables. For datahandler clauses in insert and update statements and where clauses, if this variable is set to a negative value, the data handler routine is not called. If the data returned by a select or fetch statement is null, the indicator variable is set to -1 and the data handler routine is not called. |
For example, the following select statement returns the column, bookname, using the normal SQL method and the long varchar column, booktext, using a data handler:
exec sql select bookname, booktext into
:booknamevar, datahandler(get_text())
from booktable where bookauthor = 'Melville';
Separate data handler routines can be created to process different columns.
In select loops, data handlers are called once for each row returned.
|
|
|
Errors from put data and get data statements are raised immediately, and abort the SQL statement that invoked the data handler. If an error handler is in effect (as the result of a set_sql(errorhandler) statement), the error handling routine is called.
The data handler read routines (routines that issue get data statements) must issue the enddata statement before exiting. If a data handler routine attempts to exit without issuing the enddata statement, a runtime error is issued.
To determine the name of the column for which the data handler was invoked, use the inquire_sql(columnname) statement. To determine the data type of the column, use the inquire_sql(columntype) statement. The inquire_sql(columntype) statement returns an integer code corresponding to the column data type. For a table listing the data type codes, see the table titled Data Type Codes. These inquire_sql statements are valid only within a data handler routine. Outside of a data handler, these statements return empty strings.
|
|
|
Data handlers are subject to the following restrictions:
|
|
|
The following sections contain considerations and restrictions for using large object data in dynamic SQL programs. For details about dynamic SQL, see Dynamic Programming.
|
|
|
The sqllen field of the SQLDA is a 2-byte integer in which the DBMS Server returns the length of a column. If a long varchar or long byte column that is longer than the maximum value possible for sqllen (32,768) is described, a 0 is returned in sqllen.
Long varchar and long byte columns can contain a maximum of two gigabytes of data. To prevent data truncation, be sure that the receiving variable to which the SQLDA sqldata field points is large enough to accommodate the data in the large object columns your program is reading. If data is truncated to fit in the receiving variable, the sqlwarn member of the sqlca structure is set to indicate truncation.
|
|
|
To specify a data handler routine to be called by a dynamic query that reads or writes a large object column, prepare the SQLDA fields for the large object column as follows:
If the value of the large object column is null (sqlind field of the SQLDA set to -1) the data handler is not invoked.
|
|
|
The following example illustrates the use of the put data statement; the data handler routine writes a chapter from a text file to the book table. The data handler is called when the insert statement is executed on a table with the following structure.
exec sql create table book
(chapter_name char(50),
chapter_text long varchar);
For example:
exec sql begin declare section;
char chapter_namebuf(50);
exec sql end declare section;
int put_handler();/* not necessary to
declare to embedded SQL */
...
copy chapter text into chapter_namebuf
exec sql insert into book
(chapter_name, chapter_text)
values (:chapter_namebuf,
datahandler(put_handler()));
...
put_handler()
exec sql begin declare section;
char chap_segment[3000];
int chap_length;
int segment_length;
int error;
exec sql end declare section;
int local_count = 0;
...
exec sql whenever sqlerror goto err;
chap_length = byte count of file
open file for reading
loop while (local_count < chap_length)
read segment from file into chap_segment
segment_length = number of bytes read
exec sql put data
(segment = :chap_segment,
segmentlength = :segment_length)
local_count = local_count + segment_length
end loop
exec sql put data (dataend = 1); /* required by embedded SQL */
...
err:
exec sql inquire_sql(:error = errorno);
if (error <> 0)
print error
close file
|
|
|
The following example illustrates the use of the get data statement in a data handler. This routine retrieves a chapter titled, "One Dark and Stormy Night," from the book table which has the following structure.
exec sql create table book
(chapter_name char(50),
chapter_text long varchar);
The data handler routine is called when the select statement is executed:
exec sql begin declare section;
char chapter_namebuf(50);
exec sql end declare section;
int get_handler()
...
Copy the string "One Dark and Stormy Night" into the chapter_namebuf variable.
exec sql select chapter_name, chapter_text
into :chapter_namebuf, datahandler(get_handler())
from book where chapter_name = :chapter_namebuf
exec sql begin
/* get_handler will be invoked
once for each row */
exec sql end;
...
get_handler()
exec sql begin declare section;
char chap_segment[1000];
int segment_length;
int data_end;
int error;
exec sql end declare section;
...
exec sql whenever sqlerror goto err;
data_end = 0
open file for writing
/* retrieve 1000 bytes at a time and write to text file. on last segment, less than 1000 bytes may be returned, so segment_length is used
for actual number of bytes to write to file. */
while (data_end != 1)
exec sql get data (:chap_segment = segment,
:segment_length = segmentlength,
:data_end = dataend)
with maxlength = 1000;
write segment_length number of bytes from
"chap_segment" to text file
end while
...
err:
exec sql inquire_ingres(:error = errorno);
if (error != 0)
print error
close file
|
|
|
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;
}