Data fetched from the data source is returned to the application in variables the application has allocated for this purpose. Before this can be done, the application must associate, or bind, these variables to the columns of the result set. Conceptually, this process is the same as the one described in the section that dealt with parameterized queries (Queries with parameter).
When the application binds a variable to a result set column, it describes that variable (i.e. address, data type, and so on) to the driver. The driver stores this information in the structure it maintains for that statement, and uses the information to return the value from the column when the row is fetched.
Applications can bind as many or as few columns of the result set as they choose. When a row of data is fetched, the driver returns the data for the bound columns to the application. Whether the application binds all of the columns in the result set depends on the application. For example, applications that generate reports usually have a fixed format. These applications create a result set containing all of the columns used in the report, then bind and retrieve the data for all of these columns. Applications that display screens full of data sometimes allow the user to decide which columns to display. Such applications create a result set containing all columns the user might want, but bind and retrieve the data only for those columns chosen by the user.
Data can be retrieved from unbound columns by calling f90SQLGetData. This is commonly called to retrieve long data, which often exceeds the length of a single buffer and must be retrieved in parts.
Columns can be bound at any time, even after rows have been fetched. However, the new bindings do not take effect until the next time a row is fetched. They are not applied to data from rows already fetched.
A variable remains bound to a column until a different variable is bound to that column. The column is unbound by calling f90SQLBindCol with a null pointer as the variable’s address. Calling f90SQLFreeStmt with the SQL_UNBIND option also unbinds all columns at once. The same happens when the statement is released. For this reason, the application must be sure that all bound variables remain valid as long as they are bound (see Example 4.5).
Because column bindings are just information associated with the statement structure, they can be set in any order. They are also independent of the result set. For example, suppose an application binds the columns of the result set generated by the following SQL statement:
SELECT *
FROM Orders
If the application then executes the SQL statement:
SELECT *
FROM Lines
on the same statement handle, the column bindings for the first result set are still in effect because those are the bindings stored in the statement structure. In most cases, this is a poor programming practice and should be avoided. Instead, the application should call f90SQLFreeStmt with the SQL_UNBIND option to unbind all the old columns and then bind new ones.
The application binds columns by calling f90SQLBindCol. This subroutine binds one column at a time. With it, the application specifies:
When f90SQLBindCol is called, the driver associates this information with the statement. When each row of data is fetched, it uses the information to place the data for each column in the bound application variables. A typical call to f90SQLBindCol is preformed as follows:
| call f90SQLBindCol | (StmtHandle, ColumnNumber, TargetType,{TargetVar, | TargetVarPtr, TargetVarBufferLength,} StrLen_or_IndPtr, iRet) |
where:
StmtHandle is a statement handle.
ColumnNumber indicates the column number of the result set to be bound to the variable passed in TargetVar. This argument must be an integer of the kind defined by constant SQLUSMALLINT_KIND.
TargetType is the Fortran type of variable TargetVar. f90SQL defines constants that identify the different types. For example, constant SQL_F_CHAR tells f90SQLBindCol that variable TargetVar is a character variable. For a complete list of Fortran types identifiers, see Table 4.3 in Chapter 4.
TargetVar is the variable that will be bound to the column of the recordset.
TargetVarPtr and TargetVarBufferLength: instead of TargetVar, the user may want to pass the address of the buffer that stores the parameter value (ParameterValuePtr) and the length of this buffer (TargetVarBufferLength).
[Note: A call to f90SQLBindCols must pass either TargetVar or the argument pair TargetVarPtr, TargetVarBufferLength]
StrLen_or_IndPtr is a pointer (i.e. the address) to an integer variable (its kind is given by the constant SQLINTEGER_KIND) that will store the length/indicator value for the buffer. This variable will contain the length of the data stored in variable TargetVar when the rows of the recordset are fetched. In Fortran, to pass a pointer you use the function loc(var). If your are not interested in this value, you can pass a null pointer (a 0 or the constant f90SQL_NULL_PTR).
Several of the examples in the previous sections have shown how to do basic column binding using f90SQLBindCol (see Examples 4.4, 5.2 and 5.4)