Download demo project - 291 KB

Introduction

This is the third article on using the ATL OLE DB Consumer Templates on Pocket PC. In the first article I had described how the ATL OLE DB Consumer Templates can be adapted and used on Pocket PC platforms. The second article showed how to manage large data types, also known as BLOBs. Both the articles provided are examples of managing data in a SQL Server CE 2.0 database, and are the foundation for the material provided here.

Here, I will show you how to explore the database schema information, particularizing for the SQL CE 2.0 database. Like you have seen in the first article, I will have to adapt one of the ATL distribution files in order to make it work correctly on the Windows CE compilers.

Enabling

If you remember my first article, I had to create a customized version of the atldbcli.h header file and rearrange the way stdafx.h is laid out so that we can compile the ATL OLE DB Consumer Templates along with MFC. Now, there is more of the same work to be done, but now we will have to focus on the atldbsch.h header file.

The atldbsch.h header file
This file contains a number of class declarations that help us in enumerating database schema information. Most of this information is provided in the form of a row set that can be accessed just like a table. This information includes the list of tables, columns, indexes, constraints and so forth. Interestingly, and as you will see in the course of this article, we will use a mix of techniques to access SQL CE 2.0 schema information. These row sets play a very important role, but they are not enough as we will see in this article.

There are a number of interesting classes in atldbsch.h, most notably the CRestrictions class template. This template is used to instantiate the schema row set classes using auxiliary accessor classes. Each one of these classes defines the structure of the underlying data structure. For instance, to enumerate all the tables on a database you use the CTables class which is nothing but a typedef (you can find all available typedefs at the end of the atldbsch.h file):
```
typedef CRestrictions<CAccessor<CTableInfo>,4,&DBSCHEMA_TABLES> CTables;
```
To understand what information is available when you open and browse such a row set, you must look into the definition of the CTableInfo class. For convenience of our discussion, I reproduce it here:
```
class CTableInfo
{
public:
// Constructors
    CTableInfo()
    {
        memset(this, 0, sizeof(*this));
    }

// Attributes
    TCHAR   m_szCatalog[129];
    TCHAR   m_szSchema[129];
    TCHAR   m_szName[129];
    TCHAR   m_szType[129];
    GUID    m_guidTable;
    TCHAR   m_szDescription[129];

// Binding Map
BEGIN_COLUMN_MAP(CTableInfo)
    COLUMN_ENTRY(1, m_szCatalog)
    COLUMN_ENTRY(2, m_szSchema)
    COLUMN_ENTRY(3, m_szName)
    COLUMN_ENTRY(4, m_szType)
    COLUMN_ENTRY(5, m_guidTable)
    COLUMN_ENTRY(6, m_szDescription)
END_COLUMN_MAP()
};
```
As you can see, this is pretty simple stuff: a record description and an accessor column map. Most of these classes will run as you expect them to, some will fail and others are downright incomplete. So, like we did before, we have to change this file to make it work correctly. Let’s see how.
Modifying the atldbsch.h header file
Before you start, copy the atldbsch.h (found on the ATL include folder) to atldbsch_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 that it doesn’t get deleted if you need to reinstall the development tools.

The change we need to make is to correct how boolean values are accessed. Boolean values are declared using the VARIANT_BOOL type which is nothing more than an unsigned short with specific semantics: false is represented as 0 and true as ~0. This contrasts a little bit with the customary definitions of the boolean constants, and the trouble arises when data is bound by the accessor. The solution is actually pretty simple and it involves replacing the accessor column binding macro. Let’s see an example.

Start by locating the CColumnsInfo class. As you can see, there are two boolean values: m_bColumnHasDefault and m_bIsNullable. If you look below, you will see the binding entries for these values:
```
COLUMN_ENTRY(8, m_bColumnHasDefault)
```
Three lines below you will find the other entry:
```
COLUMN_ENTRY(11, m_bIsNullable)
```
This is what we need to change to make all boolean bindings work, and it is a simple change. For instance, we change the first binding to:
```
COLUMN_ENTRY_TYPE_SIZE(8, DBTYPE_BOOL, 2, m_bColumnHasDefault)
```
This column binding macro now says explicitly that we are binding a boolean value that is two bytes long. Now, your mission is to replace all such occurrences on the file.

