Cursor Rowset Size

ODBC cursors are not limited to fetching one row at a time; they can retrieve multiple rows in each call to SQLFetch or SQLFetchScroll. When working with a client/server database such as Microsoft® SQL Server™, it is more efficient to fetch several rows at a time. The number of rows returned on a fetch is called the rowset size and is specified using the SQL_ATTR_ROW_ARRAY_SIZE of SQLSetStmtAttr. Cursors whose rowset size is greater than 1 are called block cursors.

There are two options for binding result set columns for block cursors:

• Column-wise binding

  Each column is bound to an array of variables. Each array has the same number of elements as the rowset size.

• Row-wise binding

  An array is built using structures that hold the data and indicators for all the columns in a row. The array has the same number of structures as the rowset size.

When either column-wise or row-wise binding is used, each call to SQLFetch or SQLFetchScroll fills the bound arrays with data from the rowset retrieved.

SQLGetData can also be used to retrieve column data from a block cursor. Because SQLGetData works one row at a time, SQLSetPos must be called to set a specific row in the rowset as the current row before calling SQLGetData.

The SQL Server ODBC driver offers an optimization using rowsets to quickly retrieve an entire result set. To use this optimization, set the cursor attributes to their defaults (forward-only, read-only, rowset size = 1) at the time SQLExecDirect or SQLExecute is called. The ODBC driver sets up a default result set, which is more efficient than server cursors when only transferring results to the client without scrolling. After the statement has been executed, increase the rowset size and use either column-wise or row-wise binding. This allows SQL Server to use a default result set to efficiently send result rows to the client, while the ODBC driver continuously pulls rows from the network buffers on the client.

See Also