When retrieving data, you may want
to retrieve more than one row at a time and store the data in an array.
Instead of fetching and copying each row of data into an array, or
binding to new storage areas, you can retrieve multiple rows of data
at once using column-wise binding. Column-wise binding is the default
row-binding method whereby each data value and its length is stored
in an array.
Before you begin
Before using column-wise binding to retrieve data into arrays,
ensure you have initialized your CLI application.
Procedure
To retrieve data using column-wise binding:
- Allocate an array of the appropriate data type for each
column data value. This array will hold the retrieved data value.
- Allocate an array of SQLINTEGER for each column. Each
array will store the length of each column's data value.
- Specify that column-wise array retrieval will be used by
setting the SQL_ATTR_ROW_BIND_TYPE statement attribute to SQL_BIND_BY_COLUMN
using
SQLSetStmtAttr().
- Specify the number of rows that will be retrieved by setting
the SQL_ATTR_ROW_ARRAY_SIZE statement attribute using
SQLSetStmtAttr().
When the value of the SQL_ATTR_ROW_ARRAY_SIZE attribute
is greater than 1, CLI treats
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.
- Prepare and execute the SQL statement used to retrieve
the data.
- Bind each array to its column by calling
SQLBindCol() for
each column.
- Retrieve the data by calling
SQLFetch() or SQLFetchScroll().
When returning data, CLI uses
the maximum buffer size argument (BufferLength)
of 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.