Finally, you should modify your stdafx.h file and include atldbsch_ce.h right after atldbcli_ce.h:
```
...
#include "atldbcli_ce.h"
#include "atldbsch_ce.h"
...
```

Using

OLE DB allows you to enumerate everything in a database, starting from the available servers down to the very specific properties of a table column. In this article, we are considering only enumerating SQL CE 2.0 schemas, so some restrictions apply.

To see which schema row sets are supported, see the SQL CE 2.0 online documentation. As you can see, we don't have some of the relevant row sets. Let’s see how we can do without them.

Enumerating Servers

Enumerating servers is not an option because there can be only one server per device so the server is either installed or not. To determine if the server is installed, you can use the following code:

#include <ca_mergex20.h>

bool IsSqlCeInstalled()
{
    ISSCEEngine* pEngine;
    HRESULT      hr;

    ::CoInitializeEx(NULL, COINIT_MULTITHREADED);

    hr = CoCreateInstance(CLSID_Engine, 
                          NULL,
                          CLSCTX_INPROC_SERVER,
                          IID_ISSCEEngine, 
                          (LPVOID *) &pEngine);

    if(SUCCEEDED(hr))
        pEngine->Release();

    ::CoUninitialize();

    return SUCCEEDED(hr);
}

Do not forget to include the ca_mergex20.lib on your project's library list (Project Settings / Link tab).

Enumerating Databases
SQL CE 2.0 databases are stored in single files, and there is no centralized reference for the available databases, unlike SQL Server 2000, but like Access. So enumerating available databases is just a simple matter of using the FindFirstFile and FindNextFile functions, using “*.sdf” as a search wildcard.
Enumerating Tables
Database tables are enumerated using the CTables class. The following code assumes that you have opened a database using the techniques described in the first article.
```
HRESULT hr;
CTables table;

hr = table.Open(session, NULL, NULL, NULL, _T("TABLE"));
```
The variable session holds an open CSession object. The last parameter instructs the OLE DB provider to enumerate the user tables, skipping the system tables. To see all the supported values for this parameter, please see the OLE DB Programmer’s Reference under the "TABLES Rowset" topic.

Enumerating the tables is a simple matter, just like in a table or query:
```
if(SUCCEEDED(hr))
{
    for(hr = table.MoveFirst(); hr == S_OK; hr = table.MoveNext())
    {
    // The table name is table.m_szName
    }
}
```
At the end of the table enumeration, do not forget to close the CTables object:
```
table.Close();
```
This is simple, right? Now, let’s look at column enumeration in a table.
Enumerating Table Columns
Enumerating columns in a table is also a simple matter. The SQL CE 2.0 OLE DB provider supports the COLUMNS row set, implemented by ATL through the CColumns class. The logic is the same:
```
HRESULT  hr;
CColumns column;

hr = column.Open(session, NULL, NULL, _T("TableName"));
```
This code opens the columns row set for enumeration on a given table. Enumeration is performed just like in the tables: using the underlying cursor.

Unfortunately, this schema row set will not expose all the column information you might require (see the CColumnsInfo class on atldbsch_ce.h). As you can see, some of the most interesting pieces of information are missing like whether the column is an IDENTITY column, its seed and increment. Unfortunately, there is no way to get this information using the schema row sets. We have to go another way.

The ITableCreation Interface

To access extended column schema information, we have to use a specific OLE DB interface: ITableCreation. If you look at the interface documentation, you will see that it supports just one method: GetTableDefinition. And what a powerful method this is! With just one call, you get all the columns’ names, properties and constraints.

To illustrate how to use this method, please refer to the sample application, on the CDbTable class, and the LoadSchema method. This class encapsulates the entire table’s schema information, including columns, constraints and indexes.

First, we begin by declaring the required variables:

HRESULT                 hr;
CComPtr<ITableCreation> spTableCreation;

Now, we instantiate a pointer to the ITableCreation interface:

hr = session.m_spOpenRowset->QueryInterface(IID_ITableCreation,
                                            (void**)&spTableCreation);

