Correct Column Ordinals of an ADOX Table Object

• Download source files - 2.9 Kb
• Download demo project - 55 Kb

Introduction

The ADOX Table object allows us to easily retrieve each table column using the Columns collection. However, the collection is sorted by the column/field name. In one of my projects, I need to support the following:

1. Represent the columns collection in the same order as when being displayed in MS Access
2. Be able to insert new columns anywhere in the collection (another article will discuss this)

Using the ADOX Columns collection alone is inadequate. Hence, the need to get the actual column ordinals.

Methodology

There are two ways to get the column ordinals:

• By walking through the rowset returned by ADO OpenSchema(adSchemaColumns,...) - slow
• Getting the DBCOLUMNINFO data from OLEDB IColumnsInfo interface - faster

This article discusses how to get the column ordinals from the IColumnsInfo OLEDB interface.

The IColumnsInfo is available from (implemented by) the command and rowset OLE DB objects. The method I present is basically composed of three steps:

1. Create an ADO Recordset from the ADOX Table
2. Convert the ADO Recordset into an OLEDB IRowset
3. Get the IColumnsInfo from IRowset

Here we go...

Step 1. Create an ADO Recordset from the table

// ADO recordset
ADODB::_RecordsetPtr p_adoRecordset;

p_adoRecordset.CreateInstance(__uuidof(ADODB::Recordset));
p_adoRecordset->Open(p_table->GetName(), 
    _variant_t((IDispatch *)p_connection,true), ADODB::adOpenKeyset,
    ADODB::adLockReadOnly, ADODB::adCmdTable);

Step 2. Convert the ADO Recordset into IRowset

// First, get ADORecordsetConstruction interface from the the ADO Recordset
ADODB::ADORecordsetConstruction * p_adoRecordsetConstruct;
p_adoRecordset->QueryInterface(__uuidof(ADODB::ADORecordsetConstruction),
    (void **) &p_adoRecordsetConstruct);

// From it, we can get the OLEDB <code>IRowset
IRowset * p_rowset;
p_adoRecordsetConstruct->get_Rowset((IUnknown **)&p_rowset);
p_adoRecordsetConstruct->Release();  // don't need it anymore

Step 3. Get the IColumnsInfo from IRowset

From IColumnsInfo, we can get the DBCOLUMNINFO data which contains the information we want. In this example, I create a map of the column name and the ordinal.

// The IColumnsInfo that contains ordinals
CComPtr<IColumnsInfo> spColumns;

// Determine if bookmark is supported (New in 3 Jun 2003)
BOOL b_hasBookMark = FALSE;
CComPtr<IRowsetInfo> spRowsetInfo;
HRESULT hr = p_rowset->QueryInterface(IID_IRowsetInfo, 
                                   (void**)&spRowsetInfo);
if (hr == S_OK)
{
    CDBPropIDSet set(DBPROPSET_ROWSET);
    set.AddPropertyID(DBPROP_BOOKMARKS);
    DBPROPSET* pPropSet = NULL;
    ULONG ulPropSet = 0;

    hr = spRowsetInfo->GetProperties(1, &set, &ulPropSet, &pPropSet);
    if (hr == S_OK)
    {
        b_hasBookMark = 
          (pPropSet->rgProperties[0].vValue.boolVal == VARIANT_TRUE);
    }
}

// Get the the IColumnsInfo from IRowset interface
p_rowset->QueryInterface(&spColumns);
// At this point, we may now release p_rowset
p_rowset->Release();

// IColumnsInfo will give us the DBCOLUMNINFO structure
DBCOLUMNINFO * p_columnInfo = NULL;
LPOLESTR pstr_stringBuffer = NULL;
ULONG ul_numColumns;

// Now get the DBCOLUMNINFO data 
spColumns->GetColumnInfo(&ul_numColumns, &p_columnInfo, &pstr_stringBuffer);

// Let's create a mapping for columName and the actual ordinal
typedef std::map<<_bstr_t, long>> ColumnNameToOrdinal;
ColumnNameToOrdinal columnOrdinals;

// Using the DBCOLUMNINFO, fill the name-to-ordinal map
USES_CONVERSION;
TCHAR * psz_fieldName;
for (ULONG i = 0; i < ul_numColumns; i++)
{
    psz_fieldName = OLE2T(p_columnInfo[i].pwszName);
    if (p_columnInfo[i].pwszName != NULL)
    {
        // iOrdinal of 0 is reserved for book mark, if supported
        columnOrdinals[_bstr_t(psz_fieldName)] = 
            p_columnInfo[i].iOrdinal- b_hasBookMark;
    }
}

// Clean up
CoTaskMemFree(p_columnInfo);
CoTaskMemFree(pstr_stringBuffer);

Using the code

The file ADOXColumnOrdinal.h contains static functions that you may want to directly use in your project or cut-and-paste into your class. You may include the file in your stdafx.h as shown below (items in bold are required):

// I assume you have this somewhere
#import "C:\Program Files\Common Files\System\ado\msado15.dll" \
    rename( "EOF", "adoEOF" ) rename("DataTypeEnum", "adoDataTypeEnum")
