SQLBindCol - Bind a column to an application variable

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 SQLColAttribute() to get the attributes of the corresponding result set column.

SQLBindCol() must be called before SQLFetch() to transfer data to the storage buffers that are specified by this call.

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_C_BIGINT SQL_C_BINARY SQL_C_BLOB SQL_C_BLOB_LOCATOR SQL_C_CHAR SQL_C_CLOB SQL_C_CLOB_LOCATOR SQL_C_DATE SQL_TYPE_DATE SQL_C_DATETIME SQL_C_DBCHAR SQL_C_DBCLOB SQL_C_DBCLOB_LOCATOR SQL_C_DECFLOAT128 SQL_C_DECFLOAT64 SQL_C_DECFLOAT32 SQL_C_DOUBLE SQL_C_FLOAT SQL_C_LONG SQL_C_SLONG SQL_C_REAL SQL_C_SHORT SQL_C_TIME SQL_C_TIMESTAMP SQL_C_STINYINT SQL_C_UTINYINT SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_C_WCHAR Specifying SQL_DEFAULT causes data to be transferred to its default data type; refer to Table 1 for more information. The SQL data type constants , such as SQL_DECIMAL, may also be used for the application data type in many cases.
SQLPOINTER	rgbValue	Output (deferred)	Pointer to buffer where DB2® for i 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 SQLColAttribute(). 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 for i 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 SQLColAttribute(). 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 for i CLI functions for more information.

For subsequent Fetch requests, the application can change the binding of these columns or bind unbound columns by calling FSQLBindCol(). 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 as they appear in the result set, starting at 1. The number of columns in the result set can be determined by calling SQLNumResultCols() or SQLColAttribute() with the FieldIdentifier 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 to bind anywhere from zero columns to all 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 you must 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 SQL_ATTR_OUTPUT_NTS attribute is set to SQL_TRUE (the default), then SQL_NTS is returned in the pcbValue.
• If the SQL_ATTR_OUTPUT_NTS attribute is set to SQL_FALSE, 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 for i CLI 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 for i CLI returns the bytes that are fetched in the first 2 bytes of the VARCHAR structure that is bound. DB2 for i CLI 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.

For decimal floating point data types, a precision of 32, 64, or 128 can be specified by using the default symbolic C data type constants. For example, to specify a decimal floating point data type with a precision of 128 bytes, fCType can be set to SQL_C_DECIMAL128.

Return codes

• SQL_SUCCESS
• SQL_ERROR
• SQL_INVALID_HANDLE

Diagnostics

Example

References

• SQLExecDirect - Execute a statement directly
• SQLExecute - Execute a statement
• SQLFetch - Fetch next row
• SQLPrepare - Prepare a statement