Using the ATL OLE DB Consumer Templates

By Joao Paulo Figueira, March 10, 2004.
Print version

Introduction

This article describes how the ATL OLE DB Consumer Templates can be adapted and used on Pocket PC platforms. The information presented here applies to both the Pocket PC 2002 and 2003 SDKs. A working knowledge of the desktop OLE DB Consumer Templates is assumed - you won't learn them by reading this article.

Microsoft advertises OLE DB as the database access method of choice for C++ developers on the Pocket PC platform. Although there is also the ADOCE (3.0 and 3.1) alternative for Pocket PC developers, Microsoft will not support it on C++ applications, meaning that you will be left on your own if you use it. Also, ADOCE is built on top of OLE DB so one should expect a slower performance (not to mention the use of the dreaded VARIANT for data exchange).

The problem with using OLE DB on the Pocket PC is its apparent complexity. A good example of this complexity is the NorthwindOleDb sample found on the SQL CE 2.0 SDK. This sample application uses OLE DB directly, without any encapsulation whatsoever. A simple table insertion takes tens of lines of code to achieve. Local variables multiply as well as the chance of a programming error. Debugging such code is a nightmare, at best.

A framework is definitely needed in order to encapsulate all this complexity and make life easier for the developer. Fortunately, such framework already exists: the ATL OLE DB Consumer Template Library.

Availability

Although it is very popular on desktop implementations, the ATL OLE DB Consumer Templates Library has not been ported to the Pocket PC platforms. Strangely enough, these templates can still be found on the Pocket PC 2002 and 2003 Software Development Kits, seeming to imply that their use is allowed or even recommended by Microsoft. But this is not necessarily the case. If you open the wce.h header file (on the MFC includes folder), you will see an interesting definition on line 164:

And again, on line 198 (197 on the 2003 SDK):

This means that under MFC, the OLE DB header file will not be included. If you try to include it after including stdafx.h, the pre compiler test on top of oledb.h will fail:

Microsoft seems to be telling us not to use OLE DB with MFC. As we shall see there are some reasons not to use OLE DB and MFC, but there are also ways to put them to work together to great advantage.

In the next section, we will se what can be done to enable this library's use on the Pocket PC.

Enabling

There are a number of things that we need to do in order to enable the library use. First, we must be able to include the header files properly. Next, we will have to correct a few errors that prevent the files from being used. Finally, we will update the library with new classes to handle some fine details of Pocket PC database programming.

Including the OLE DB header files

The OLE DB header files provide the very basic declarations that allow your application to use the OLE DB interfaces and consequently the Consumer Templates. As I showed previously, the default MFC header files are prepared to prevent OLE DB usage. If you don't include these headers, the Consumer Templates will not compile.

My solution to this problem was to change the way how the stdafx.h header file is organized. The other alternative would be to change the wce.h header file, but I find this last solution far less maintainable. If you ever need to reinstall your development environment, you will lose all changes.

The application wizard automatically creates the stdafx.h header file for you. This file contains common inclusions and is also used by the compiler as the reference for precompiled headers. My changes were trivial: I included the OLE DB headers before the standard MFC headers (this file was generated by the 2002 SDK):

Note: This process is somewhat different under Windows CE .Net (Pocket PC 2003) because the oledb.dll file is not present on the system. There is a workaround for this specific situation (see below).

As you can see, all AFX-related includes occur after the OLE DB includes. This way, we can avoid the MFC prohibition.

After the AFX includes, you see a small block of ATL includes:

This prepares the path for the inclusion of the OLE DB Consumer template header file. The last line is what this chapter is all about:

Including the Consumer Templates

One of the reasons (I believe) why Microsoft does not want us to include the ATL Consumer Templates header file is because it will not compile under eVC3 or eVC4.

This file (atldbcli.h) relies on the standard COM interface declarations, namely the IUnknown interface, and this is the first difficulty we face when porting to the Pocket PC. The IUnknown interface declaration found on the Pocket PC SDK is different from its desktop counterpart. It is missing a method declaration that is abundantly used on the atldbcli.h file, so a successful compilation is impossible.

To check this, compare the unknwn.h header files on the desktop and Pocket PC versions (2002 and 2003). On the desktop, you can find the following declaration that is missing on the Pocket PC:

