Using Block Cursors with APIs

API server cursors are not limited to fetching one row at a time; they can retrieve multiple rows in each fetch. 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. Cursors that have a rowset size greater than one are called block cursors. In the supported APIs, you can use the block cursors in these ways:

• ODBC

  • Set the statement attribute SQL_ATTR_ROWSET_SIZE to the size of the rowset.

  • Use column-wise or row-wise binding to bind the columns to arrays of variables to hold the data from the rows returned. The number of elements in each array is equal to the rowset size.

  • Each call to SQLFetchScroll fetches the number of rows set with SQL_ATTR_ROWSET_SIZE.

• OLE DB

  • Allocate an array of row handles and bind the columns represented by each handle to an array of variables to hold the column data.

  • Call IRowset::GetNextRows with the cRows parameter set to the number of handles in the row handle array and the address of the array of row handles in the prghRows parameter.

• ADO

  • Dim a variant to hold the data for the number of rows you want to retrieve.

  • Optionally, position at the proper point in the cursor using the recordset methods Move, MoveFirst, MoveLast, MoveNext, and MovePrevious.

  • Call the recordset method GetRows with the array parameter holding the address of the variant to hold the rows and the Crows parameter holding the number of rows to return. Optionally, have the Start parameter indicate the fetch position in the recordset.