5. Embedded OpenSQL : Data Handlers for Large Objects
 
Share this page                  
Data Handlers for Large Objects
To read and write columns that contain large objects (such as long varchar and long byte types), 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 (see page 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.
The data handler must issue a PUT DATA statement with DATAEND set to 1 before exiting; otherwise, the DBMS Server issues a runtime error. When sending empty LOB data, for example, a zero-length BLOB (LONG BYTE), the PUT DATA call must be made with the SEGMENT parameter so that the client informs the DBMS of the correct data type. If the SEGMENT parameter is omitted, the client will default to sending CLOB (LONG VARCHAR).