Seek and you shall find

I recently found myself involved in a prototype Windows Mobile mapping application using the Virtual Earth tile system. Downloading the 256 x 256 bitmaps to a mobile device through a GPRS connection is slow and a bit painful, so I thought about creating an external cache mechanism. This would temporarily store the bitmaps for future use and, instead of writing the bitmaps to the file system I thought about using SQL Compact. There are a few advantages for using a database instead of the file system: you store all the bitmaps in a single file and you get the added benefit of easier maintenance (especially when deleting outdated bitmaps to clear the cache). So I looked back to the OLE DB library I have been working with for some help but I found a major whole (yes, there are more, I know!): there was no support for IRowsetIndex::Seek. Why do I need to use this method when I can equally retrieve individual records using a plain SELECT command? The answer is speed. When you go through the SQL Compact Query Processor your application pays a performance penalty that may be avoided (with varying degrees of effort). In this case I wanted to retrieve a single row from a table using an index (no fancy JOIN or GROUP BY clauses) so using a base table is very appropriate, performance-wise.

The idea is very simple: open a base table cursor and specify the index to use; seek using the required key values and retrieve the row data (if it's there). As I said, you can do this using SQL but there is performance penalty that I did not want to pay, especially when fast painting is required. My major problem was that there was no support for this in the previous version of the OLD DB library, so I had to implement it.

Implementing the IRowsetIndex::Seek supporting code implies adding an extra column binding code (similar to the one I wrote for the columns). In fact, you need an extra IAccessor object to bind the key columns as well as all the other supporting data structures. After a few hours worth of work, I managed to develop a working version of the code that I'm publishing here. If you look at the CRowset class implementation you will see how its complexity increased to a point where some new abstractions are just crying out to be created...

When using this code, remember to:

• Add the DBPROP_IRowsetIndex property (set to true) to the rowset property set;
• Call CRowset::SetKeyValue for each one of the index key values: ordinals start at one;
• To seek to the requested row, call CRowset::Seek specifying the number of valid key columns (the number of valid values set with CRowset::SetKeyValue).
  

If you get an S_OK as return value, the row was found and the data loaded into the row and ready to use.

Sample code: OleDbClientLib3.zip

EDIT: The link has been fixed.