Row-wise binding for array data

Row-wise binding associates an entire row of the result set with a structure. You retrieve a rowset that is bound in this manner into an array of structures. Each structure holds the data and associated length fields from an entire row. You use row-wise binding only to retrieve data, and not to send it.

The following figure gives a pictorial view of row-wise binding.

Figure 1. Row-wise binding
Begin figure summary. This figure shows an example result set with three columns and a structure that is used to retrieve this data. Detailed description available.

You must call SQLSetStmtAttr() to set the number of rows that you want to retrieve before you can call SQLBindCol().

To perform row-wise array retrieval, include the following procedure in your application:
  1. Call SQLSetStmtAttr() to indicate how many rows to retrieve at a time.

    If you plan to fetch rows with SQLExtendedFetch(), set the SQL_ATTR_ROWSET_SIZE attribute set to the number of rows that you want to retrieve with each fetch.

    If you plan to fetch rows with SQLFetchScroll(), set the SQL_ATTR_ROW_ARRAY_SIZE attribute set to the number of rows that you want to retrieve with each fetch.

  2. Call SQLSetStmtAttr() again with the SQL_ATTR_BIND_TYPE attribute value set to the size of the structure to which the result columns are bound. When Db2 ODBC returns data, it uses the value of the SQL_ATTR_BIND_TYPE attribute to determine where to store successive rows in the array of structures.
  3. Call SQLBindCol() to bind the array of structures to the result set. In this call, include the following argument values:
    • Point the rgbValue argument to the address of the element of the first structure in an array that is to receive data from the column that you specify with the icol argument.
    • For character and binary input data, specify the length, in bytes, of each element in the array that receives data in the input argument cbValueMax. (For other input data types, this argument is ignored.)
    • Optionally, point the pcbValue argument to the address of the element of the first structure in an array that is to receive the number of bytes that the column value for this bind occupies. Otherwise, set this value to NULL.
  4. Call SQLExtendedFetch() or SQLFetchScroll() to retrieve the result data into the array.

The following figure shows the required functions to return column-wise and row-wise bound data with SQLExtendedFetch(). In this figure, n is the value of the SQL_ATTR_ROWSET_SIZE attribute, and m is the number of columns in the result set. The left side of the figure shows how n rows are selected and retrieved one row at a time into m application variables where The right side of the figure shows how the same n rows are selected and retrieved directly into an array.

Figure 2. Array retrieval
Begin figure summary. This figure is a flowchart that compares retrieval of a result set one row at a time to retrieval of a result set directly into an array. Detailed description available.
When you perform array retrieval:
  • If you specify the value n for SQL_ATTR_ROWSET_SIZE (or SQL_ATTR_ROW_ARRAY_SIZE, for SQLFetchScroll()), you must retrieve the result set into an array of at least n elements. Otherwise, a memory overlay might occur.
  • To bind m columns to application variables or an array, you must always make m calls to SQLBindCol().
  • If the result set contains more rows than SQL_ATTR_ROWSET_SIZE or SQL_ATTR_ROW_ARRAY_SIZE specifies, you must make multiple calls to SQLExtendedFetch() or SQLFetchScroll() to retrieve all the rows in the result set. When you make multiple calls to SQLExtendedFetch() or SQLFetchScroll(), you must perform an operation between these calls to save the previously fetched data. These operations are listed in Retrieving a result set into an array.