Using Database Engines in a Mobile Application

There is no need to note that, with database functionality, application logic becomes much simplier. And, a programmer’s life—I don’t even have to mention how that’s simplified…. Windows CE has its own database support, but its usage is a bit complicated. It isn’t overly complicated, but it’s complicated enough enough from my point of view to choose flat binary files instead of inviting all this headache. Currently, you also may find a lot of database solutions for mobile devices. Which one of them you should use often depends on quite unrelated factors, such as existing environment, price, capabilities, and so forth. Microsoft provides, as a standard engine, SQL Server CE (in version 2.0 already), so I’ll focus on this particular engine in this article. We definitely won’t dive into all the details, but I’ll try to cover in ‘one touch’ several techniques or environments, so you will be able to make your decision what to choose.

ADOCE

Using ADOCE on a Pocket PC is simple enough, even from C++ applications. If you are familiar with ADO for the desktop, you will find a pretty similar environment. Unfortunately, ADOCE development, along with eVB development, is no longer supported under the eVC4 IDE. Microsoft requires managed code and ADO.NET. That’s really out of the clear blue sky, I would say. Nevetherless, Pocket PC 2002 devices are still alive, so it’s not totally dead.

Windows CE provides two sets of interfaces: ADOCE and ADOXCE. ADOCE is used to access data in databases, and ADOXCE serves to manupulate the database objects such as tables, columns, and so forth. All this stuff is designed for single-user access only. For more convenient work, it’s advised to use smart pointers with all interfaces. After all this dry theory, a small sample below demonstrates the taste of ADO under Windows CE:

#undef EOF
#import "adoce31.tlb"
#import "adoxce31.tlb"

