Column binding in CLI applications

The column binding is associating columns in a result set to C data type variables, and associating LOB columns to LOB locators or LOB file references.

Columns may be bound to:
  • Application storage

    SQLBindCol() is used to bind application storage to the column. Data will be transferred from the server to the application at fetch time. Length of the available data to return is also set.

  • LOB locators

    SQLBindCol() is used to bind LOB locators to the column. Only the LOB locator (4 bytes) will be transferred from the server to the application at fetch time.

    If a CLI application does not provide an output buffer for a LOB column using the function SQLBindCol() the IBM® data server client will, by default, request a LOB locator on behalf of the application for each LOB column in the result sets.

    Once an application receives a locator it can be used in SQLGetSubString(), SQLGetPosition(), SQLGetLength(), or as the value of a parameter marker in another SQL statement. SQLGetSubString() can either return another locator, or the data itself. All locators remain valid until the end of the transaction in which they were created (even when the cursor moves to another row), or until it is freed using the FREE LOCATOR statement.

  • Lob file references

    SQLBindFileToCol() is used to bind a file to a LOB or XML column. CLI will write the data directly to a file, and update the StringLength and IndicatorValue buffers specified on SQLBindFileToCol().

    If the data value for the column is NULL and SQLBindFileToCol() was used, then IndicatorValue will be set to SQL_NULL_DATA and StringLength to 0.

The number of columns in a result set can be determined by calling SQLNumResultCols() or by calling SQLColAttribute() with the DescType argument set to SQL_COLUMN_COUNT.

The application can query the attributes (such as data type and length) of the column by first calling SQLDescribeCol() or SQLColAttribute(). This information can then be used to allocate a storage location of the correct data type and length, to indicate data conversion to another data type, or in the case of LOB data types, optionally return a locator.

An application can choose not to bind every column, or even not to bind any columns. Data in any of the columns can also be retrieved using SQLGetData() after the bound columns have been fetched for the current row. It is usually more efficient to bind application variables or file references to result sets than to use SQLGetData(). When the data is in a LOB column, LOB functions are preferable to SQLGetData() . Use SQLGetData() when the data value is large variable-length data that:
  • must be received in pieces, or
  • may not need to be retrieved.

Instead of multiple calls to SQLBindCol(), CLI also supports column binding offsets. Rather than re-binding each time, an offset can be used to specify new buffer and length/indicator addresses which will be used in a subsequent call to SQLFetch() or SQLFetchScroll(). This can only be used with row wise binding, but will work whether the application retrieves a single row or multiple rows at a time.

When binding any variable length column, CLI will be able to write StrLen_or_IndPtr and TargetValuePtr in one operation if they are allocated contiguously. For example: 
    struct {  SQLINTEGER  StrLen_or_IndPtr;
              SQLCHAR     TargetValuePtr[MAX_BUFFER];
           } column;

The most recent bind column function call determines the type of binding that is in effect.