Tuesday, November 18, 2008

Implementing SQL Compact Command Parameter BLOBs

After much ramblings, tests and misconceptions, I finally closed the chapter on SQL Compact command parameters. The sample code (see below) illustrates how to use a BLOB command parameter using the "variable length" binding buffer. Whenever the total size of the columns exceeds the allocated binding buffer size, the binding process is repeated increasing the binding buffer size. Although this is not an optimal solution (being able to use storage objects for this purpose would be much better), it frees you from having to specify the maximum size for BLOB parameters - the code calculates this for you.

Before you can execute a command with parameters, you must prepare it by calling the Prepare method (failing to do so will cause a run-time error). Here the code determines the number of existing parameters and allocates memory for a "user buffer". This user buffer is an array of CDbValue objects, a VARIANT-like class whose sole purpose is to store parameter values. When the user sets a parameter it is first written to one of these objects and upon execution it is copied to the data buffer. Meanwhile, the code determines if the new set of parameters on the CDbValue array requires rebinding, does so if needed and finally copies the values to the data buffer. This way you just have to set the parameters and execute the command (see the sample code).

As a side-effect of all these travails, I ended up creating a new class - CDbValueRef - from which CDbValue derives that I use as the "engine" that sets and gets data from various types, performing the necessary data type conversions. As you can now see, the column data access on the CRowset class is performed through an array of such objects that directly reference the bound data buffer, and they know how to handle storage object BLOBs. Now you can access your CRowset data using a type-safe interface.

Sample: InsertBlob.zip (152 KB)


Murph said...

Great work on all of your SQL Mobile articles and blogs. I am looking at your recent blogs about SQL/OLE DB and wonder how that fits in with your series of "ATL OLE DB Consumer Templates" articles you wrote for SQLCE v2 and SQL Mobile 2005? I have an app that uses the code from your ATL article and I am looking into updating from evc4/SQL Mobile 2005 to VC08/SQL Server Compact 3.5. Any hints on what would be easiest? I am not sure if the ATL code will still work with VS08 and SQL Compact (I am guessing it does not). Just looking for a link to one of your blogs as the best place to start if I need to replace the existing ATL code. Thanks and I really appreciate all of the great information you provided over the past years about SQL Mobile!

João Paulo Figueira said...

I started writing this series of blog posts with the purpose of replacing the ATL OLE DB Consumer Templates with a set of open source and freely distributable classes. The "problem" with the Consumer Templates is that they are no longer distributed on the Windows Mobile SDKs so there is no easy alternative for native code developers to access SQL Compact databases. You can "steal" the desktop headers and adapt them for your mobile / embedded applications but I'm not sure if this breaks Microsoft's licensing.

I'm also trying to make these classes very easy to use, taking some hints from the managed ADO .NET model. One of the design decisions I made was to separate the table and command classes from the resulting rowset (the Consumer Templates actually mixes them together, which sometimes makes our life harder).

Want to replace the ATL code? Well, I would say that the code is not finished yet (download the latest sample and have a look at the OleDbClient project). All the schema enumeration code, transaction support and a few other odds and ends are still missing. But if you want to start using this right away, please take a look at the samples I have been publishing. You will see that I retained the data source and session objects from the Consumer Templates (opening a connection is done pretty much in the same way). Then you can create your command or table objects, open them and retrieve the data using the rowset object.

Finally, thank you very much for your kind words!