DB2 10.5 for Linux, UNIX, and Windows

StreamOutputLOBonCall CLI/ODBC configuration keyword

The StreamOutputLOBonCall keyword enables streaming of OUT parameters of stored procedures that are LOB or XML data type. You can also specify the StreamOutputLOBonCall keyword to obtain the data length information for the OUT parameters that are LOB and XML data type.

db2cli.ini keyword syntax:

StreamOutputLOBonCall = TRUE | FALSE

Default setting:

The streaming of OUT parameter data that are LOB or XML data type is disabled.

Equivalent environment or connection attribute:

SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL

Usage notes:

You must ensure that the following conditions are satisfied to get the actual data length information and stream the OUT parameter data that are LOB or XML data type:

Specify NULL value for the rgbValue argument of the SQLBindParameters() function for OUT parameters that are LOB and XML data type.
If application requires the data length information for OUT parameters that are LOB and XML data type, you must pass the valid memory address to an SQLINTEGER variable for the pcbValue argument of the SQLBindParameter() function. Successful CALL statement through use of the SQLExecute() function returns the length information in the SQLINTEGER variable.
All OUT parameter data from the stored procedure must be retrieved with the SQLGetData() function before you retrieve any result sets with the SQLFetch() function. Calling the SQLFetch() function causes the CLI driver to delete all the data buffers.
The LOB locators are not used.
The OUT parameters of stored procedures are not ARRAY form of LOB or XML data type.

The following example retrieves the length of the OUT parameter that is CLOB data type when the StreamOutputLOBonCall keyword is enabled:

#define C2_LEN (2 * 1024 * 1024 * 1024) // 2GB

SQLPrepare (hStmt, "CALL clob1(?, ?)", SQL_NTS);
...
// The rgbValue is set to NULL for the second SP argument. 
// Address to an SQLINTEGER variable is passed to hold the length details.
SQLBindParameter(hStmt, 2, SQL_PARAM_OUTPUT, 
                 SQL_C_CHAR, SQL_CLOB,
                 C2_LEN+1, 0, NULL, 0, &ind1);  

SQLExecute (hStmt);
// When the SQLExecute() function returns SQL_SUCCESS, the SQLINTEGER variable contains the data length.

// Following statement allocates ind1 bytes to store the CLOB data 
buffer = malloc (ind1 + 1);

// call SQLGetData with the same rgbValue value and the ind1 as the cbValueMax.
// cbValueMax to be ind1 to get entire argument data in one go 
SQLGetData(hStmt, 2, SQL_C_CHAR, buffer, ind1, &ind1);

Following example streams OUT parameter data that are LOB or XML data type in 1K bytes:

#define C3_LEN (2 * 1024 * 1024 * 1024) // 2GB

// Allocate fixed 1K bytes to store part of the CLOB data
buffer = malloc (1024);

SQLPrepare (hStmt, "CALL clob1(?, ?)", SQL_NTS);
...
// As CLOB data is retrieved in pieces, the data length information is not necessary.
// The pcbValue is NULL since data length information is not needed.
SQLBindParameter(hStmt, 2, SQL_PARAM_OUTPUT, 
                 SQL_C_CHAR, SQL_CLOB,
                 C3_LEN+1, 0, NULL, 0, NULL);

SQLExecute (hStmt);

// Call the SQLGetData() function inside the loop till SQL_NO_DATA_FOUND is returned.
while (rc != SQL_NO_DATA_FOUND)
{
    rc = SQLGetData(hStmt, 2, SQL_C_CHAR, buffer, 1024, &ind1);     
}