Faced with no alternative but to change the provided header files, I copied all of atldbcli.h into a new file (atldbcli_ce.h) where I started to make all the necessary changes and adaptations. This file is not included in this article due to Microsoft copyright restrictions. To circumvent this, I will describe all the changes I made in order to make the header file compile and work on a Pocket PC. By following these instructions, you will also be able to modify the header file.

Modifying the atldbcli.h Header File

Before you start, copy the atldbcli.h (found on the ATL include folder) to atldbcli_ce.h. This will allow you to make the necessary changes without modifying your SDK distribution. Furthermore, I advise you to place this new file in a different directory so it doesn't get deleted when and if you need to reinstall the development tools.

The first change you need to make is to replace all QueryInterface calls with the Pocket PC-compatible version. For instance, change this:

To this:

As you can see, you have to insert the interface IID as the first argument and add the cast on the second. I counted 35 replacements on the Pocket PC 2002 and 2003 SDK files. These files are essentially the same except for the copyright notice found on the top.

After this change your new header should compile correctly, but it will not work correctly.

Correcting CDynamicAccessor

There is a major defect that needs to be corrected when using the CDynamicAccessor class template. This class allows you to use an arbitrary row set and dynamically builds an accessor and data buffer for you (hence the name).

If you look closely to the code, you will see that ASCII strings are treated the same way as Unicode strings. This leads to a very nasty memory alignment problem on the Pocket PC. To correct this problem, replace this code (line 2285 on the 2002 SDK, line 2294 on the 2003 SDK) from this:

to this:

This way, the accessor will correctly allocate a Unicode string and avoid memory corruption and pointer alignment problems. This completes the corrections needed in order for the header file to be put to use. Now, let's add a few enhancements.

Enhancing CAccessorBase and CDynamicAccessor

One of the enhancements to these classes that I found useful is to keep track of the accessor buffer size. Once you know how large it is, you can create accessor buffers as you like and have CDynamicAccessor fill them with row data. This can be useful for buffering and caching.

First, declare a new member variable in CAccessorBase:

Now, initialize it on the constructor:

Now, we turn to CDynamicAccessor to set the variable whenever it is changed. Start with the Close method:

Now, on BindColumns:

We are done with changing this header file. Now, we will see how these classes can be used, and what enhancements are needed in order to make database development easier on the Pocket PC.

Note for Windows CE .Net (Pocket PC 2003)

Windows CE .Net does not include oledb.dll making it impossible to write OLE DB applications for this platform. Fortunately, Microsoft has published a Knowledge Base Article 825393 that explains how to work around it, and provides a header file to make everything work.

There are some small changes you will have to do on stdafx.h in order to accomodate this. This is illustrated on the sample application source code on the eVC4 version.

Using

Although they are now ready to use, we will need to add some more functionality to these classes in order to use them in a real-life situation: managing SQL CE 2.0 databases and their data.

SQL CE 2.0

The first extension we will write is a specialized connection object for SQL CE 2.0, by deriving a class from CDataSource:

If you want to specify additional properties to the data source, such as a password or a temporary file directory, you will need to add the appropriate properties (DBPROP_SSCE_DBPASSWORD, and DBPROP_SSCE_TEMPFILE_DIRECTORY). These symbols are defined in ssceoledb.h (distributed with SQL CE 2.0).

After opening a data source, a session must be created and opened. This object will be unique in the whole application since SQL CE will only support one open connection per database on a system wide basis. Here is a code sample:

This particular piece of code was designed to be executed at the very top of the application class' InitInstance method. The source and session objects have to be closed when the application closes and this can be done on the application's ExitInstance method:

Now the application is ready to start managing an SQL database. We begin by taking a look at how to execute SQL commands.

Executing Commands

Commands are executed through the instantiation of the CCommand class template. This template takes as arguments an accessor class and a row set class. There are a number of these on atldbcli.h like CDynamicAccessor and CRowset, but they may not necessarily fit the bill of our needs.

The CDynamicAccessor class will dynamically create an accessor for the underlying row set. This contrasts with CAssessor that requires a predefined data schema and a fixed structure to receive the data. We will see that although CDynamicAccessor is very convenient to use, it has a few shortcomings of its own, namely in its C++ interface.

The second class, CRowset, defines how data is inserted, retrieved and updated (in conjunction with the accessor class) as well as cursor and bookmark management. Once again, this class (the OLE DB interface it wraps) does not give us all the information we need, namely the row count.

