One of the most common tasks performed by an application is to issue a query statement, and then fetch each row of the result set into application variables that have been bound using SQLBindCol(). If the application requires that each column or each row of the result set be stored in an array, each fetch must be followed by either a data copy operation or a new set of SQLBindCol() calls to assign new storage areas for the next fetch.
Alternatively, applications can eliminate the overhead of extra data copies or extra SQLBindCol() calls by retrieving multiple rows of data (called a rowset) at a time into an array.
| Note: | A third method of reducing overhead, which can be used on its own or with arrays, is to specify a binding offset. 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 offset binding, and is described in Column Binding Offsets. |
When retrieving a result set into an array, SQLBindCol() is also used to assign storage for application array variables. By default, the binding of rows is in column-wise fashion: this is symmetrical to using SQLBindParameter() to bind arrays of input parameter values as described in the previous section.
Figure 12. Column-Wise Binding
|
 |
SQLFetchScroll() supports scrollable cursors, the ability to move forwards and backwards from any position in the result set. This can be used with both column wise and row wise binding. See Scrollable Cursors for more information.
Figure 12 is a logical view of column-wise binding. The right side of Figure 14 shows the function flows for column-wise retrieval.
To specify column-wise array retrieval, the application calls SQLSetStmtAttr() with the SQL_ATTR_ROW_ARRAY_SIZE attribute to indicate how many rows to retrieve at a time. When the value of the SQL_ATTR_ROW_ARRAY_SIZE attribute is greater than 1, DB2 CLI knows to treat the deferred output data pointer and length pointer as pointers to arrays of data and length rather than to one single element of data and length of a result set column.
The application then calls SQLFetchScroll() to retrieve the data. When returning data, DB2 CLI uses the maximum buffer size argument (BufferLength) on SQLBindCol() to determine where to store successive rows of data in the array; the number of bytes available for return for each element is stored in the deferred length array. If the number of rows in the result set is greater than the SQL_ATTR_ROW_ARRAY_SIZE attribute value, multiple calls to SQLFetchScroll() are required to retrieve all the rows.
The application can also do row-wise binding which associates an entire row of the result set with a structure. In this case the rowset is retrieved into an array of structures, each of which holds the data in one row and the associated length fields. Figure 13 gives a pictorial view of row-wise binding.
To perform row-wise array retrieval, the application needs to call SQLSetStmtAttr() with the SQL_ATTR_ROW_ARRAY_SIZE attribute to indicate how many rows to retrieve at a time. In addition, it must call SQLSetStmtAttr() with the SQL_ATTR_ROW_BIND_TYPE attribute value set to the size of the structure to which the result columns will be bound. DB2 CLI treats the deferred output data pointer of SQLBindCol() as the address of the data field for the column in the first element of the array of these structures. It treats the deferred output length pointer as the address of the associated length field of the column.
The application then calls SQLFetchScroll() to retrieve the data. When returning data, DB2 CLI uses the structure size provided with the SQL_ATTR_ROW_BIND_TYPE attribute to determine where to store successive rows in the array of structures.
Figure 14 shows the required functions for each method. The left side shows n rows being selected, and retrieved one row at a time into m application variables. The right side shows the same n rows being selected, and retrieved directly into an array.
 |
When an application needs to change bindings (for a subsequent fetch for example) it can call SQLBindCol() a second time. This will change the buffer address and length/indicator pointer used.
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.
To make use of column binding offsets, an application would follow these steps:
The statement attribute SQL_ATTR_ROW_BIND_OFFSET_PTR points to the address of an SQLINTEGER buffer where the offset will be stored. This address must remain valid until the cursor is closed.
This extra level of indirection enables the use of a single memory variable to store the offset for multiple sets of bindings on different statement handles. The application need only set this one memory variable and all of the offsets will be changed.
The offset value is always added to the memory location of the originally bound values. This sum must point to a valid memory address.
See the section Column Binding Offsets in SQLBindCol() for more information.
/* From the CLI sample PCALL.C */
/* ... */
/* print result sets, if any */
do
{ rc = StmtResultPrint( hstmt1);
}
while( SQLMoreResults( hstmt1) == SQL_SUCCESS);