f90SQLBindCol (SQLBindCol)

Conformance

Version Introduced: ODBC 1.0

Standards Compliance: ISO 92

Summary

f90SQLBindCol binds application data buffers to columns in the result set.

Syntax

Arguments

StatementHandle [Input]

Statement handle.

ColumnNumber [Input]

Number of the result set column to bind. Columns are numbered in increasing column order starting at 0, where column 0 is the bookmark column. If bookmarks are not used - that is, the SQL_ATTR_USE_BOOKMARKS statement attribute is set to SQL_UB_OFF - then column numbers start at 1.

TargetType [Input]

The identifier of the Fortran data type of the *TargetValuePtr buffer. When retrieving data from the data source with f90SQLFetch, f90SQLFetchScroll, f90SQLBulkOperations, or f90SQLSetPos, the driver converts the data to this type; when sending data to the data source with f90SQLBulkOperations or f90SQLSetPos, the driver converts the data from this type. For a list of valid Fortran data types and type identifiers, see the Tables 4.3 in Chapter 4 and Appendix 4.

If the TargetType argument is an interval data type, the default interval leading precision (2) and the default interval seconds precision (6), as set in the SQL_DESC_DATETIME_INTERVAL_PRECISION and SQL_DESC_PRECISION fields of the ARD, respectively, are used for the data. If the TargetType argument is SQL_C_NUMERIC, the default precision (driver-defined) and default scale (0), as set in the SQL_DESC_PRECISION and SQL_DESC_SCALE fields of the ARD, are used for the data. If any default precision or scale is not appropriate, the application should explicitly set the appropriate descriptor field by a call to f90SQLSetDescField or f90SQLSetDescRec.

TargetValuePtr [Deferred Input/Output]

Pointer to the data buffer to bind to the column. f90SQLFetch and f90SQLFetchScroll return data in this buffer. f90SQLBulkOperations returns data in this buffer when Operation is SQL_FETCH_BY_BOOKMARK; it retrieves data from this buffer when Operation is SQL_ADD or SQL_UPDATE_BY_BOOKMARK. f90SQLSetPos returns data in this buffer when Operation is SQL_REFRESH; it retrieves data from this buffer when Operation is SQL_UPDATE.

If TargetValuePtr is a null pointer, the driver unbinds the data buffer for the column. An application can unbind all columns by calling f90SQLFreeStmt with the SQL_UNBIND option. An application can unbind the data buffer for a column, but still have a length/indicator buffer bound for the column, if the TargetValuePtr argument in the call to f90SQLBindCol is a null pointer, but the StrLen_or_IndPtr argument is a valid value.

TargetValue [Deferred Input/Output]

Buffer to bind to the column. f90SQLFetch and f90SQLFetchScroll return data in this buffer. f90SQLBulkOperations returns data in this buffer when Operation is SQL_FETCH_BY_BOOKMARK; it retrieves data from this buffer when Operation is SQL_ADD or SQL_UPDATE_BY_BOOKMARK. f90SQLSetPos returns data in this buffer when Operation is SQL_REFRESH; it retrieves data from this buffer when Operation is SQL_UPDATE.

TargetValue can be one the f90SQL predefined structures, in which case f90SQLBindCol will bind all the fields in the structure. These predefined structures are useful to bind the columns of resultsets from Catalog functions. See Chapter 13, Catalog Functions, for more information.

If TargetValue is used to bind a character buffer, then TargetValueBufferLength is set to Len(TargetValue).

TargetValueBufferLength [Input]

Length of the *TargetValuePtr buffer in bytes.

The driver uses TargetValueBufferLength to avoid writing past the end of the *TargetValuePtr buffer when returning variable-length data, such as character or binary data. Note that the driver counts the null-termination character when returning character data to *TargetValuePtr. *TargetValuePtr must therefore contain space for the null-termination character or the driver will truncate the data.

When the driver returns fixed-length data, such as an integer or a date structure, the driver ignores TargetValueBufferLength and assumes the buffer is large enough to hold the data. It is therefore important for the application to allocate a large enough buffer for fixed-length data or the driver will write past the end of the buffer.

f90SQLBindCol returns SQLSTATE HY090 (Invalid string or buffer length) when TargetValueBufferLength is less than 0 but not when TargetValueBufferLength is 0. However, if TargetType specifies a character type, an application should not set TargetValueBufferLength to 0, because ISO CLI - compliant drivers return SQLSTATE HY090 (Invalid string or buffer length) in that case.

StrLen_or_IndPtr [Deferred Input/Output]

Pointer to the length/indicator buffer to bind to the column. f90SQLFetch and f90SQLFetchScroll return a value in this buffer. f90SQLBulkOperations retrieves a value from this buffer when Operation is SQL_ADD, SQL_UPDATE_BY_BOOKMARK, or SQL_DELETE_BY_BOOKMARK. f90SQLBulkOperations returns a value in this buffer when Operation is SQL_FETCH_BY_BOOKMARK. f90SQLSetPos returns a value in this buffer when Operation is SQL_REFRESH; it retrieves a value from this buffer when Operation is SQL_UPDATE.