Now, we can access the column schema:

if(SUCCEEDED(hr))
{
    DBID              idTable;
    ULONG             nColumns,
                      nProperties,
                      nConstraints;
    DBCOLUMNDESC*     pColDesc;
    DBPROPSET*        pPropertySet;
    DBCONSTRAINTDESC* pConstraints;
    OLECHAR*          pStrings;
    TCHAR             szName[129];

    hr = spTableCreation->GetTableDefinition(&idTable,
                                             &nColumns,
                                             &pColDesc,
                                             &nProperties,
                                             &pPropertySet,
                                             &nConstraints,
                                             &pConstraints,
                                             &pStrings);

    if(SUCCEEDED(hr))
    {
        AddColumns(pColDesc, nColumns);
        AddConstraints(pConstraints, nConstraints);
        AddIndexes(session);
        CoTaskMemFree(pStrings);
    }

    spTableCreation.Release();
}

The code is not very complex, really. The method fills in a number of provider-allocated buffers (except pStrings) that will contain all the information we need. As you can see, except for pStrings, we do not care about releasing these buffers. Accessing this wealth of information requires some work. Here I have encapsulated all this work under specialized methods: AddColumns to add all column definitions, AddConstraints to add both primary and foreign key constraints, and finally AddIndexes to add all the index information pertaining to a table.

Let’s look at each one in detail.

AddColumns

Each table column is described by a DBCOLUMNDESC structure. If you look at it in the oledb.h file, you will see:

typedef struct  tagDBCOLUMNDESC
    {
    LPOLESTR pwszTypeName;
    ITypeInfo __RPC_FAR *pTypeInfo;
    /* [size_is] */ DBPROPSET __RPC_FAR *rgPropertySets;
    CLSID __RPC_FAR *pclsid;
    ULONG cPropertySets;
    ULONG ulColumnSize;
    DBID dbcid;
    DBTYPE wType;
    BYTE bPrecision;
    BYTE bScale;
    }   DBCOLUMNDESC;

This structure contains an array of property sets that describes all the features of the column. Each property set is identified by its own GUID and the column properties we are looking for can be found on the DBPROPSET_COLUMN property set. The most relevant properties belonging to this property set are:

DBPROP_COL_AUTOINCREMENT: Tells us if this is the IDENTITY column. SQL CE restricts these columns to 32 bit integer types (DBTYPE_I4).
DBPROP_COL_SEED: This property contains the seed (initial value) value for IDENTITY columns.
DBPROP_COL_INCREMENT: The value that the database engine uses to increment the IDENTITY column is stored here.
DBPROP_COL_NULLABLE: This property is set to true if the column accepts null values.
DBPROP_COL_ISLONG: Columns storing binary large object (BLOBs) have this property set to true.
DBPROP_COL_FIXEDLENGTH: This property is true for fixed length columns.

To read all these property values, we have to loop through the property set array. My approach was to use an intermediate structure to store the column properties:

typedef struct tagCOLUMN
{
    TCHAR           szName[129];
    DBTYPE          wType;
    DBCOLUMNFLAGS   dwFlags;
    ULONG           nSize,
                    nOrdinal;
    USHORT          nPrecision;
    SHORT           nScale;
    VARIANT_BOOL    bIdentity;
    int             nSeed,
                    nIncr;
} COLUMN;

Some of the properties will be stored in the structure variables while others will be converted into column flags. Note that in this sample I am not retrieving the column default value, but this can also be done by using the DBPROP_COL_DEFAULT property.

After filling up this structure, an object of type CDbColumn is created and added to the table’s column list (the m_vecCol member variable).

Here is the full code:

