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.

When the procedure is defined as having LONG VARCHAR or LONG BINARY for column s, the data must be processed using the blob API. This code snippet from the fetch_into_extfn method illustrates how a TPF can use the blob API to process LOB and CLOB data from an input table:

if( EXTFN_COL_IS_BLOB(cd, 1) ) {
	ret = state->rs->get_blob( state->rs, &cd[1], &blob );

	UDF_SQLERROR_RT( tctx->proc_context,
			 "Failed to get blob",
			 (ret == 1 && blob != NULL),
			 0 );

	if( state->data_type == DT_VARCHAR || 
	    state->data_type == DT_LONGVARCHAR ) {

	    num = ProcessBlob( tctx->proc_context, blob, state->pattern );
	} else {
	    char i = (char)atoi( &(state->pattern) );
	    num = ProcessBlob( tctx->proc_context, blob, i );
	}
	ret = blob->release( blob );
	UDF_SQLERROR_RT( tctx->proc_context,
			 "Failed to release blob",
			 (ret == 1),
			 0 );
    } else {
	if( state->data_type == DT_VARCHAR || 
	    state->data_type == DT_LONGVARCHAR ) {
	    num = CountNum( (char *)cd[1].data,
*(cd[1].piece_len),
state>pattern );
	} else {
	    char i = (char)atoi( &(state->pattern) );
	    num = CountNum( (char *)cd[1].data, *(cd[1].piece_len), i );
	}
    }

For each of the rows in the input table, the TPF checks if it is a blob using the macro EXTFN_COL_IS_BLOB. If it is a blob, then the TPF uses the get_blob method of a_v4_extfn_table_context to create a blob object for the specified column. On success, the get_blob method provides the TPF with an instance of a_v4_extfn_blob, which allows the TPF to read in the blob data. Once the TPF is finished with the blob, it should call release on it.

The ProcessBlob method illustrates how a blob object processes the data:

static a_sql_uint64 ProcessBlob(
    a_v4_extfn_proc_context *ctx,
    a_v4_extfn_blob *blob,
    char pattern)
/*******************************/
{
    char buffer[BLOB_ISTREAM_BUFFER_LEN];
    size_t len = 0;
    short ret = 0;

    a_sql_uint64 num = 0;

    a_v4_extfn_blob_istream *is = NULL;

    ret = blob->open_istream( blob, &is );
    UDF_SQLERROR_RT( ctx,
		     "Failed to open blob istream",
		     (ret == 1 && is != NULL),
		     0 );

    for(;;) {
	len = is->get( is, buffer, BLOB_ISTREAM_BUFFER_LEN );
	if( len == 0 ) {
	    break;
	}
	num += CountNum( buffer, len, pattern );
    }

    ret = blob->close_istream( blob, is );
    UDF_SQLERROR_RT( ctx,
		     "Failed to close blob istream",
		     (ret == 1),
		     0 );
    return num;
}

The open_istream method on the blob object creates an instance of a_v4_extfn_blob_istream, which can then be used to read a specified amount of the blob into a buffer using the get method.