f90SQLFetch, f90SQLFetchScroll, f90SQLBulkOperations, and f90SQLSetPos can return the following values in the length/indicator buffer:

• The length of the data available to return
• SQL_NO_TOTAL
• SQL_NULL_DATA

The application can place the following values in the length/indicator buffer for use with f90SQLBulkOperations or f90SQLSetPos:

• The length of the data being sent
• SQL_NTS
• SQL_NULL_DATA
• SQL_DATA_AT_EXEC
• The result of the SQL_LEN_DATA_AT_EXEC macro
• SQL_COLUMN_IGNORE

If the indicator buffer and the length buffer are separate buffers, the indicator buffer can return only SQL_NULL_DATA, while the length buffer can return all other values.

If StrLen_or_IndPtr is a null pointer, no length or indicator value is used. This is an error when fetching data and the data is NULL.

iRet [Output]

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When f90SQLBindCol returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling f90SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by f90SQLBindCol and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

f90SQLBindCol is used to associate, or bind, columns in the result set to data buffers and length/indicator buffers in the application. When the application calls f90SQLFetch, f90SQLFetchScroll, or f90SQLSetPos to fetch data, the driver returns the data for the bound columns in the specified buffers; for more information, see f90SQLFetch. When the application calls f90SQLBulkOperations to update or insert a row or f90SQLSetPos to update a row, the driver retrieves the data for the bound columns from the specified buffers; for more information, see f90SQLBulkOperations or f90SQLSetPos.

Note that columns do not have to be bound to retrieve data from them. An application can also call f90SQLGetData to retrieve data from columns. Although it is possible to bind some columns in a row and call f90SQLGetData for others, this is subject to some restrictions. For more information, see f90SQLGetData.

Binding, Unbinding, and Rebinding Columns

A column can be bound, unbound, or rebound at any time, even after data has been fetched from the result set. The new binding takes effect the next time a function that uses bindings is called. For example, suppose an application binds the columns in a result set and calls f90SQLFetch. The driver returns the data in the bound buffers. Now suppose the application binds the columns to a different set of buffers. The driver does not place the data for the just-fetched row in the newly bound buffers. Instead, it waits until f90SQLFetch is called again and then places the data for the next row in the newly bound buffers.

Note: The statement attribute SQL_ATTR_USE_BOOKMARKS should always be set before binding a column to column 0. This is not required, but is strongly recommended.

Binding Columns

To bind a column, an application calls f90SQLBindCol and passes the column number, type, address, and length of a data buffer, and the address of a length/indicator buffer. For information about how these addresses are used, see "Buffer Addresses," later in this section.

The use of these buffers is deferred; that is, the application binds them in f90SQLBindCol but the driver accesses them from other functions - namely f90SQLBulkOperations, f90SQLFetch, f90SQLFetchScroll, or f90SQLSetPos. It is the application's responsibility to make sure that the pointers specified in f90SQLBindCol remain valid as long as the binding remains in effect. If the application allows these pointers to become invalid - for example, it frees a buffer - and then calls a function that expects them to be valid, the consequences are undefined.

The binding remains in effect until it is replaced by a new binding, the column is unbound, or the statement is freed.

Unbinding Columns

To unbind a single column, an application calls f90SQLBindCol with ColumnNumber set to the number of that column and TargetValuePtr set to a null pointer. If ColumnNumber refers to an unbound column, f90SQLBindCol still returns SQL_SUCCESS.

To unbind all columns, an application calls f90SQLFreeStmt with fOption set to SQL_UNBIND. This also can be accomplished by setting the SQL_DESC_COUNT field of the ARD to zero.

Rebinding Columns

An application can perform either of two operations to change a binding:

• Call f90SQLBindCol to specify a new binding for a column that is already bound. The driver overwrites the old binding with the new one.

• Specify an offset to be added to the buffer address that was specified by the binding call to f90SQLBindCol. For more information, see the next section, "Binding Offsets."

Binding Offsets

A binding offset is a value that is added to the addresses of the data and length/indicator buffers (as specified in the TargetValuePtr and StrLen_or_IndPtr arguments) before they are dereferenced. When offsets are used, the bindings are a "template" of how the application's buffers are laid out and the application can move this "template" to different areas of memory by changing the offset. Because the same offset is added to each address in each binding, the relative offsets between buffers for different columns must be the same within each set of buffers. This is always true when row-wise binding is used; the application must carefully lay out its buffers for this to be true when column-wise binding is used.

Using a binding offset has much the same effect as rebinding a column by calling f90SQLBindCol. The difference is that a new call to f90SQLBindCol specifies new addresses for the data buffer and length/indicator buffer, while use of a binding offset does not change the addresses, but merely adds an offset to them. The application can specify a new offset whenever it wants, and this offset is always added to the originally bound addresses. In particular, if the offset is set to 0 or if the statement attribute is set to a null pointer, the driver uses the originally bound addresses.