void CDbTable::AddColumns(DBCOLUMNDESC* pColDesc, ULONG nColumns)
{
    ULONG        i;
    COLUMN       col;

    m_vecCol.reserve(nColumns);

    //
    // Loop through all columns
    //
    for(i = 0; i < nColumns; ++i)
    {
        ULONG       cs,
                    cp;
        DBPROPSET*  pPropSet;
        CDbColumn*  pColumn;

        StrCopyN(col.szName, pColDesc[i].dbcid.uName.pwszName, 128);

        col.dwFlags     = 0;
        col.wType       = pColDesc[i].wType;
        col.nPrecision  = pColDesc[i].bPrecision;
        col.nOrdinal    = i + 1;
        col.nScale      = pColDesc[i].bScale;
        col.nSize       = pColDesc[i].ulColumnSize;
        col.bIdentity   = VARIANT_FALSE;
        col.nSeed       = 0;
        col.nIncr       = 0;

        pPropSet = pColDesc[i].rgPropertySets;
        for(cs = 0; cs < pColDesc[i].cPropertySets; ++cs, ++pPropSet)
        {
            if(pPropSet->guidPropertySet == DBPROPSET_COLUMN)
            {
                DBPROP* pProp = pPropSet->rgProperties;
                for(cp = 0; cp < pPropSet->cProperties; ++cp, ++pProp)
                {
                    switch(pProp->dwPropertyID)
                    {
                    case DBPROP_COL_AUTOINCREMENT:
                        col.bIdentity = pProp->vValue.boolVal;
                        break;

                    case DBPROP_COL_SEED:
                        col.nSeed = pProp->vValue.intVal;
                        break;

                    case DBPROP_COL_INCREMENT:
                        col.nIncr = pProp->vValue.intVal;
                        break;

                    case DBPROP_COL_NULLABLE:
                        if(pProp->vValue.boolVal == VARIANT_TRUE)
                            col.dwFlags |= DBCOLUMNFLAGS_ISNULLABLE;
                        break;

                    case DBPROP_COL_ISLONG:
                        if(pProp->vValue.boolVal == VARIANT_TRUE)
                            col.dwFlags |= DBCOLUMNFLAGS_ISLONG;
                        break;

                    case DBPROP_COL_FIXEDLENGTH:
                        if(pProp->vValue.boolVal == VARIANT_TRUE)
                            col.dwFlags |= DBCOLUMNFLAGS_ISFIXEDLENGTH;
                        break;
                    }
                }
            }
        }

        //
        // Add the column
        //
        pColumn = new CDbColumn(&col);
        if(pColumn)
            m_vecCol.push_back(pColumn);
    }
}

AddConstraints

Constraints are of four different types:

primary keys
foreign keys
check constraints
unique constraints

If the SQL CE 2.0 does not support check constraints, then we can skip them. Unique constraints will not be covered in this article although they can be easily added.

All constraint types are described by a DBCONSTRAINTDESC structure that is defined as:

typedef struct  tagDBCONSTRAINTDESC
    {
    DBID __RPC_FAR *pConstraintID;
    DBCONSTRAINTTYPE ConstraintType;
    ULONG cColumns;
    /* [size_is] */ DBID __RPC_FAR *rgColumnList;
    DBID __RPC_FAR *pReferencedTableID;
    ULONG cForeignKeyColumns;
    /* [size_is] */ DBID __RPC_FAR *rgForeignKeyColumnList;
    OLECHAR __RPC_FAR *pwszConstraintText;
    DBUPDELRULE UpdateRule;
    DBUPDELRULE DeleteRule;
    DBMATCHTYPE MatchType;
    DBDEFERRABILITY Deferrability;
    ULONG cReserved;
    /* [size_is] */ DBPROPSET __RPC_FAR *rgReserved;
    }   DBCONSTRAINTDESC;

The constraint type is stored on the ConstraintType member and can be of the following values:

DBCONSTRAINTTYPE_UNIQUE
DBCONSTRAINTTYPE_FOREIGNKEY
DBCONSTRAINTTYPE_PRIMARYKEY
DBCONSTRAINTTYPE_CHECK

To handle both primary and foreign keys, I created classes to store them, namely CDbForeignKey and CDbIndex (a primary key is actually a special kind of index).

These classes are simply lists of columns or column pairs (please see sample application for details).

Here is the full code of AddConstraints:

