Piecemeal retrieval of data through CLI

There are two ways for a user to retrieve data from a DB2 Everyplace table through CLI:

• The application may choose to allocate the maximum memory the column value could occupy (based on its knowledge of a column in the result set via SQLDescribeCol() or prior knowledge), and bind it via SQLBindCol().
• The application may call SQLGetData() to get column data into the application allocated buffer.

In the case of binary data (BLOB) or character data (CHAR or VARCHAR), the column can be very long. The application developer may not want to allocate a buffer big enough to hold the whole column, or may not be able to afford to allocate a buffer this large. Additionally, in some cases the application only requires some pieces of the column. In these scenarios, piecemeal retrieval of data is needed.

Procedure

A feature of SQLGetData() allows the application to use repeated calls to obtain, in sequence, the value of a single column in more manageable pieces. Essentially, a call to SQLGetData() returns SQL_SUCCESS_WITH_INFO (with SQLSTATE 01004) to indicate more data exists for this column. SQLGetData() is called repeatedly to get the remaining pieces of data until it returns SQL_SUCCESS, signifying that the entire data has been retrieved for this column.

Syntax

SQLRETURN SQLGetData (
SQLHSTMT StatementHandle, /* hstmt */
SQLUSMALLINT ColumnNumber, /* icol */
SQLSMALLINT TargetType, /* fCType */
SQLPOINTER TargetValuePtr, /* rgbValue */
SQLINTEGER BufferLength, /* cbValueMax */
SQLINTEGER *FAR StrLen_or_IndPtr); /* pcbValue */

This will retrieve BufferLength bytes at a time, and StrLen_or_IndPtr indicates the number of bytes remaining. The return value of the function is SQL_SUCCESS_WITH_INFO (with SQLSTATE 01004) if there are bytes remaining. Otherwise, if the return value is SQL_SUCCESS, StrLen_or_IndPtr indicates the number of bytes that DB2 Everyplace CLI has available to return in the TargetValuePtr buffer. SQLGetData() can be used this way to retrieve long columns if the C data type (TargetType) is SQL_C_CHAR, SQL_C_BINARY, or if TargetType is SQL_C_DEFAULT and the column type denotes a binary or character string.

To use this feature of SQLGetData(), you must first set a statement attribute SQL_ATTR_GETDATA_MODE to SQL_PIECEMEAL_DATA. The default value of this attribute is SQL_CHUNK_DATA. The difference between these two modes is that, in SQL_CHUNK_DATA mode (which is the default mode), and when truncation occurs, SQLGetData()'s return value StrLen_or_IndPtr indicates the total number of bytes of this column, and the second call still retrieves data from the very beginning of the column.

Example code fragment

sqlrc = SQLSetStmtAttr(hstmt, SQL_ATTR_GETDATA_MODE, (SQLPOINTER) SQL_PIECEMEAL_DATA, 0);
    SQLCHAR * stmt = (SQLCHAR *) "SELECT blobColumn FROM t1 where c1 = ?"; 
    sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ; 
    sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, ...); 
    sqlrc = SQLExecute( hstmt ) ; 
    sqlrc = SQLFetch( hstmt ); 
    /* get BUFSIZ bytes at a time, bufInd indicates number of Bytes LEFT */ 
    sqlrc = SQLGetData (hstmt, 1, SQL_C_BINARY, (SQLPOINTER) buffer, BUFSIZ, &bufInd); 
    while( sqlrc == SQL_SUCCESS_WITH_INFO ) { 
        // handle BUFSIZ bytes of blob data in buffer 
            : 
        sqlrc = SQLGetData (hstmt, 1, SQL_C_BINARY, (SQLPOINTER) buffer, BUFSIZ, &bufInd); 
    } 
    if (sqlrc == SQL_SUCCESS) {  /* partial buffer on last GetData */ 
        // handle bufInd bytes of blob data in buffer 
            : 
    }