SQL Compact's OLE DB limitations

There are a few bits of information that we must keep in mind when developing OLE DB applications for SQL Compact. I will briefly describe here what I believe to be the most important limitations and their implications.

Rowset limitations
SQL Compact's OLE DB provider returns only one row handle through IRowset::GetNextRows. This is not a dramatic limitation, but do keep it in mind.

Binding limitations
You cannot bind columns by reference, only by value. What does this mean? It means that you can only create bindings that have enough allocation for the bound data sizes. So how do we handle BLOBs? On desktop providers (like SQL Server's and Access) you can bind a BLOB column through a pointer to the provider memory. The provider allocates enough storage for the BLOB data and passes back a pointer that the consumer application later releases. This option is not available with SQL Compact, so a different approach is provided: structured storage. Instead of returning a pointer to the BLOB data, SQL Compact's provider returns a pointer to either an ISequentialStream or ILockBytes interface (the client application decides which). The major difference between the two is that the first provides sequential access while the second provides random access to the BLOB data. This is a bit of a pain, but gets worse.

Accessor limitations
Only one storage object can be open at a time (see DBPROP_MULTIPLESTORAGEOBJECTS). So what happens if you have more than one BLOB column to bind to at the same time? You can actually bind more than one BLOB column but you cannot get and set data to more than one BLOB column at the time: you have to get each storage object, use it and dispose of it. This means that you cannot read the whole row at once...

The trick is to create more than one accesor handle per row, binding all the non-BLOB columns with the first handle and creating an extra accessor handle per BLOB column. Reading and writing row data now becomes a multi-step process, and for that reason you must make sure that you use delayed update mode (use DBPROP_IRowsetUpdate).

Confusing? Things will become clearer with some code...