SQL Compact 3.5 SP1 and OLE DB

Yesterday the SP1 of SQL Compact 3.5 was announced with some interesting additions like 64 bit support. I have been using SQL CE since version 1.0 (since 2002?) and always from native code. My early applications were written with ADOCE which was later deprecated when the Pocket PC 2003 platform was introduced. The only alternative left for native applications was to use straight OLE DB interfaces. Interestingly, the ATL OLE DB Consumer Templates were available on the original Pocket PC 2003 SDK, but did not compile correctly. My early work with this code (two header files, essentially) was to adapt it in order to make it work with eVC3 and eVC4. Finally, Microsoft completely dropped these files from the Windows CE and Windows Mobile SDKs and this sent a very clear signal (or so I think): don't use this code.

What can we do now? Using the OLE DB interfaces directly is a big pain because these are very low-level abstractions. We need something that elevates the abstraction to something of the ATL OLE DB Consumer Templates level. And this is just what I'm proposing to do (yes, I'm crazy).

The Consumer Templates are a very interesting and informative piece of code. The usual ATL templated classes are there and the classes model very closely all sorts of consumer objects that you may use. But they have one very interesting drawback. As I found out, you have to tweak the code under some very specific circumstances, when you don't know the exact outcome of a command execution, for example. These are not insurmountable and reflect the purpose of the class library: use a class template instantiation that is customized for your exact needs. I will use some of the Consumer Templates' concepts (not the code) in this new library which will be prmarily targeted to embedded and mobile devices. As a side effect, it should also be able to work in a desktop application.

Given this scope, the primary target of this new library is to develop applications for all versions of SQL Compact (SQL CE), namely 3.5, 3.0 and 2.0. Instead of writing SQL Compact-specific code only, this will be built in two layers: a generic OLE DB layer and a SQL Compact-specific layer built on top of the OLE DB layer.

So what do you need to get started? First of all you need the SQL Compact header file. For SQL Compact 3.5 you need the sqlce_oledb.h, while for SQL Compact 3.0 and 3.1 you need ssceoledb.h. These files contain both the OLE DB Interface, structure and constant declarations as well as the SQL Compact-specific GUIDs and constants. This means that, without tweaking, you musy have a header file for each version of SQL Compact. Fortunately this is not required and we may use just one header file to compile an application that will consume all versions of the database engine - a very nice feature indeed.

On my next post I will provide an add-on header file that will allow you to use sqlce_oledb.h to target all SQL Compact versions.