void CDbTable::AddConstraints(DBCONSTRAINTDESC *pConstraints, 
                                          ULONG nConstraints)
{
    ULONG    iCon;

    for(iCon = 0; iCon < nConstraints; ++iCon)
    {
        DBCONSTRAINTDESC* pCon = pConstraints + iCon;

        switch(pCon->ConstraintType)
        {
        case DBCONSTRAINTTYPE_UNIQUE: // Not handled
            break;

        case DBCONSTRAINTTYPE_FOREIGNKEY:
            {
                CDbForeignKey* pFky = new CDbForeignKey(
                    pCon->pConstraintID->uName.pwszName,
                    pCon->pReferencedTableID->uName.pwszName,
                    pCon->UpdateRule,
                    pCon->DeleteRule);

                if(pFky)
                {
                    ULONG iCol;

                    m_vecFky.push_back(pFky);

                    for(iCol = 0; iCol < pCon->cColumns; ++iCol)
                    {
                      CDbForeignKeyCol* pCol;
                        
                      pCol = new CDbForeignKeyCol(
                        pCon->rgColumnList[iCol].uName.pwszName,
                        pCon->rgForeignKeyColumnList[iCol].uName.pwszName);

                      if(pCol)
                          pFky->AddColumn(pCol);
                    }
                }
            }
            break;

        case DBCONSTRAINTTYPE_PRIMARYKEY:
            {
                ULONG     iCol;
                CDbIndex* pIndex = new CDbIndex(
                    pCon->pConstraintID->uName.pwszName, true);

                if(pIndex)
                    m_vecIdx.push_back(pIndex);

                for(iCol = 0; iCol < pCon->cColumns; ++iCol)
                {
                    CDbColumn* pColumn = FindColumn(
                         pCon->rgColumnList[iCol].uName.pwszName);
                    CDbIndexCol* pIdxCol = new CDbIndexCol(
                         pCon->rgColumnList[iCol].uName.pwszName, 1);

                    if(pColumn)
                        pColumn->SetPrimaryKey(true);

                    if(pIdxCol && pIndex)
                        pIndex->AddColumn(pIdxCol);
                }
            }
            break;

        case DBCONSTRAINTTYPE_CHECK: // Not supported
            break;
        }
    }
}

AddIndexes

Finally we can add indexes to our table definition. Unfortunately, this procedure is not as straightforward as you might have expected. Indexes are enumerated using the CIndexes class but this list will include genuine indexes, the primary key (a special type of index) and all foreign keys. So, we need to tell them before figuring out if the object we are being reported is a bona-fide index or something else.

The CIndexes class helps us with the primary key by storing whether an index is a primary key on the m_bPrimaryKey member variable. Unfortunately we have no such luck with foreign keys – we have to compare constraint names.

This process is illustrated in the source code, fully reproduced here:

void CDbTable::AddIndexes(CSession &session)
{
    CIndexes idx;
    HRESULT  hr;

    hr = idx.Open(session, NULL, NULL, NULL, NULL, m_strName);
    if(hr == S_OK)
    {
        while(idx.MoveNext() == S_OK)
        {
            CDbIndex* pIdx = NULL;

            // Skip primary keys.
            if(idx.m_bPrimaryKey == VARIANT_TRUE)
                continue;

            // Skip foreign key indexes.
            if(FindForeignKey(idx.m_szIndexName))
                continue;

            pIdx = FindIndex(idx.m_szIndexName);

            if(!pIdx)
            {
                pIdx = new CDbIndex(idx.m_szIndexName, 
                                    false, 
                                    idx.m_bUnique == VARIANT_TRUE);
                if(pIdx)
                    m_vecIdx.push_back(pIdx);
            }

            if(pIdx)
            {
                CDbIndexCol* pCol = new CDbIndexCol(idx.m_szColumnName, 
                                                    idx.m_nCollation);

                if(pCol)
                    pIdx->AddColumn(pCol);
            }
        }
        idx.Close();
    }
}

Note that each record contains both the index name and the column name. To build multiple column indexes, we have to read more than one row from CIndexes. That is why I have to used the FindIndex method.

Sample Application

The article’s sample application is SqlCeSpy, a small freeware tool. Here you have access to the full source code with some interesting features, like a main window with splitter and a data cache for the CListView control. But these will be covered in other articles.

Note: The sample includes the eVC3 project file only.

ATL OLE DB Consumer Template Schemas