Pass-Through TPF in tpf_blob

The TPF sample tpf_blob.cxx demonstrates advanced UDF LOB and CLOB handling. The example is available in the samples directory. This examination of tpf_blob illustrates concepts not already covered by the simpler examples in tpf_rg_1 and tpf_rg_2; only the relevant portions are discussed.

A table UDF or TPF can not produce LOB or CLOB data. However, using a concept known as pass-through, LOB or CLOB data can be passed from an input table to an output table. In fact, any data type can be passed through from an input table to the result set. This allows a TPF to filter rows, meaning that the output is a subset of the input table rows.

CREATE PROCEDURE tpf_blob( IN tab TABLE( num INT, s [LONG] <VARCHAR | BINARY >,
                           IN pattern char(1) )
RESULT SET ( num INT, s [LONG] <VARCHAR | BINARY > )
EXTERNAL NAME ‘tpf_blob@libv4apiex’

The procedure supports multiple schemas. The data types for column s in the result set and input table can be one of VARCHAR, BINARY, LONG VARCHAR, or LONG BINARY.

• Dynamic Schema Support
  The schema for the tpf_blob procedure is dynamic.
• Processing LOB and CLOB Columns in Input Tables
  A tpf_blob example counting the number of occurrences of a pattern in each data row in the input table.
• Passing Input Table Columns to the Result Set
  A tpf_blob illustrating how a TPF can pass the rows from an input table to the result table, and how to use the row_status flag to indicate if a row is present.
• Running the Sample TPF in tpf_blob.cxx
  The sample tpf_blob is included in a precompiled dynamic library called libv4apiex (extension is platform-dependent). Its implementation is in the samples directory in tpf_blob.cxx.