| Specification: | DB2 CLI 1.1 | ODBC 1.0 | ISO CLI |
SQLBindCol() is used to associate (bind) columns in a result set to either:
Alternatively, LOB columns can be bound directly to a file using SQLBindFileToCol().
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() or SQLFetchScroll() is called after. Column attributes may also be needed before calling SQLBindCol(), and can be obtained using SQLDescribeCol() or SQLColAttribute().
Syntax
SQLRETURN SQLBindCol (SQLHSTMT StatementHandle,
SQLUSMALLINT ColumnNumber,
SQLSMALLINT TargetType,
SQLPOINTER TargetValuePtr,
SQLINTEGER BufferLength,
SQLINTEGER *FAR StrLen_or_IndPtr);
Function Arguments
Table 15. SQLBindCol Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle |
| SQLUSMALLINT | ColumnNumber | input | Number identifying the column. Columns are numbered sequentially,
from left to right.
|
| SQLSMALLINT | TargetType | input | The C data type for column number ColumnNumber in the result
set. The following types are supported:
Specifying SQL_C_DEFAULT causes data to be transferred to its default C data type, refer to Table 2 for more information. |
| SQLPOINTER | TargetValuePtr | input/output (deferred) | Pointer to buffer (or an array of buffers if using
SQLFetchScroll()) where DB2 CLI is to store the column data or the
LOB locator when the fetch occurs.
This buffer is used to return data when the Operation argument to SQLSetPos is SQL_REFRESH. The buffer is used to retrieve data when the SQLSetPos Operation argument is set to SQL_UPDATE. If TargetValuePtr is null, the column is unbound. |
| SQLINTEGER | BufferLength | input | Size of TargetValuePtr buffer in bytes available to store the
column data or the LOB locator.
If TargetType denotes a binary or character string (either single or double byte) or is SQL_C_DEFAULT, then BufferLength must be > 0, or an error will be returned. Otherwise, this argument is ignored. |
| SQLINTEGER * | StrLen_or_IndPtr | input/output (deferred) | Pointer to value (or array of values) which indicates the number of bytes
DB2 CLI has available to return in the TargetValuePtr buffer.
If TargetType is a LOB locator, the size of the locator is returned,
not the size of the LOB data.
This buffer is used to return data when the Operation argument to SQLSetPos is SQL_REFRESH. The buffer is used to retrieve data when the SQLSetPos Operation argument is set to SQL_UPDATE. SQLFetch() returns SQL_NULL_DATA in this argument if the data value of the column is null. This pointer value must be unique for each bound column, or NULL. A value of SQL_COLUMN_IGNORE can also be set for use with SQLBulkOperations(). See SQLBulkOperations - Add, Update, Delete or Fetch a Set of Rows for more details. SQL_NO_LENGTH may also be returned, refer to the Usage section below for more information. |
Usage
The application calls SQLBindCol() once for each column in the result set for which it wishes to retrieve either the data, or optionally in the case of LOB columns, a LOB locator. Result sets are generated either by calling SQLExecute(), SQLExecDirect(), SQLGetTypeInfo(), or one of the catalog functions. When SQLFetch() is called, the data in each of these bound columns is placed into the assigned location (given by the pointers TargetValuePtr and StrLen_or_Ind). If TargetType is a LOB locator, a locator value is returned, not the LOB data; the LOB locator references the entire data value in the LOB column.
SQLFetch() and SQLFetchScroll() can be used to retrieve multiple rows from the result set into an array. In this case, TargetValuePtr references an array. For more information, refer to Retrieving a Result Set into an Array and SQLFetchScroll - Fetch Rowset and Return Data for All Bound Columns.
Columns are identified by a number, assigned sequentially from left to right.
If you are going to use bookmarks you must first set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_ON.
The number of columns in the 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. Refer to Data Types and Data Conversion for more information on default types and supported conversions.
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. Generally, SQLBindCol() is more efficient than SQLGetData(). For a discussion of when to use one function over the other, refer to Appendix A, Programming Hints and Tips.
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.
Instead of multiple calls to SQLBindCol(), DB2 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.
See Column Binding Offsets for the list of steps required to use an offset.
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 may be truncated. If the buffer is to contain fixed length data, DB2 CLI assumes the size of the buffer is the length of the C data type. If data conversion is specified, the required size may be affected, see Data Types and Data Conversion for more information.
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 DB2 CLI to null terminate graphic strings, see the PATCH1 keyword in Configuration Keywords.
| Note: | SQL_NO_TOTAL will be returned in StrLen_or_IndPtr if: |
LOB locators can in general be treated as any other data type, but there are some important differences:
A LOB locator is not a pointer to a database position, but rather it is a reference to a LOB value: a snapshot of that LOB value. There is no association between the current position of the cursor and the row from which the LOB value was extracted. This means that even after the cursor has moved to a different row, the LOB locator (and thus the value that it represents) can still be referenced.
For a given LOB column in the result set, the binding can be to a:
The most recent bind column function call determines the type of binding that is in effect.
Descriptors and SQLBindCol
The following sections describe how SQLBindCol() interacts with descriptors.
| Note: | Calling SQLBindCol() for one statement can affect other statements. This occurs when the ARD associated with the statement is explicitly allocated and is also associated with other statements. Because SQLBindCol() modifies the descriptor, the modifications apply to all statements with which this descriptor is associated. If this is not the required behavior, the application should dissociate this descriptor from the other statements before calling SQLBindCol(). |
Argument Mappings
Conceptually, SQLBindCol() performs the following steps in sequence:
The variable that the StrLen_or_Ind argument refers to is used for both indicator and length information. If a fetch encounters a null value for the column, it stores SQL_NULL_DATA in this variable; otherwise, it stores the data length in this variable. Passing a null pointer as StrLen_or_Ind keeps the fetch operation from returning the data length, but makes the fetch fail if it encounters a null value and has no way to return SQL_NULL_DATA.
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_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_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 may 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
Table 16. SQLBindCol SQLSTATEs
| SQLSTATE | Description | Explanation | ||
|---|---|---|---|---|
| 07009 | Invalid descriptor index | The value specified for the argument ColumnNumber exceeded the maximum number of columns in the result set. | ||
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. | ||
| 58004 | Unexpected system failure. | Unrecoverable system error. | ||
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. | ||
| HY002 | Invalid column number. | The value specified for the argument ColumnNumber was less than
0.
The value specified for the argument ColumnNumber exceeded the maximum number of columns supported by the data source. | ||
| HY003 | Program type out of range. | TargetType was not a valid data type or SQL_C_DEFAULT. | ||
| HY010 | Function sequence error. | The function was called while in a data-at-execute
(SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. | ||
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of the function. | ||
| HY090 | Invalid string or buffer length. | The value specified for the argument BufferLength is less than 1 and the argument TargetType is either SQL_C_CHAR, SQL_C_BINARY or SQL_C_DEFAULT. | ||
| HYC00 | Driver not capable. | DB2 CLI recognizes, but does not support the data type specified in the
argument TargetType
A LOB locator C data type was specified, but the connected server does not support LOB data types. | ||
| ||||
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, SQLSTATE HYC00 will be returned.
(The complete sample utilcli.c is also available here .)
/* From the CLI sample utilcli.c */
/* ... */
/* bind columns to program vars, converting all types to CHAR */
sqlrc = SQLBindCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_C_CHAR,
outData[i].buff,
outData[i].buffLen,
&outData[i].len ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
References