Click here to Skip to main content
Click here to Skip to main content

Correct Column Ordinals of an ADOX Table Object

, 2 Jun 2003
Rate this:
Please Sign up or sign in to vote.
Getting the correct column ordinals of an ADOX Table object.

Sample Image - ADOXColumnOrdinal.gif

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"
Note: If not already set, you need to set the /GX compiler option to support exception handling.

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 favoriteSmile | :) 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 Smile | :) . 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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ferdie
Software Developer (Senior)
United States United States
Roaming halfway around the globe programming in C++, MFC, COM/ATL, WTL, C#, .NET, OLEDB, ADO, ADO/X.
 
Living under the pleasant weather of Irvine, California, Ferdie is a Computer Engineering graduate of Mapua Institute of Technology (MIT Smile | :) ) in Philippines. Developed GIS applications in Japan for 5 years. Now a member of a team developing Windows GUI and real time software for semi-robotic equipments.

Comments and Discussions

 
GeneralCrash in AddPropertyID [modified] Pinmember7.e.Q16-Aug-07 3:46 
QuestionVB.Net equivalent? PinmemberJDMils11-Mar-06 17:36 
Generalatlres.h Pinmemberkrippi23-Jun-04 1:36 
GeneralPlease help PinmemberSAHorowitz22-Apr-03 18:30 
GeneralRe: Please help PinmemberFerdie7-May-03 20:19 
GeneralTasty! PinmemberPhil J Pearson6-Jan-03 2:46 
GeneralThanks PinmemberPaul Selormey6-Jan-03 1:50 
Generalatlres.h missing PinmemberGregor Schiller5-Jan-03 11:39 
GeneralRe: atlres.h missing PinmemberChristian Graus5-Jan-03 11:49 
GeneralRe: atlres.h missing PinmemberFerdie5-Jan-03 12:43 
GeneralRe: atlres.h missing PinmemberGregor Schiller5-Jan-03 17:59 
GeneralRe: atlres.h missing Pinmemberkrippi22-Jun-04 2:40 
GeneralRe: atlres.h missing PinmemberFerdie22-Jun-04 11:49 
GeneralThanks! it's just the one needed PinmemberMariduke5-Dec-02 5:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 3 Jun 2003
Article Copyright 2002 by Ferdie
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid