OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point