#import "C:\Program Files\Common Files\System\ado\msadox.dll" \
    rename( "EOF", "adoEOF" ) no_namespace 
            rename("DataTypeEnum", "adoDataTypeEnum")
// For _bstr_t and _variant_t
#include <comdef.h>
// OLEDB
#include "oledb.h"
// And of course, our code
#include "ColumnOrdinals.h"

Data structures

The following map definitions are used, respectively, in the functions described in the next section. Please refer to the function descriptions and notes on how to use these maps.

typedef std::map<long, long> ColumnOrdinalToIndex
typedef std::map<_bstr_t, long> ColumnNameToOrdinal
typedef std::map<long, _bstr_t> ColumnOrdinalToName

Utility functions

The following three utilities are provided as static functions so that you can easily adapt them into your class or library.

GetColumnOrdinalToIndexMap

static BOOL GetColumnOrdinalToIndexMap(_TablePtr p_table, 
                                 ColumnOrdinalToIndex & columnOrdinals)

This function will get a mapping of the ADOX field indices to the correct ordinals.

Parameters

• _TablePtr p_table - the ADOX table object
• ColumnOrdinalToIndex & columnOrdinals - a reference to a map defined as std::map<long, long> which will receive the map of ADOX index to ordinals.

Returns

• BOOL - TRUE if no errors were encountered, FALSE otherwise.

Notes

The ColumnOrdinalToIndex map (my favorite:-) is useful in determining the ADO index (index to GetItem) when accessing the nth field. e.g. sequentially listing the fields by their correct order:

ColumnOrdinalToIndex ordinals;
GetColumnOrdinalToIndexMap(p_tableFromSomewhere, ordinals);

_ColumnPtr p_column = NULL;
for (long i; i < p_tableFromSomewhere->Columns->Count; i++)
{
    p_column = p_tableFromSomewhere->Columns->GetItem(columnMap[i]);
    ATLTRACE(_T("%d.) %s\n"), i, p_column->GetName());
}

GetColumnNameToOrdinalMap

static BOOL GetColumnNameToOrdinalMap(_TablePtr p_table, 
                                   ColumnNameToOrdinal & columnOrdinals)

This function will get a mapping of the field names to the correct ordinals.

Parameters

• _TablePtr p_table - the ADOX table object
• ColumnNameToOrdinal & columnOrdinals - a reference to a map defined as std::map<_bstr_t, long> which will receive the map of column names to ordinals

Returns

• BOOL - TRUE if no errors were encountered, FALSE otherwise

Notes

The ColumnNameToOrdinal map is useful if you know a field name and you want to get the ordinal. For example:

ColumnNameToOrdinal ordinals;
GetColumnNameToOrdinalMap(p_tableFromSomewhere, ordinals);

ATLTRACE(_T("The ordinal of %s is %d\n"), _T("myField"), 
                                          ordinals[_T("myField")]);

GetColumnOrdinalToNameMap

static BOOL GetColumnOrdinalToNameMap(_TablePtr p_table, 
                                       ColumnOrdinalToName & columnOrdinals)

This function will get a mapping of the ADOX field indices to the correct ordinals.

Parameters

• _TablePtr p_table - the ADOX table object
• ColumnOrdinalToName & columnOrdinals - a reference to a map defined as std::map<long, _bstr_t> which will receive the map of map of ordinals to the field names

Returns

• BOOL - TRUE if no errors were encountered, FALSE otherwise

Notes

The GetColumnOrdinalToNameMap map is useful in getting the name of the nth field. e.g. sequentially listing the fields by their correct order:

ColumnOrdinalToIndex ordinals;
GetColumnOrdinalToIndexMap(p_tableFromSomewhere, ordinals);

for (long i; i < p_tableFromSomewhere->Columns->Count; i++)
{
    ATLTRACE(_T("%d.) %s\n"), i, ordinals[i]);
}

In practice

The functions above gets the ordinals in bulk. It would be impractical to get the ordinals every time, say, you want to display a field. This could slow down your application tremendously.

The proper way of implementation would be to make one of the maps shown above as a member of your document, dialog or a wrapper class. So you will only get the ordinals once, when your object is created.

The sample application

The sample project is a simple ATL application. You can see a bit of:

• Using ADO and ADOX together
• Using ADOX Catalog, Table and Column objects
• Scanning for OLEDB provider error
• Basic way of subclassing a ListView control
• Reflection, notification handling in WTL
• Using DDX in ATL/WTL

Notes

1. The base ATL application was generated using the ATL version 7's AppWizard.
2. Lesson learned: When compiling the release mode I had to remove the _ATL_MIN_CRT defined in the project settings to resolve link error : LNK2001 symbol '_main' not found. The main() entry point is required by STL.
3. Turn /GX option on to support exception handling
4. If you want to compile it with ATL version 3, comment out the line with the AtlInitCommonControls() in ADOXColumnOrdinal.h file.

Finally

Finally, may I say - the codes here were written while I'm cooking my dinner :-). It may not be perfect, but I hope it helped to illustrate my points.

History

• 3 Jun 2003 - Dynamically detects whether bookmark is supported in the rowset
• 5 Dec 2002 - Initial release