Result set retrieval into arrays in CLI applications

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 by using the SQLBindCol() function. If the application requires that each column or each row of a 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 resource usage of extra data copies or extra SQLBindCol() calls by retrieving multiple rows of data (called a rowset) at one time into an array.

Note: A third method of reducing resource usage, 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.

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 similar to using SQLBindParameter() to bind arrays of input parameter values. Figure 1 is a logical view of column-wise binding.

Figure 1. Column-wise binding
Column-wise binding

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 2 gives a pictorial view of row-wise binding.

Figure 2. Row-wise binding
Row-wise binding