using namespace ADOCE;
using namespace ADOXCE;
...
CString CSQLCEApp::m_sConnString =
   _T("Provider=Microsoft.SQLServer.OLEDB.CE.2.0;
      Data Source=\Test.sdf");
...
int CSQLCEApp::CreateCatalog()
{
   _CatalogPtr pCatalog;
   if ( FAILED( pCatalog.CreateInstance( _T("ADOXCE.Catalog.3.1"))
      ) )
   {
      TRACE(_T("Failed to create Catalog objectn"));
      return 0;
   }

   _bstr_t bstrConnection(m_sConnString);
   _variant_t varConnection;
   varConnection = pCatalog->Create(bstrConnection);
   if(varConnection.vt != VT_DISPATCH)
   {
      TRACE(_T("Catalog already existsn"));
      return 1;
   }
   TRACE(_T("Catalog created successfullyn"));
   return 1;
}

As you can see, the working environment is comfortable enough. You do import tlb-files and then use interfaces such as C++ classes. Pay attention to two things. First, #undef EOF—the usual ADO workaround. Second, in the connection string, you may define any existing provider, e.g. native Windows CE databases. Choosing ADO gives you relative simplicity in coding, but you pay for it by taking a performance hit. When your application doesn’t keep a large amount of data, ADOCE may be the best choice. With heavy amounts of data, the user will be happy to throw the device in the recycle bin pretty quickly.

Similarly to the desktop environment, ADOCE provides you with two main methods of data manipulation. You either may execute SQL queries for all possible needs or work with database objects (such as tables) directly. The last method may be quicker, but not always suitable. Thus, the correct strategy depends on your application.

In addition, you have an option to synchronize databases with a desktop computer via ActiveSync. (Frankly, now only native and SQLCE databases may be synchronized via ActiveSync unless your database provider has implemented its own.)

CF.NET and the C# world

Since .NET 1.1, you have had a new namespace (System.Data.SqlServerCe) to work with a SQL server from C# applications. The following code snippet is just a short extraction from VC7 Help:

using System.Data.SqlServerCe;
...
string connStr = @"Data Source = Test.sdf; Password = <password>";

SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();

SqlCeConnection conn = null;

try
{
   conn = new SqlCeConnection(connStr);
   conn.Open();

   SqlCeCommand cmd = conn.CreateCommand();
   cmd.CommandText = "CREATE TABLE TestTable (col1 int, col2 ntext)";
   cmd.ExecuteNonQuery();
}
catch {}
finally
{
   conn.Close();
}

We won’t stick it in here specifically because you can find many good samples on the Web, for example here. As a bottom line, a C# programmer has a rich set of classes, even though not all of the desktop ones are supported under WinCE. But, at least replication and remote data access (RDA) are supported as far as manipulating data in local database goes. Once again, performance may be a bottleneck in this case also.

Using OLE DB CE

First glance

When you’re facing high performance requirements or have a large amount of application data, OLE DB raises its head. It sounds attractive until you start to code something. The native OLE DB API is very similar to regular CE databases. A good example is shipped within SQL CE samples. No doubt, you will want to implement some wrapper classes or use existing ones. Thus, our hopes are moving forward to ATL CE.

If you look into atldbcli.h, you’ll be surprised to find a lot of code doing a lot of black jobs for you with OLE DB interfaces. Great, you might say. The sad fact is that you can’t compile your code with ATL OLE DB client code (no matter whether it’s on PPC 2002 or PPC 2003). Fortunately, we’re able to get it working after doing some effort.

First all, if you try to include atldbcli.h at the end of standard stdafx.h for an MFC-based application and compile, you’ll get error messages. That happens because of the following line in wce.h:

#define __oledb_h__

The workaround of this issue is simple: Either put all needed stuff before the MFC headers or just use an undef ‘undesirable’ definition and then include all needed headers:

/////////////////////////
// MFC stuff
/////////////////////////
#define VC_EXTRALEAN        // Exclude rarely-used stuff from
                            // Windows headers
#include <afxwin.h>         // MFC core and standard components
#include <afxext.h>         // MFC extensions
#if defined(_AFXDLL)
   #include <afxdtctl.h>    // MFC support for Internet
                            // Explorer 4 Common Controls
#endif
#ifndef _AFX_NO_AFXCMN_SUPPORT
   #include <afxcmn.h>      // MFC support for Windows Common
                            // Controls
#endif                      // _AFX_NO_AFXCMN_SUPPORT

////////////////////////
// ATL OLEDB stuff
////////////////////////
#define _ATL_MIN_CRT
#define INITGUID
#define DBINITCONSTANTS

#undef __oledb_h__

#include <oledb.h>
#include <oledberr.h>
#include <atldbcli.h>

////////////////////////
// SQLCE stuff
////////////////////////
#include "ssceoledb.h"
#include "ca_mergex.h"

The main issue left is the QueryInterface function of the IUnknown interface. Unlike the desktop version, its Windows CE counterpart has lost a small template version of QueryInterface. The following code marks all important changes in unknwn.h with a bold red font:

extern "C++"
{

   interface DECLSPEC_UUID("00000000-0000-0000-C000-000000000046")
   IUnknown
   {
   public:
      BEGIN_INTERFACE
      virtual HRESULT STDMETHODCALLTYPE QueryInterface(
         /* [in] */    REFIID riid,
         /* [iid_is][out] */    void __RPC_FAR *__RPC_FAR *ppvObject)
                                = 0;

      virtual ULONG STDMETHODCALLTYPE AddRef( void)  = 0;

      virtual ULONG STDMETHODCALLTYPE Release( void) = 0;



   template <class Q>
   HRESULT STDMETHODCALLTYPE QueryInterface(Q** pp)
   {
      return QueryInterface(__uuidof(Q), (void**)pp);
   }

      END_INTERFACE
   };

}

Another solution is to change atldbcli.h in all methods that take a pointer to IUnknown-derived interfaces according to the following pattern:

// smart pointer definition
CComPtr<ISomething> spSomething;

// change next line from
HRESULT hr = pUnk->QueryInterface(&spSomething);
// to
HRESULT hr = pUnk->QueryInterface(__uuidof(ISomething),
                                 (void**)&spSomething);

In addition, in all places where class members are used (defined as smart pointers), just replace “->” with “.”:

class CRowset
{
...
   HRESULT Compare(const CBookmarkBase& bookmark1,
                   const CBookmarkBase& bookmark2,
      DBCOMPARE* pComparison) const
   {
      ATLASSERT(m_spRowset != NULL);
      CComPtr<IRowsetLocate> spLocate;
      // Replace the next line
      HRESULT hr = m_spRowset->QueryInterface(&spLocate);
      // with this
      HRESULT hr = m_spRowset.QueryInterface(&spLocate);
      if (FAILED(hr))
         return hr;

      return spLocate->Compare(NULL, bookmark1.GetSize(),
                               bookmark1.GetBuffer(),
         bookmark2.GetSize(), bookmark2.GetBuffer(), pComparison);
   }
...
};

Which solution you choose is totally your call. I guess that changing things in IUnknown is much quicker, even though you should correct atldbcli.h in any case. When you take a look at the CDynamicAccessor::BindColumns method, you’ll see the following code:

HRESULT BindColumns(IUnknown* pUnk)
{
...
   // If column is of type STR or WSTR increase length by 1
   // to accommodate the NULL terminator.
   if (m_pColumnInfo[i].wType == DBTYPE_STR ||
       m_pColumnInfo[i].wType == DBTYPE_WSTR)
       m_pColumnInfo[i].ulColumnSize += 1;
...
}

It means that ASCII and UNICODE strings are handled the same way. You may correct it as follows:

HRESULT BindColumns(IUnknown* pUnk)
{
...
   // If column is of type STR or WSTR increase length by 1
   // to accommodate the NULL terminator.
   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(TCHAR);
   ...
}

And finally, there’s one last thing before you can program something meaningful. In SDKs for WinCE 4.X, there is no oledb.lib file. In the case of SQL CE, you may download a workaround for this issue from here.

Connecting to a data source

The first step you will do is connect to the desired data source. The ATL DB has a CDataSource class this purpose. The CSession class represents opened sessions. The following code sample shows all the required stuff:

HRESULT OpenDataSource()
{
   HRESULT hr;
   CDataSource db;
   CDBPropSet dbinit [2];

   dbinit [0].SetGUID(DBPROPSET_DBINIT);
   dbinit [1].SetGUID(DBPROPSET_SSCE_DBINIT);

   dbinit [0].AddProperty(DBPROP_INIT_DATASOURCE,         OLESTR
                         (<Path to database file>);
   dbinit [1].AddProperty(DBPROP_SSCE_TEMPFILE_DIRECTORY, OLESTR
                         (<Path to store temp files>);

   hr = db.Open(_T ("Microsoft.SQLSERVER.OLEDB.CE.2.0"), dbinit, 2);
   if (FAILED(hr))
   {
      return hr;
   }

   // m_session is of type CSession (refer to atldbcli.h for details)
   return m_session.Open(db);
}

You may add all desired DBPROP_INIT_XXX or DBPROP_SSCE_XXX propeties or implement additional wrapper classes if you need to. Once you’ve successfully opened the session, you should close it somewhere.

Start

ATL DB CE has several ‘base’ classes that handle a typical database’s tasks. These classes are CCommand, CAccessor, CDynamicAccessor, and CRowset. CAccessor and CDynamicAccessor are used to fetch data. CAccessor does it by using fixed schema, while CDynamicAccessor allows you to do it all at runtime. In turn, the CRowset class precedes different data management operations such as inserting, deleting, and updating data in tables and working with cursors and bookmarks. The CCommand class, according to its name, executes commands by using assessors or row sets.

Now, taking all that was noted above in mind, we may start to code our simple ATL-based program. Let’s start with executing some command. As we may find in atldbcli.h, the CCommand class has the following definition:

template <class TAccessor = CNoAccessor,
          class TRowset   = CRowset,
          class TMultiple = CNoMultipleResults>
class CCommand :
   public CAccessorRowset<TAccessor, TRowset>,
   public CCommandBase,
   public TMultiple

So, if you don’t need any parameters or output columns, you may declare your command object as the following:

CCommand<CNoAccessor,CNoRowset> cmd;
HRESULT hr = cmd.Open(session, L"update GROUPS set name = 'group1'
                                 where code = 1");

That’s the simplest way to run queries with INSERT, UPDATE, or DELETE statements. In the case of more complicated commands, you may first define, for example, fixed schema for data:

class CGroupsAccessor
{
   SHORT     m_nGroupCode;
   TCHAR     m_wszGroupDesc[14];

BEGIN_COLUMN_MAP(CGroupsAccessor)
   COLUMN_ENTRY(1, m_nGroupCode);
   COLUMN_ENTRY(2, m_wszGroupDesc);
END_COLUMN_MAP()

DEFINE_COMMAND (CGroupsAccessor, L"SELECT * FROM GROUPS")

   void ClearRecord()
   {
      memset(this, 0, sizeof(*this));
   }
};

Thus, we’ve declared the GROUPS table with two columns: code and description. In addition, the DEFINE_COMMAND macro defines a default SQL query to retrieve table data. Then, you are ready to declare a command object for each accessor:

CCommand<CAccessor<CGroupsAccessor> > cmd;
HRESULT hr = cmd.Open(session);

You also may use CDynamicAccessor instead of CAccessor-derived classes, as in the following example:

CCommand<CDynamicAccessor, CRowset> commandInsert;
TCHAR tszSQL[] = L"INSERT INTO GROUPS (Code,Name) VALUES (1,'Group1')";
hr = m_session.StartTransaction();
hr = commandInsert.Open(m_session,
                       (LPCTSTR) tszSQL,
                        NULL ,
                        NULL,
                        DBGUID_DBSQL,
                        false));
hr = m_session.Commit();

Now, several words about row sets. That is an alternative way to manipulate the data, like in ADOCE, with all the pro and contra. ATL DB CE doesn’t support all kinds of CRowset-derived classes, but you will find CBulkRowset and CArrayRowset as useful examples. In addition, similarly to ADOCE, the CTable class handles database tables. The work flow for all of the above templates is to get the desired row set and then manage it by calling the corresponding Get/Set methods. Such an approach may be much more efficient because you’re working directly with data without any SQL commands. On the other hand, the logic becomes more complicated. Hence, you should weigh both approaches before the final shot.

Where to go

As we saw, you have a lot of choices at hand. For me, the preferable way is to use ATL. If Microsoft will fix or re-port a desktop version of the ATL DB client, you’ll get much more suitable classes than we have now under WinCE. I hope you’re disoriented enough now to dive into the ATL DB consumer sea for the great fun. Good luck!

About the Author

Alex Gusev started to play with mainframes at the end of the 1980s, using Pascal and REXX, but soon switched to C/C++ and Java on different platforms. When mobile PDAs seriously rose their heads in the IT market, Alex did it too. Now, he works at an international retail software company as a team leader of the Mobile R department, making programmers’ lives in the mobile jungles a little bit simpler.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read