To specify a binding offset, the application sets the SQL_ATTR_ROW_BIND_OFFSET_PTR statement attribute to the address of an SQLINTEGER buffer. Before the application calls a function that uses bindings, it places an offset in bytes in this buffer. To determine the address of the buffer to use, the driver adds the offset to the address in the binding. Note that the sum of the address and the offset must be a valid address, but that the address to which the offset is added need not be valid. For more information about how binding offsets are used, see "Buffer Addresses," later in this section.

Binding Arrays

If the rowset size (the value of the SQL_ATTR_ROW_ARRAY_SIZE statement attribute) is greater than 1, the application binds arrays of buffers rather than single buffers.

The application can bind arrays in two ways:

• Bind an array to each column. This is called column-wise binding because each data structure (array) contains data for a single column.

• Define a structure to hold the data for an entire row and bind an array of these structures. This is called row-wise binding because each data structure contains the data for a single row.

Each array of buffers must have at least as many elements as the size of the rowset.

Note: An application must verify that alignment is valid. For more information about alignment considerations, see Appendix 6.

Column-Wise Binding

In column-wise binding, the application binds separate data and length/indicator arrays to each column.

To use column-wise binding, the application first sets the SQL_ATTR_ROW_BIND_TYPE statement attribute to SQL_BIND_BY_COLUMN (this is the default). For each column to be bound, the application performs the following steps:

1. Allocates a data buffer array.
   
   
2. Allocates an array of length/indicator buffers.
   
   Note: If the application writes directly to descriptors when column-wise binding is used, then separate arrays can be used for length and indicator data.
   
   
3. Calls f90SQLBindCol with the following arguments:

• TargetType is the type of a single element in the data buffer array.
• TargetValuePtr is the address of the data buffer array.
• TargetValueBufferLength is the size of a single element in the data buffer array. The TargetValueBufferLength argument is ignored when the data is fixed-length data.
• StrLen_or_IndPtr is the address of the length/indicator array.

For more information about how this information is used, see "Buffer Addresses," later in this section.

Row-Wise Binding

In row-wise binding, the application defines a structure containing data and length/indicator buffers for each column to be bound.

To use row-wise binding, the application performs the following steps:

1. Defines a structure to hold a single row of data (including both data and length/indicator buffers) and allocates an array of these structures.
   
   Note: If the application writes directly to descriptors when row-wise binding is used, then separate fields can be used for length and indicator data.
   
   
2. Sets the SQL_ATTR_ROW_BIND_TYPE statement attribute to the size of the structure containing a single row of data, or to the size of an instance of a buffer into which the results columns will be bound. The length must include space for all of the bound columns, and any padding of the structure or buffer to make sure that when the address of a bound column is incremented with the specified length, the result will point to the beginning of the same column in the next row.
   
   
3. Calls f90SQLBindCol with the following arguments for each column to be bound:

• TargetType is the type of the data buffer member to be bound to the column.
• TargetValuePtr is the address of the data buffer member in the first array element.
• TargetValueBufferLength is the size of the data buffer member.
• StrLen_or_IndPtr is the address of the length/indicator member to be bound.

For more information about how this information is used, see "Buffer Addresses," later in this section.

Buffer Addresses

The buffer address is the actual address of the data or length/indicator buffer. The driver calculates the buffer address just prior to writing to the buffers (such as during fetch time). It is calculated from the following formula, which uses the addresses specified in the TargetValuePtr and StrLen_or_IndPtr arguments, the binding offset, and the row number:

Bound Address + Binding Offset + ((Row Number) x Element Size)

where:

Descriptors and f90SQLBindCol

The following sections describe how f90SQLBindCol interacts with descriptors.

Caution: Calling f90SQLBindCol 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 f90SQLBindCol 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 f90SQLBindCol.

Argument Mappings

Conceptually, f90SQLBindCol performs the following steps in sequence:

1. Calls f90SQLGetStmtAttr to obtain the ARD handle.
   
   
2. Calls f90SQLGetDescField to get this descriptor's SQL_DESC_COUNT field, and if the value in the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, calls f90SQLSetDescField to increase the value of SQL_DESC_COUNT to ColumnNumber.
   
   
3. Calls f90SQLSetDescField multiple times to assign values to the following fields of the ARD:

• Sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of TargetType, except that if TargetType is one of the concise identifiers of a datetime or interval subtype, it sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL, respectively, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.
• Sets one or more of SQL_DESC_LENGTH, SQL_DESC_PRECISION, SQL_DESC_SCALE, and SQL_DESC_DATETIME_INTERVAL_PRECISION, as appropriate for TargetType.
• Sets the SQL_DESC_OCTET_LENGTH field to the value of TargetValueBufferLength.
• Sets the SQL_DESC_DATA_PTR field to the value of TargetValue.
• Sets the SQL_DESC_INDICATOR_PTR field to the value of StrLen_or_Ind (see the following paragraph).
• Sets the SQL_DESC_OCTET_LENGTH_PTR field to the value of StrLen_or_Ind (see the following paragraph).

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 f90SQLBindCol 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

f90SQLBindCol 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, f90SQLBindCol 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.

Code Example

See examples in Chapter 5 and 6.

Related Subroutines