INF: Movement of Keyset in a Mixed Cursor

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

A mixed cursor is keyset-driven inside the keyset, but fully dynamic outside the keyset. This article discuss the movement of a keyset in this type of cursor.

MORE INFORMATION

By using the Microsoft Cursor APIs in dblib, you can create four different kinds of cursors: static, keyset-driven, dynamic, and mixed. A keysetdriven cursor keeps all the keys of the result set locally and the fetch buffer is smaller than the keyset. A dynamic cursor has the fetch buffer equal to the keyset. A mixed cursor has keyset that is larger than the fetch buffer, but smaller than the result set. Hence, it is keyset-driven inside the keyset, but fully dynamic outside of it.

While the fetch buffer can be moved by direct API calls (dbcursorfetch, for example), the movement of keyset is always implicit. In a fully keyset-driven cursor, the keyset does not move because it is equal to the result set. In a fully dynamic cursor, the keyset moves with the fetch buffer. The movement of keyset is more involved in a mixed cursor. For example, for a mixed cursor, if the result set size is 100, the keyset size is 20 and the fetch buffer is 10.

Also, assume that the fetch buffer starts from the third row in the keyset. Then, a call to dbcursorfetch with FETCH_NEXT option will return only the next eight rows (rows 13-20 in the keyset) because it is unable to span the keyset boundary. To move the keyset down, dbcursorfetch should be called with either FETCH_RANDOM or FETCH_RELATIVE option, and the rownum set to 13. This will fetch the next eight rows within the keyset. The next call to dbcursorfetch with the FETCH_NEXT option will result in moving the keyset down and fetching the two remaining rows. This is the only way that a keyset can move in case of a mixed cursor.