Creating SDF files from OLE DB

Following up on my last post I wrote a very simple Windows Mobile application that will create SDF files (versions 2.0, 3.0 and 3.5) using straight OLE DB calls. This sample application illustrates why it is so hard to develop against the OLE DB interfaces without a proper abstraction. Included in the ZIP file you will find the sqlce_ex.h header file that complements the original sqlce_oledb.h by adding the GUID declarations for the older databases and also by modifying some of the constants.

When Microsoft upgraded to SQL CE 3.0, some of the property ID constants were changed while retaining their names. This meant that the same property ID would have different values in 2.0 and 3.0. To overcome this, the extension header file undefines the conflicting properties and redefines them with new names that refer to the version:

#undef DBPROP_SSCE_COL_ROWGUID
#undef DBPROP_SSCE_MAXBUFFERSIZE
#undef DBPROP_SSCE_DBPASSWORD
#undef DBPROP_SSCE_ENCRYPTDATABASE
#undef DBPROP_SSCE_TEMPFILE_DIRECTORY

These are redefined as:


#define DBPROP_SSCE3_COL_ROWGUID 0x1F9L
#define DBPROP_SSCE3_MAXBUFFERSIZE 0x1FAL
#define DBPROP_SSCE3_DBPASSWORD 0x1FBL
#define DBPROP_SSCE3_ENCRYPTDATABASE 0x1FCL
#define DBPROP_SSCE3_TEMPFILE_DIRECTORY 0x1FEL

And:

#define DBPROP_SSCE2_COL_ROWGUID 0x69
#define DBPROP_SSCE2_MAXBUFFERSIZE 0x70
#define DBPROP_SSCE2_DBPASSWORD 0x71
#define DBPROP_SSCE2_ENCRYPTDATABASE 0x72
#define DBPROP_SSCE2_TEMPFILE_DIRECTORY 0x73

The aplication itself is a very simple WTL 8.0 dialog with just a few simple properties:

• File name
• Password (optional)
• Locale (optional)
• Encryption (behavior depends on engine version)
• Engine version

When the user presses the "Create" menu option the dialog calls the OnCreateSdf message handler where all the action takes place. As you can see, this is a piece of ugly code. Most of it involves managing the DBPROPSET array and the contained DBPROP arrays, something that you will see in a large number of OLE DB method calls.

A DBPROPSET array contains an array of DBPROP arrays. Each DBPROP structure contains the value of a single OLE DB property (like file name, locale, encryption mode...) and these are grouped in property sets. Each property set has its own unique ID, like DBPROPSET_DBINIT (generic OLE DB database initialization properties) and DBPROPSET_SSCE_DBINIT (SQL CE-specific initialization properties). This scheme only works correctly if you put each property in its own specific set and you must read the documentation to learn which goes where. In our case, we have:

DBPROPSET_DBINIT:

• DBPROP_INIT_DATASOURCE - database file name
• DBPROP_INIT_LCID - database locale ID

DBPROPSET_SSCE_DBINIT:

• DBPROP_SSCE_ENCRYPTIONMODE - database encryption mode (for 3.5 only)
• DBPROP_SSCE2_ENCRYPTDATABASE - database encryption status (for 2.0 only)
• DBPROP_SSCE3_ENCRYPTDATABASE - database encryption status (for 3.0 only - deprecated in 3.5)
• DBPROP_SSCE2_DBPASSWORD - database password (for 2.0 only)
• DBPROP_SSCE3_DBPASSWORD - database password (for 3.0 / 3.5 only)

After filling up the property arrays, we can call the IDBDataSourceAdmin::CreateDataSource method and, if all properties are correctly set, we sould get a brand new SDF file.

At the end, don't forget to cleanup all the resources you have allocated. This includes the input properties and any output data. Also, don't forget to release the used OLE DB interfaces!

Before you make this code work on a Windows Mobile device, you must install at least one of the SQL Compact engines. For versions 3.0 and 3.5 make sure you also install the replication cab file because this is how the OLE DB provider is installed. Needless to say, you can have all three engines installed on the same device.

What's next? First, we need a better abstraction for handling all the property values. Second, we need a better way of retrieving error information whenever one pops up. I will start the OLE DB client library by implementing these two abstractions.