Foreword
This article was published for the first time on the Pocket PC Developer Network.
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:
#define _AFX_NO_OLEDB_SUPPORT
And again, on line 198 (197 on the 2003 SDK):
#define __oledb_h__
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:
#ifndef __oledb_h__
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 see 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).
#if !defined(AFX_STDAFX_H__...
#define AFX_STDAFX_H__...
#if _MSC_VER >= 1000
#pragma once
#endif
#if (_WIN32_WCE >= 200)
#error This project does not support...
#endif
#if (_WIN32_WCE <= 211)
#error This project can not be built...
#endif
#define OLEDBVER 0x0210
#define DBINITCONSTANTS
#include <OLEDB.H>
#include <OLEDBERR.H>
#include <COGUID.H>
#include <SSCEOLEDB.H>
#define VC_EXTRALEAN
#include <AFXWIN.H>
#include <AFXEXT.H>
#if defined(_AFXDLL)
#include <AFXDTCTL.H>
#endif
#ifndef _AFX_NO_AFXCMN_SUPPORT
#include <AFXCMN.H>
#endif
#include <AFXPRIV.H>
#include <ATLBASE.H>
extern CComModule _Module;
#include <ATLCOM.H>
#include "atldbcli_ce.h"
#endif
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:
#include <ATLBASE.H>
extern CComModule _Module;
#include <ATLCOM.H>
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:
#include "atldbcli_ce.h"
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:
template <CLASS Q>
HRESULT STDMETHODCALLTYPE QueryInterface(Q** pp)
{
return QueryInterface(__uuidof(Q), (void **)pp);
}
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:
HRESULT hr = pUnk->QueryInterface(&spSupportErrorInfo);
to this:
HRESULT hr = pUnk->QueryInterface(IID_ISupportErrorInfo,
(void**)&spSupportErrorInfo);
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:
if (m_pColumnInfo[i].wType == DBTYPE_STR ||
m_pColumnInfo[i].wType == DBTYPE_WSTR)
m_pColumnInfo[i].ulColumnSize += 1;
to this:
if (m_pColumnInfo[i].wType == DBTYPE_STR ||
m_pColumnInfo[i].wType == DBTYPE_WSTR)
m_pColumnInfo[i].ulColumnSize += 1;
if(m_pColumnInfo[i].wType == DBTYPE_WSTR)
m_pColumnInfo[i].ulColumnSize *= sizeof(wchar_t);
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:
ULONG m_nBufferSize;
Now, initialize it on the constructor:
CAccessorBase()
{
m_pAccessorInfo = NULL;
m_nAccessors = 0;
m_pBuffer = NULL;
m_nBufferSize = 0;
}
Now, we turn to CDynamicAccessor to set the variable whenever it is changed. Start with the Close method:
void Close()
{
.
.
.
m_nBufferSize = 0;
CAccessorBase::Close();
}
Now, on BindColumns:
HRESULT BindColumns(IUnknown* pUnk)
{
.
.
.
m_pBuffer = NULL;
ATLTRY(m_pBuffer = new BYTE[nOffset]);
if (m_pBuffer == NULL)
{
m_nBufferSize = 0;
delete [] pBinding;
return E_OUTOFMEMORY;
}
m_nBufferSize = nOffset; .
.
.
}
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 accommodate 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:
HRESULT CSqlCeDataSource::Open(LPCTSTR pszDbFile)
{
CDBPropSet propset(DBPROPSET_DBINIT);
propset.AddProperty(DBPROP_INIT_DATASOURCE, pszDbFile);
return CDataSource::Open(CLSID_SQLSERVERCE_2_0, &propset);
}
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:
if(!AfxOleInit())
return FALSE;
hr = g_oleDbSource.Open(_T("\\database.sdf"));
if(FAILED(hr))
{
AfxMessageBox(_T("Failed to open the data source"));
return FALSE;
}
hr = g_oleDbSession.Open(g_oleDbSource);
if(FAILED(hr))
{
AfxMessageBox(_T("Failed to open the session"));
return FALSE;
}
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:
g_oleDbSession.Close();
g_oleDbSource.Close();
AfxOleTerm();
Now the application is ready to start managing a 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:
class CRowsetPosition : public CRowset
{
public:
~CRowsetPosition()
{
CRowset::~CRowset();
Close();
}
void Close()
{
CRowset::Close();
if(m_spRowsetPos != NULL)
m_spRowsetPos.Release();
}
HRESULT GetRecordCount(ULONG *pcRows,
HCHAPTER hChapter = DB_NULL_HCHAPTER)
{
ATLASSERT(m_spRowsetPos != NULL);
HRESULT hr = S_OK;
hr = m_spRowsetPos->GetRecordCount(hChapter, pcRows);
return hr;
}
HRESULT GetCurrentPosition(ULONG *pulPosition,
HCHAPTER hChapter = DB_NULL_HCHAPTER)
{
ATLASSERT(m_spRowsetPos != NULL);
HRESULT hr = S_OK;
hr = m_spRowsetPos->GetCurrentPosition(hChapter, pulPosition);
return hr;
}
void SetupOptionalRowsetInterfaces()
{
CRowset::SetupOptionalRowsetInterfaces();
if (m_spRowset != NULL)
{
m_spRowset->QueryInterface(
IID_IRowsetPosition,
(void**)&m_spRowsetPos);
}
}
CComPtr&IRowsetPosition& m_spRowsetPos;
};
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:
CCommand<CDYNAMICACCESSOR, CRowsetPosition> cmd;
Now, we need to set its properties:
CDBPropSet propSetCmd(DBPROPSET_ROWSET);
propSetCmd.AddProperty(DBPROP_BOOKMARKS, true);
propSetCmd.AddProperty(DBPROP_OWNUPDATEDELETE, false);
propSetCmd.AddProperty(DBPROP_OWNINSERT, false);
propSetCmd.AddProperty(DBPROP_OTHERUPDATEDELETE, false);
propSetCmd.AddProperty(DBPROP_OTHERINSERT, false);
propSetCmd.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetCmd.AddProperty(DBPROP_QUICKRESTART, true);
Finally, we open the command:
HRESULT hr;
CString strSelect = _T("SELECT * FROM Customer");
//
// Open the command
//
hr = cmd.Open(g_oleDbSession, strSelect, &propSetCmd);
As you can see, it is pretty straightforward. What is now missing is the row count:
ULONG nRows = 0;
if(hr == S_OK)
{
hr = cmd.GetRecordCount(&nRows);
cmd.MoveFirst();
}
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 a 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 a 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 a 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:
CCommand<CNOACCESSOR, CNoRowset> cmd;
HRESULT hr;
hr = cmd.Open(g_oleDbSession,
_T("CREATE UNIQUE INDEX ix ON Customer (CustID ASC)"));
if(FAILED(hr))
{
}
Updating Data
There are two ways to update data in a SQL CE database using the ATL OLE DB Consumer Templates: using a 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:
CTable<CDYNAMICACCESSOR, CRowset> tbl;
CDBPropSet propSetTbl(DBPROPSET_ROWSET);
HRESULT hr;
propSetTbl.AddProperty(DBPROP_BOOKMARKS, true);
propSetTbl.AddProperty(DBPROP_OWNUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OWNINSERT, true);
propSetTbl.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OTHERINSERT, true);
propSetTbl.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetTbl.AddProperty(DBPROP_QUICKRESTART, true);
propSetTbl.AddProperty(DBPROP_IRowsetChange, true);
propSetTbl.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_DELETE |
DBPROPVAL_UP_INSERT);
hr = tbl.Open(g_oleDbSession, _T("Table"), &propSetTbl);
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.
template <CLASS TBaseRowset="CRowset">
class CRowsetIndex : public TBaseRowset
{
...
}
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:
template <CLASS class="" TAccessor="CNoAccessor," TRowset="CRowset">
class CIndexTable :
public CAccessorRowset<TACCESSOR, TRowset>
{
public:
HRESULT Open(const CSession& session, LPCTSTR szTableName,
LPCTSTR szIndexName, DBPROPSET* pPropSet = NULL)
{
USES_CONVERSION;
DBID idTable,
idIndex;
idTable.eKind = DBKIND_NAME;
idTable.uName.pwszName = (LPOLESTR)T2COLE(szTableName);
idIndex.eKind = DBKIND_NAME;
idIndex.uName.pwszName = (LPOLESTR)T2COLE(szIndexName);
return Open(session, idTable, idIndex, pPropSet);
}
HRESULT Open(const CSession& session, DBID& dbidTbl,
DBID& dbidIdx, DBPROPSET* pPropSet = NULL)
{
ATLASSERT(session.m_spOpenRowset != NULL);
HRESULT hr;
hr = session.m_spOpenRowset->OpenRowset(
NULL, &dbidTbl,
&dbidIdx,
GetIID(),
(pPropSet) ? 1 : 0,
pPropSet,
(IUnknown**)GetInterfacePtr());
if(SUCCEEDED(hr))
{
SetupOptionalRowsetInterfaces();
if (_OutputColumnsClass::HasOutputColumns())
hr = Bind();
}
return hr;
}
BOOL IsOpen()
{
return GetInterface() != NULL;
}
};
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.
CIndexTable<CDYNAMICACCESSOR, CRowsetIndex<CRowset> > tbl;
HRESULT hr;
CDBPropSet propSetTbl(DBPROPSET_ROWSET);
int nID = 1234; TCHAR szID[] = _T("id");
propSetTbl.AddProperty(DBPROP_BOOKMARKS, true);
propSetTbl.AddProperty(DBPROP_OWNUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OWNINSERT, true);
propSetTbl.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OTHERINSERT, true);
propSetTbl.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetTbl.AddProperty(DBPROP_QUICKRESTART, true);
propSetTbl.AddProperty(DBPROP_IRowsetIndex, true);
hr = tbl.Open(g_oleDbSession, _T("Table"), _T("pk_id"),
&propSetTbl);
if(hr == S_OK)
{
tbl.SeekValue(szID, nID);
hr = tbl.SeekAndGet(1);
if(hr == S_OK)
{
tbl.Get(m_szID, m_nID);
}
else
{
}
tbl.Close();
}
Updating Row Data
Updating data is very similar, but you need a slightly different set of properties:
propSetTbl.AddProperty(DBPROP_BOOKMARKS, true);
propSetTbl.AddProperty(DBPROP_OWNUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OWNINSERT, true);
propSetTbl.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OTHERINSERT, true);
propSetTbl.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetTbl.AddProperty(DBPROP_QUICKRESTART, true);
propSetTbl.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_DELETE |
DBPROPVAL_UP_INSERT);
propSetTbl.AddProperty(DBPROP_IRowsetIndex, true);
propSetTbl.AddProperty(DBPROP_IRowsetChange, true);
hr = tbl.Open(g_oleDbSession, _T("Table"), _T("pk_id"),
&propSetTbl);
if(hr == S_OK)
{
tbl.SeekValue(szID, nID);
hr = tbl.Seek(1);
if(hr == S_OK)
{
}
else
{
}
tbl.Close();
}
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:
ULONG i;
for(i = 0; i < tbl.m_nColumns; ++i)
tbl.SetStatus(i, DBSTATUS_S_IGNORE);
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.
HRESULT hr = E_FAIL;
CDBPropSet propSetTbl(DBPROPSET_ROWSET);
propSetTbl.AddProperty(DBPROP_BOOKMARKS, true);
propSetTbl.AddProperty(DBPROP_OWNUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OWNINSERT, true);
propSetTbl.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OTHERINSERT, true);
propSetTbl.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetTbl.AddProperty(DBPROP_QUICKRESTART, true);
propSetTbl.AddProperty(DBPROP_IRowsetChange, true);
propSetTbl.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_DELETE |
DBPROPVAL_UP_INSERT);
hr = tbl.Open(g_oleDbSession, _T("Table"), _T("pk_id"),
&propSetTbl);
if(hr == S_OK)
{
ULONG i;
for(i = 0; i < tbl.m_nColumns; ++i)
tbl.SetStatus(i, DBSTATUS_S_IGNORE);
.
.
.
hr = tbl.Insert();
if(FAILED(hr))
{
}
tbl.Close();
}
Deleting a Row
To delete an existing row, we must first 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:
HRESULT hr = E_FAIL;
CDBPropSet propSetTbl(DBPROPSET_ROWSET);
propSetTbl.AddProperty(DBPROP_BOOKMARKS, true);
propSetTbl.AddProperty(DBPROP_OWNUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OWNINSERT, true);
propSetTbl.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
propSetTbl.AddProperty(DBPROP_OTHERINSERT, true);
propSetTbl.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propSetTbl.AddProperty(DBPROP_QUICKRESTART, true);
propSetTbl.AddProperty(DBPROP_IRowsetChange, true);
propSetTbl.AddProperty(DBPROP_IRowsetIndex, true);
hr = tbl.Open(g_oleDbSession, _T("Table"), _T("pk_id"),
&propSetTbl);
if(hr == S_OK)
{
tbl.SeekValue(m_szID, nID);
hr = m_table.SeekAndGet(1);
if(hr == S_OK)
{
hr = m_table.Delete();
if(FAILED(hr))
{
}
}
else
{
}
m_table.Close();
}
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:
CString strText;
strText = (LPCTSTR)tbl._GetDataPtr(nColumn);
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.
