SQLBindCol function (CLI) - Bind a column to an application variable or LOB locator

Purpose

SQLBindCol() is called once for each column in the result set that the application needs to retrieve.

In general, SQLPrepare(), SQLExecDirect() or one of the schema functions is called before this function, and SQLFetch(), SQLFetchScroll(), SQLBulkOperations(), or SQLSetPos() is called after. Column attributes might also be needed before calling SQLBindCol(), and can be obtained using SQLDescribeCol() or SQLColAttribute().

Syntax

SQLRETURN   SQLBindCol (
               SQLHSTMT          StatementHandle,            /* hstmt */
               SQLUSMALLINT      ColumnNumber,               /* icol */
               SQLSMALLINT       TargetType,                 /* fCType */
               SQLPOINTER        TargetValuePtr,             /* rgbValue */
               SQLLEN            BufferLength,               /* dbValueMax */
               SQLLEN            *StrLen_or_IndPtr);         /* *pcbValue */

Function arguments

• For this function, both TargetValuePtr and StrLen_or_IndPtr are deferred outputs, meaning that the storage locations these pointers point to do not get updated until a result set row is fetched. As a result, the locations referenced by these pointers must remain valid until SQLFetch() or SQLFetchScroll() is called. For example, if SQLBindCol() is called within a local function, SQLFetch() must be called from within the same scope of the function or the TargetValuePtr buffer must be allocated as static or global.
• CLI will be able to optimize data retrieval for all variable length data types if TargetValuePtr is placed consecutively in memory after StrLen_or_IndPtr.

Usage

Call SQLBindCol() once for each column in the result set for which either the data or, for LOB columns, the LOB locator is to be retrieved. When SQLFetch() or SQLFetchScroll() is called to retrieve data from the result set, the data in each of the bound columns is placed in the locations assigned by the TargetValuePtr and StrLen_or_IndPtr pointers. When the statement attribute SQL_ATTR_ROW_ARRAY_SIZE is greater than 1, then TargetType should refer to an array of buffers. If TargetType is a LOB locator, a locator value is returned, not the actual LOB data. The LOB locator references the entire data value in the LOB column.

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.

After columns have been bound, in subsequent fetches the application can change the binding of these columns or bind previously unbound columns by calling SQLBindCol(). The new binding does not apply to data already fetched, it will be used on the next fetch. To unbind a single column (including columns bound with SQLBindFileToCol()), call SQLBindCol() with the TargetValuePtr pointer set to NULL. To unbind all the columns, the application should call SQLFreeStmt() with the Option input set to SQL_UNBIND.

The application must ensure enough storage is allocated for the data to be retrieved. If the buffer is to contain variable length data, the application must allocate as much storage as the maximum length of the bound column plus the NULL terminator. Otherwise, the data might be truncated. If the buffer is to contain fixed length data, CLI assumes the size of the buffer is the length of the C data type. If data conversion is specified, the required size might be affected.

If string truncation does occur, SQL_SUCCESS_WITH_INFO is returned and StrLen_or_IndPtr will be set to the actual size of TargetValuePtr available for return to the application.

Truncation is also affected by the SQL_ATTR_MAX_LENGTH statement attribute (used to limit the amount of data returned to the application). The application can specify not to report truncation by calling SQLSetStmtAttr() with SQL_ATTR_MAX_LENGTH and a value for the maximum length to return for all variable length columns, and by allocating a TargetValuePtr buffer of the same size (plus the null-terminator). If the column data is larger than the set maximum length, SQL_SUCCESS will be returned when the value is fetched and the maximum length, not the actual length, will be returned in StrLen_or_IndPtr.

If the column to be bound is a SQL_GRAPHIC, SQL_VARGRAPHIC or SQL_LONGVARGRAPHIC type, then TargetType can be set to SQL_C_DBCHAR or SQL_C_CHAR. If TargetType is SQL_C_DBCHAR, the data fetched into the TargetValuePtr buffer will be null-terminated with a double byte null-terminator. If TargetType is SQL_C_CHAR, then there will be no null-termination of the data. In both cases, the length of the TargetValuePtr buffer (BufferLength) is in units of bytes and should therefore be a multiple of 2. It is also possible to force CLI to null terminate graphic strings using the PATCH1 keyword.

• The SQL type is a variable length type, and
• StrLen_or_IndPtr and TargetValuePtr are contiguous, and
• The column type is NOT NULLABLE, and
• String truncation occurred.

Descriptors and SQLBindCol

The following sections describe how SQLBindCol() interacts with descriptors.

Argument mappings

If the call to SQLBindCol() fails, the content of the descriptor fields it would have set in the ARD are undefined, and the value of the SQL_DESC_COUNT field of the ARD is unchanged.

Implicit resetting of COUNT field

SQLBindCol() sets SQL_DESC_COUNT to the value of the ColumnNumber argument only when this would increase the value of SQL_DESC_COUNT. If the value in the TargetValuePtr argument is a null pointer and the value in the ColumnNumber argument is equal to SQL_DESC_COUNT (that is, when unbinding the highest bound column), then SQL_DESC_COUNT is set to the number of the highest remaining bound column.

Cautions regarding SQL_C_DEFAULT

To retrieve column data successfully, the application must determine correctly the length and starting point of the data in the application buffer. When the application specifies an explicit TargetType, application misconceptions are readily detected. However, when the application specifies a TargetType of SQL_C_DEFAULT, SQLBindCol() can be applied to a column of a different data type from the one intended by the application, either from changes to the metadata or by applying the code to a different column. In this case, the application might fail to determine the start or length of the fetched column data. This can lead to unreported data errors or memory violations.

Return codes

Diagnostics

Restrictions

The LOB data support is only available when connected to a server that supports large object data types. If the application attempts to specify a LOB locator C data type for a server that does not support it, SQLSTATE HYC00 will be returned.

Example

 /* bind column 1 to variable */
 cliRC = SQLBindCol(hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, &deptnumb.ind);