Counting Row Sets: IRowsetPosition

Row set count information is required in a number of situations and, although you can access it using a specialized query, it would be very nice if we could calculate it as a by-product of executing a command. Fortunately there is a simple solution: the IRowsetPosition interface.

This interface can be provided by SQL CE 2.0 and it reports the row set count as well as the current cursor position. Unfortunately, there is no provision for this interface on the ATL OLE DB Consumer Templates, so we have to write our own. This is very simply done by deriving a class from the provided CRowset.

Following the style of atldbcli.h, I implemented the whole class inline:

Note that both Close and SetupOptionalRowsetInterfaces call the base class. The first is required in order to close the IRowset (and possibly IRowsetChange) interface. The second is called by the enclosing CCommand class template in order to instantiate all the required interfaces.

Sample: Executing a Command

After all this effort, let us finally execute a command. First, we declare a command object:

Now, we need to set its properties:

Finally, we open the command:

As you can see, it is pretty straightforward. What is now missing is the row count:

First, we check if the Open command succeeded. If it did, we retrieve the row set count and position the cursor on the first row. Now, we can start retrieving information from the row set using the CDynamicAccessor's GetValue family of methods.

Note that data cannot be updated by using an SQL SELECT command. Updating SQL CE data requires a chapter of its own.

DDL and DML Commands

The above sample shows you how to execute an SQL SELECT command. These commands will generally return a row set whose rows and column data can be accessed, but not changed (see below).

Managing an SQL Server database is not a simple matter of executing SELECT commands. You can also INSERT, DELETE and UPDATE data in tables. However, these commands, as well as the Data Definition Language (DDL) commands, will not return a row set for you to work with so it is unnecessary to specify an accessor or row set class. The Consumer Templates provide the CNoAccessor and CNoRowset for these exact purposes. A DDL command can be executed like this:

Updating Data

There are two ways to update data in an SQL CE database using the ATL OLE DB Consumer Templates: using an SQL DML (see above) command such as INSERT, UPDATE or DELETE or by using the row set class' methods like Insert and Delete.

This type of data updating uses the OLE DB interfaces directly and is, arguably, much more efficient and versatile than the DML approach. All we need to do is create an updateable row set and use CDynamicAccessor's GetValue and SetValue family of methods.

But this is exactly where we get into trouble: SQL CE will only allow us to update tables using the so-called "base table cursor". The logic is simple: we open the table, seek the required row and update it (this is obviously not necessary for insertions). Though simple it may seem, it is by no means simple to implement. Let's see why.

The ATL OLE DB Consumer Templates provide us with a class template to handle tables - CTable. This template operates in a manner similar to CCommand, but it does not work with SQL commands: it works directly with individual tables, so the Open method receives as parameter a table name. A possible implementation is:

So, all we need to do is seek a specific row. Here we hit a brick wall: there is no way of seeking a record using CTable. There is no interface available for us to seek a specific row.

Actually, this is not so bad. As a matter of fact, OLE DB does provide us the IRowsetIndex interface with this specific purpose, but before we can start using it we have to do two things. First, we need to derive a class from CRowset (like we did in CRowsetPosition) to support this interface. Second, we need to change the CTable class template so that it accepts an index that we can use to seek, rather than doing a slow full table scan.

Implementing CRowsetIndex

This class is a bit more complex than what we have seen on this article. The first design choice was to decide from which row set class to derive (CRowset, CRowsetPosition). Having reached no conclusion whatsoever, I decided that the class user should make that choice herself, and so I chose to implement the class as a template.

So, by instantiating the template parameter, the class user specifies from which row set class to derive. The full class implementation is provided in the accompanying code as it is quite long to fully reproduce here. There are a couple of things that need to be noted.

In order to use the IRowsetIndex interface, we need to create an accessor with an exact mapping of the requested index. This accessor is created on the BuildBinding method which is called during the setup phase.

Seeking a row is a two-step process. First, you specify the values you want to match with the SeekValue method. This method takes a column name or index and a value. When all the values are set, you call SeekAndGet to make the actual positioning and retrieve the row set data to the main accessor. This process is illustrated below.

Implementing CIndexTable

As I said before, we need to change the CTable class template in order for it to support indexed searches. We do this through a new class template: CIndexTable.

