SQLBindCol - Bind a column to an application variable

SQLBindCol - Bind a column to an application variable

SQLBindCol() is used to associate (bind) columns in a result set to application variables (storage buffers) for all data types. Data is transferred from the Database Management System (DBMS) to the application when SQLFetch() is called.
This function is also used to specify any data conversion that is required. It is called once for each column in the result set that the application needs to retrieve.
SQLPrepare() or SQLExecDirect() is typically called before this function. It might also be necessary to call SQLDescribeCol() or SQLColAttributes().
SQLBindCol() must be called before SQLFetch() to transfer data to the storage buffers that are specified by this call.

Syntax
SQLRETURN SQLBindCol (SQLHSTMT       hstmt,
                      SQLSMALLINT    icol,
                      SQLSMALLINT    fCType,
                      SQLPOINTER     rgbValue,
                      SQLINTEGER     cbValueMax,
                      SQLINTEGER     *pcbValue);
Function arguments

Table 1. SQLBindCol arguments
Data type Argument Use Description

SQLHSTMT hstmt Input Statement handle.
SQLSMALLINT icol Input Number identifying the column. Columns are numbered sequentially, from left to right, starting at 1.
SQLSMALLINT fCType Input Application data type for column number icol in the result set. The following types are supported:

SQL_BIGINT

SQL_BINARY

SQL_BLOB

SQL_BLOB_LOCATOR

SQL_CHAR

SQL_CLOB

SQL_CLOB_LOCATOR

SQL_DATALINK

SQL_DATETIME

SQL_DBCLOB

SQL_DBCLOB_LOCATOR

SQL_DECIMAL

SQL_DOUBLE

SQL_FLOAT

SQL_GRAPHIC

SQL_INTEGER

SQL_NUMERIC

SQL_REAL

SQL_SMALLINT

SQL_TYPE_DATE

SQL_TYPE_TIME

SQL_TYPE_TIMESTAMP

SQL_VARBINARY

SQL_VARCHAR

SQL_VARGRAPHIC

SQL_WCHAR

SQL_WVARCHAR

Specifying SQL_DEFAULT causes data to be transferred to its default data type; refer to Table 1 for more information.
SQLPOINTER rgbValue Output (deferred) Pointer to buffer where DB2^® UDB CLI is to store the column data when the fetch occurs.
If rgbValue is null, the column is unbound.
SQLINTEGER cbValueMax Input Size of rgbValue buffer in bytes available to store the column data.
If fCType is either SQL_CHAR or SQL_DEFAULT, then cbValueMax must be > 0 otherwise an error is returned.
If fcType is either SQL_DECIMAL or SQL_NUMERIC, cbValueMax must actually be a precision and scale. The method to specify both values is to use (precision * 256) + scale. This is also the value returned as the LENGTH of these data types when using SQLColAttributes().
If fcType specifies any form of double-byte character data, then cbValueMax must be the number of double-byte characters, not the number of bytes.
SQLINTEGER * pcbValue Output (deferred) Pointer to value which indicates the number of bytes DB2 UDB CLI has available to return in the rgbValue buffer.
SQLFetch() returns SQL_NULL_DATA in this argument if the data value of the column is null. SQL_NTS is returned in this argument if the data value of the column is returned as a null-terminated string.

For this function, both rgbValue and pcbValue are deferred outputs, meaning that the storage locations these pointers point to are not updated until SQLFetch() is called. The locations referred to by these pointers must remain valid until SQLFetch() is called.

Usage

The application calls SQLBindCol() once for each column in the result set that it wants to retrieve. When SQLFetch() is called, the data in each of these bound columns is placed in the assigned location (given by the pointers rgbValue and pcbValue).
The application can query the attributes (such as data type and length) of the column by first calling SQLDescribeCol() or SQLColAttributes(). This information can then be used to specify the correct data type of the storage locations, or to indicate data conversion to other data types. Refer to Data types and data conversion in DB2 UDB CLI functions for more information.
In later fetches, the application can change the binding of these columns or bind unbound columns by calling SQLBindCol(). The new binding does not apply to data fetched, it is used when the next SQLFetch() is called. To unbind a single column, call SQLBindCol() with rgbValue set to NULL. To unbind all the columns, the application should call SQLFreeStmt() with the fOption input set to SQL_UNBIND.
Columns are identified by a number, assigned sequentially from left to right, starting at 1. The number of columns in the result set can be determined by calling SQLNumResultCols() or SQLColAttributes() with the fdescType argument set to SQL_DESC_COUNT.
All character data is treated as the default job coded character set identifier (CCSID) if the SQL_ATTR_UTF8 environment attribute is not set to SQL_TRUE.
An application can choose not to bind every column, or even not to bind any columns. The data in the unbound columns (and only the unbound columns) can be retrieved using SQLGetData() after SQLFetch() has been called. SQLBindCol() is more efficient than SQLGetData(), and should be used whenever possible.
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 requires; otherwise, the data might be truncated. The default is null termination for output character strings. To change this set the SQLSetEnvAttr() attribute SQL_ATTR_OUTPUT_NTS to SQL_FALSE. The output values for pcbValue after a call to SQLFetch() behave in the following way for character data types:

If the null termination attribute is set (the default), then SQL_NTS is returned in the pcbValue.

