Many applications spend a significant amount of time bringing data across the network. Part of this time is spent bringing the data across the network and part is spent on network overhead, such as the call made by the driver to request a row of data. The latter time can be reduced if the application makes efficient use of block, or fat, cursors, which can return more than one row at a time.
The rows returned in a single fetch with a block cursor are called the rowset. It is important not to confuse the rowset with the result set. The result set is maintained at the data source, while the rowset is maintained in application buffers. While the result set is fixed, the rowset is not.It changes position and contents each time a new set of rows is fetched. Just as a single-row cursor (such as the traditional SQL forward-only cursor) points to a current row, a block cursor points to a rowset, which can be thought of as "current rows."
To perform operations on a single row when multiple rows have been fetched, the application must first indicate which row is the current row. The current row is required by calls to f90SQLGetData and positioned update and delete statements. When a block cursor first returns a rowset, the current row is the first row of the rowset. To change the current row, the application calls f90SQLSetPos or f90SQLBulkOperations (to update by bookmark). Figure 5.2 shows the relationship of the result set, rowset, current row, rowset cursor, and block cursor.
Figure 5.2. Relationships among row set, record set, block cursor and cursor to current row.
A block cursor can or cannot be scrollable. For example, most of the work in a report application is spent retrieving and printing rows. Because of this, it will be faster with a forward-only, block cursor. It uses a forward-only cursor to avoid the expense of a scrollable cursor, and a block cursor to reduce the network traffic.
Because block cursors return multiple rows, applications that use them must bind a vector to each column instead of a single variable. These vectors are collectively known as the rowset buffers. Here are the two styles of binding:
As when the application binds single variables to columns, it calls f90SQLBindCol to bind vectors to columns. The only difference is that the addresses passed are vector addresses, not single variable addresses. The application sets the SQL_ATTR_ROW_BIND_TYPE statement attribute to specify whether it is using column-wise or row-wise binding. Whether to use column-wise or row-wise binding is largely a matter of application preference. Row-wise binding might correspond more closely to the application’s layout of data, in which case it would provide better performance.