Implementing CIndexTable is quite straightforward: Open atldbcli_ce.h and make a full copy of CTable's definition. Keep this copy on atldbcli_ce.h.

Now, make it look like this:

Finally, let's see how these classes work together to update data.

CRowset Data Update Primitives

So far, we have customized the ATL OLE DB Consumer Templates for use on a Pocket PC. After changing code and adding new convenience classes, let us finally see how these classes work together to achieve the very basic purpose of a database application: accessing and updating data.

For the sake of this discussion, I assume that we are working on a table named Table that has a unique integer index named pk_id on a column named id.

Retrieving Row Data

We start by retrieving row data from the table by positioning the cursor to a specific location, given by the value of the id column. The sample code below shows how this is achieved. First, the table is opened with all the necessary properties. Next, the SeekValue method is used to specify the id column value we want to search. The actual positioning is made by calling SeekAndGet. This method takes as parameter the number of columns in the index accessor that are used to perform the search: one in our case. If this method succeeds, the cursor will be positioned on the desired row and the row set accessor buffer will have the row data.

Updating Row Data

Updating data is very similar, but you need a slightly different set of properties:

The seeking sequence is slightly different because we do not necessarily need to retrieve the row data. After calling SeekValue, use Seek to position the cursor.

Make sure that all columns have their statuses set. My approach to this is to set them all to DBSTATUS_S_IGNORE. Here is a way to do it:

When you set one column value also set its status to DBSTATUS_S_OK. This way you make sure that the data provider will only set the values you want (this improvement is made on the CSmartAccessor class - see below).

Inserting a New Row

Inserting a new row in a table is also a simple operation. After opening the table, set all columns' statuses to DBSTATUS_S_IGNORE. This is especially important if you are inserting a row on a table that has an identity column. These columns are automatically incremented so you should not try to set their value. Leave that chore to the data provider.

After this, data can be set using the SetValue family of methods. Finally, call Insert to insert the new row on the table.

Deleting a Row

To delete an existing row, we must fist locate it using an index which is essentially the same procedure we used for loading a row. After successfully locating the row, we call Delete to remove it from the table. Here is some sample code:

As you can now see, these primitives are not difficult to use and their complexity is not greater than using ADOCE and have the obvious advantage of being faster. We now turn to something that was promised at the beginning of the article: enhancing CDynamicAccessor.

Improving on CDynamicAccessor

After using CDynamicAccessor for a while, you will bump into some of its shortcomings, namely how data transfer is made between the accessor buffer and your program's variables. This class provides template methods named GetValue and SetValue for this purpose. Columns are accessed via name or index (generally one-based) and specialization is achieved through the second parameter data type. Although this is convenient for most uses, it is inconvenient under two circumstances: strings and implicit data conversions. The problem is that both methods assume that you are setting or retrieving data in exactly the same binary format as it is represented on the accessor buffer.

If you are using CStrings to store column data you cannot use neither GetValue nor SetValue because the accessor stores strings as flat wide char arrays. One possible solution is:

The nColumn parameter is the internal column number that can be calculated either through TranslateColumnNo (translates column numbers) or GetInternalColumnNo (converts a column name into an internal column number).

Implicit type conversions are also impossible using either GetValue or SetValue because they assume you know the exact binary format of the data you are setting or getting from accessor buffer.

In order to circumvent these shortcomings I created CSmartAccessor, a class derived from CDynamicAccessor. This class is available on the sample application and may not have all the set and get methods you would expect, but it is nevertheless a good roadmap to build your own.

Sample Application

The sample application manages the Customers table of the Northwind database. This table is imported from the desktop SQL Server version using RDA.

There are two versions of the sample application: one for Pocket PC 2002 and another for Pocket PC 2003. This is due to the lack of direct support in Pocket PC 2003 for OLE DB.

The application manages a subset of the Customers table. It allows you to create, edit and delete customers using a context menu. The code illustrates the basic techniques for managing SQL CE databases and was tested on a Pocket PC 2002 device and on the Pocket PC 2003 emulator.

The sample code can be downloaded on the following links:

Embedded Visual C++ 3 project (88K)

Embedded Visual C++ 4 SP2 project (83K)

SQL Server CE 2.0 Database (72K)

Related resources:

• Section: Databases
• Article: INF: Building OLE DB Applications for SQL Server CE and Windows CE .NET
• Article: SQL Server CE Home

Discuss