Working with Cursors
Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) supports three types of cursors: base table, scrollable query, and forward-only. Each cursor type is different. Carefully choose your cursor in order for your application to work most effectively.
Base Table Cursors
Base table cursors are the lowest-level cursor available and are the only updatable cursors. These cursors work directly against the storage engine and are the fastest of all supported cursor types. These cursors support the ability to use bookmarks to position on rows. Indexes are supported to order the rows in a table, to enable seeking on particular values, and to restrict the rows based on a range of values within an index. Base table cursors can scroll forward or backward with minimal cost.
Base table cursors have dynamic membership, which means two cursors opened over the same table will immediately see insertions, deletions, and changes to the data (assuming both are in the same transaction scope). Because base table cursors are updatable, a client can use this cursor type to make changes to the underlying data.
Base table cursors cannot represent the results of a query. Instead, they reflect all the functionality available to a single table in the database engine. Results of queries, such as
SELECT * FROM tablename, are returned through one of the supported query result cursors.
To open a base table cursor in Microsoft ActiveX® Data Objects for Windows CE 3.1 (ADOCE), pass the adCmdTableDirect flag when the Recordset object is opened. If this flag is not passed, the cursor cannot be a base table cursor because ADOCE implicitly prepends
SELECT * FROM to the table name. Base table cursors used in ADOCE and SQL Server CE should use adOpenDynamic and adLockOptimistic, for example:
rs.Open "tablename", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Scrollable Query Cursors
Scrollable query cursors are the most functional query-result cursors. These cursors support scrolling backward and forward and using bookmarks to position on rows. They do not support updates or indexes. Scrollable cursors do not see external changes to the data; the results of the query are cached for the lifetime of the cursor. Although scrollable cursors are more functional than forward-only cursors, scrollable cursors are slower and use more memory. Clients should use scrollable cursors only if scrolling or bookmarking is necessary. SQL Server CE does not support updatable cursors from the query processor.
Scrollable cursors can use either adOpenStatic or adOpenKeyset. These cursors should use adLockReadOnly and not specify the adCmdTableDirect flag, for example:
rs.Open "SELECT * FROM tablename WHERE col1 > 2;", cn, adOpenStatic, adLockReadOnly
Forward-only cursors are only returned by the query processor and are the fastest query-based cursor. These cursors do not support bookmarks, scrolling backward, indexes, or updating. These cursors should be used in scenarios in which speed and memory footprint are most important and query results are required. Restarting these cursors is relatively expensive because the query must be run again.
Forward-only cursors should use adOpenForwardOnly and adLockOptimistic, and the adCmdTableDirect flag should not be used, for example:
rs.Open "SELECT * FROM tablename WHERE col1 > 2;", cn, adOpenForwardOnly, adLockOptimistic