If the null termination attribute is not set, then the value of cbValueMax, which is the maximum bytes available, is returned in pcbValue.

If truncation occurs, then the value of cbValueMax, which is the actual bytes available, is returned in pcbValue.

If truncation occurs and the SQLSetEnvAttr() attribute SQL_ATTR_TRUNCATION_RTNC is set to SQL_FALSE (which is the default), then SQL_SUCCESS is returned in the SQLFetch() return code. If truncation occurs and the attribute is SQL_TRUE, then SQL_SUCCESS_WITH_INFO is returned. SQL_SUCCESS is returned in both cases if no truncation occurs.
Truncation occurs when argument cbValueMax does not allocate space for the amount of fetched data. If the environment is set to run with null terminated strings, make sure to allocate space for the additional byte in cbValueMax. For additional truncation information, refer to SQLFetch - Fetch next row.
DB2 UDB CLI for i5/OS^® differs from DB2 CLI for Linux^®, UNIX^®, and Windows^® in the way it returns length information in the pcbValue argument. After a fetch for an SQL_VARCHAR column, DB2 UDB CLI for i5/OS returns the bytes that are fetched in the first 2 bytes of the VARCHAR structure that is bound. DB2 UDB CLI for i5/OS does not return the length in pcbValue as it does for SQL_CHAR. This is different from DB2 CLI for Linux, UNIX, and Windows, which have no representation of C VARCHAR and include the length information in the pcbValue buffer when the application binds to the SQL_CHAR column.

Return codes

SQL_SUCCESS

SQL_ERROR

SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLBindCol SQLSTATEs
SQLSTATE Description Explanation

40003 * Statement completion unknown The communication link between the CLI and the data source fails before the function completes processing.
58004 System error Unrecoverable system error.
HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY002 Column number that is not valid The value specified for the argument icol is 0.
The value specified for the argument icol exceeded the maximum number of columns supported by the data source.
HY003 Program type out of range fCType is not a valid data type.
HY009 Argument value that is not valid rgbValue is a null pointer.
The value specified for the argument cbValueMax is less than 1, and the argument fCType is either SQL_CHAR or SQL_DEFAULT.
HY013 * Memory management problem The driver is unable to access memory required to support the processing or completion of the function.
HY014 Too many handles The maximum number of handles has been allocated, and use of this function requires an additional descriptor handle.
HY021 Internal descriptor that is not valid The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00 Driver not capable The driver recognizes, but does not support the data type specified in the argument fCType (see also HY003).

Example

Refer to the example in SQLFetch - Fetch next row.

References

SQLExecDirect - Execute a statement directly

SQLExecute - Execute a statement

SQLFetch - Fetch next row

SQLPrepare - Prepare a statement

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLSMALLINT	icol	Input	Number identifying the column. Columns are numbered sequentially, from left to right, starting at 1.
SQLSMALLINT	fCType	Input	Application data type for column number icol in the result set. The following types are supported: SQL_BIGINT SQL_BINARY SQL_BLOB SQL_BLOB_LOCATOR SQL_CHAR SQL_CLOB SQL_CLOB_LOCATOR SQL_DATALINK SQL_DATETIME SQL_DBCLOB SQL_DBCLOB_LOCATOR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC SQL_WCHAR SQL_WVARCHAR Specifying SQL_DEFAULT causes data to be transferred to its default data type; refer to Table 1 for more information.
SQLPOINTER	rgbValue	Output (deferred)	Pointer to buffer where DB2^® UDB CLI is to store the column data when the fetch occurs. If rgbValue is null, the column is unbound.
SQLINTEGER	cbValueMax	Input	Size of rgbValue buffer in bytes available to store the column data. If fCType is either SQL_CHAR or SQL_DEFAULT, then cbValueMax must be > 0 otherwise an error is returned. If fcType is either SQL_DECIMAL or SQL_NUMERIC, cbValueMax must actually be a precision and scale. The method to specify both values is to use (precision 256) + scale. This is also the value returned as the LENGTH of these data types when using `SQLColAttributes()`. If fcType* specifies any form of double-byte character data, then cbValueMax must be the number of double-byte characters, not the number of bytes.
SQLINTEGER *	pcbValue	Output (deferred)	Pointer to value which indicates the number of bytes DB2 UDB CLI has available to return in the rgbValue buffer. `SQLFetch()` returns `SQL_NULL_DATA` in this argument if the data value of the column is null. SQL_NTS is returned in this argument if the data value of the column is returned as a null-terminated string.

SQLSTATE	Description	Explanation
40003 *	Statement completion unknown	The communication link between the CLI and the data source fails before the function completes processing.
58004	System error	Unrecoverable system error.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY002	Column number that is not valid	The value specified for the argument icol is 0. The value specified for the argument icol exceeded the maximum number of columns supported by the data source.
HY003	Program type out of range	fCType is not a valid data type.
HY009	Argument value that is not valid	rgbValue is a null pointer. The value specified for the argument cbValueMax is less than 1, and the argument fCType is either SQL_CHAR or SQL_DEFAULT.
HY013 *	Memory management problem	The driver is unable to access memory required to support the processing or completion of the function.
HY014	Too many handles	The maximum number of handles has been allocated, and use of this function requires an additional descriptor handle.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00	Driver not capable	The driver recognizes, but does not support the data type specified in the argument fCType (see also HY003).