Using scrollable cursors

IRowset::GetNextRows supports scrollable cursors, which allows both forward and backward movement within the rowset. The rowset moves backward through the results when you specify negative values for the lRowsOffset or cRows parameters of GetNextRows.

The Adaptive Server OLE DB Provider supports the Static Insensitive scrollable cursor. It implements the IRowset::GetNextRows() method, which is a standard method defined in Microsoft Open Database Connectivity Software Development Kit Programmer’s Reference Volume 2, that is part of the MSDN library. Go to the Microsoft Web site for more information.

The OLE DB Provider supports the following scrolling types:

Next – returns the next row.
Prior – returns the prior row.
Relative n rows – returns the row, n rows from the current rowset.

Setting the UseCursor connection property

To determine whether client-side or server-side scrollable cursors are used, you must set the UseCursor property:

When the UseCursor connection property is set to 1, server-side scrollable cursors are used, if the Adaptive Server version is 15.0 or later. For earlier versions of the Adaptive Server, server-side scrollable cursors are not available.
When the UseCursor connection property is set to 0, client-side scrollable cursors (cached result sets) are used, regardless of the Adaptive Server version.

WARNING! Using client-side scrollable cursors is resource intensive.

Setting scrollable cursor attributes

You must set the following attributes to use scrollable cursors:

DBPROP_CANSCROLLBACKWARDS – if set to VARIANT_TRUE, the rowset allows the lRowsOffset parameter of GetNextRows to be negative.
DBPROP_CANFETCHBACKWARDS – if set to VARIANT_TRUE, the rowset will allow the cRows parameter of GetNextRows to be negative.

Executing scrollable cursors

Executing a scrollable cursor

Set the scrollable cursor properties on the rowset:

DBPROP RowsetProperties[2];
for(int i = 0; i < 2; i++) 
   VariantInit(&RowsetProperties[i].vValue);

RowsetProperties[0].dwPropertyID = DBPROP_CANFETCHBACKWARDS;
RowsetProperties[0].vValue.vt      = VT_BOOL;
RowsetProperties[0].vValue.boolVal = VARIANT_TRUE;
RowsetProperties[0].dwOptions      = DBPROPOPTIONS_REQUIRED;
RowsetProperties[0].colid          = DB_NULLID;
RowsetProperties[1].dwPropertyID   = DBPROP_CANSCROLLBACKWARDS;
RowsetProperties[1].vValue.vt      = VT_BOOL;
RowsetProperties[1].vValue.boolVal = VARIANT_TRUE;
RowsetProperties[1].dwOptions      = DBPROPOPTIONS_REQUIRED;
RowsetProperties[1].colid          = DB_NULLID;DBPROPSET rgRowsetPropSet[1];
rgRowsetPropSet[0].guidPropertySet = DBPROPSET_ROWSET;
rgRowsetPropSet[0].cProperties     = 2;
rgRowsetPropSet[0].rgProperties    = RowsetProperties;

Open the rowset:

IRowset* pIRowset = ds.OpenRowset("book", 1, rgRowsetPropSet);

Fetch the rows forward:

DBCOUNTITEM cRowsReturned;
HROW hRow[3];
HROW* pRows = hRow;
hr = pIRowset->GetNextRows(NULL, 0, 3, &cRowsReturned, &pRows);

Release the rows:

hr = pIRowset->ReleaseRows(cRowsReturned, pRows, NULL, NULL, NULL);

Fetch the rows backward:

DBCOUNTITEM cRowsReturned;
HROW hRow[3];
HROW* pRows = hRow;
hr = pIRowset->GetNextRows(NULL, 0, -3, &cRowsReturned, &pRows);

Release the rows:

hr = pIRowset->ReleaseRows(cRowsReturned, pRows, NULL, NULL, NULL);

Release the rowset:
```
pIRowset->Release()
```

Looking at results

To identify the results and the result set interpretation, after you execute a scrollable cursor, refer to the Microsoft MSDN library

Example of scrollable static insensitive cursor program

For an example of a scrollable, static-insensitive cursor program refer to “Executing scrollable cursors”.