Creating an OLE DB Provider

It's pretty obvious how OLE DB consumers are useful. You just ask a wizard to create a wrapper for you, and you get a fairly easy way to access the data in a database. However, it might be a bit less obvious why you'd want to create an OLE DB provider.

Why Write an OLE DB Provider?

Writing an OLE DB allows you to insert a layer between a client of some data and the actual data itself. Here are just a few reasons you might want to write a provider.

• Writing an OLE DB provider means clients don't necessarily touch the data directly. Therefore, you can add additional capabilities to your data, such as query processing.

• In some cases, writing an OLE DB provider gives you the opportunity to increase data access performance by controlling how the data is manipulated.

• Adding an OLE DB provider layer increases the potential audience of your data. For example, if you have a proprietary data format that can be accessed by only one programming language, you have a single point of failure. OLE DB providers give you a way to open that proprietary format to a wider variety of programmers, regardless of the programming language they use.

Working with the OLE DB Providers is similar to working with the Consumers. The wizards do a lot of the work for you. You just need to know how to work with the generated classes. The steps for creating an OLE DB Provider are listed here.

1. The first step is to decide what you want the provider to do. Remember the philosophy behind OLE DB: it's all about providing a singular way to access multiple data sources. For example, you might want to write a provider that recursively enumerates the contents of a structured storage file. Or you might want a provider that sifts through e-mail folders and allows clients database-style access to your e-mail system. The possibilities are nearly endless.

2. Just as you did when writing a data consumer, use the ATL Object Wizard to create a provider. Just start the ATL Object Wizard from ClassView or from the Insert menu. Select the Data Access objects category, and choose Provider. The ATL Object Wizard will ask you to provide a name for your object and will allow you to modify the default names for the files it will create.

3. After you click OK, the ATL Object Wizard creates the code for a provider, including a data source, a rowset, and a session. In addition to these objects, a provider supports one or more properties, which are defined in property maps within the files created by the OLE DB Provider Template Wizard. When the Wizard creates the files, it inserts maps for the properties belonging to the OLE DB property group defined for the object or objects included in those files. For example, the header file containing the data source object also contains the property map for the DataSource properties. The session header file contains the property map for the Session properties. Finally, the rowset and command objects reside in a single header file, which includes properties for the command object.

For example, here's what the ATL Object Wizard produces for an OLE DB provider named AProvider. First the ATL Object Wizard creates a data source object, which lives in a file named AProviderDS.H:

class ATL_NO_VTABLE CAProviderSource : 
    public CComObjectRootEx<CComSingleThreadModel>,
    public CComCoClass<CAProviderSource, &CLSID_AProvider>,
    public IDBCreateSessionImpl<CAProviderSource, CAProviderSession>,
    public IDBInitializeImpl<CAProviderSource>,
    public IDBPropertiesImpl<CAProviderSource>,
    public IPersistImpl<CAProviderSource>,
    public IInternalConnectionImpl<CAProviderSource>
{
public:
    HRESULT FinalConstruct()
    {
        return FInit();
    }
DECLARE_REGISTRY_RESOURCEID(IDR_APROVIDER)
BEGIN_PROPSET_MAP(CAProviderSource)
    BEGIN_PROPERTY_SET(DBPROPSET_DATASOURCEINFO)
        PROPERTY_INFO_ENTRY(ACTIVESESSIONS)
        PROPERTY_INFO_ENTRY(DATASOURCEREADONLY)
        PROPERTY_INFO_ENTRY(BYREFACCESSORS)
        PROPERTY_INFO_ENTRY(OUTPUTPARAMETERAVAILABILITY)
        PROPERTY_INFO_ENTRY(PROVIDEROLEDBVER)
        PROPERTY_INFO_ENTRY(DSOTHREADMODEL)
        PROPERTY_INFO_ENTRY(SUPPORTEDTXNISOLEVELS)
        PROPERTY_INFO_ENTRY(USERNAME)
    END_PROPERTY_SET(DBPROPSET_DATASOURCEINFO)
    BEGIN_PROPERTY_SET(DBPROPSET_DBINIT)
        PROPERTY_INFO_ENTRY(AUTH_PASSWORD)
        PROPERTY_INFO_ENTRY(AUTH_PERSIST_SENSITIVE_AUTHINFO)
        PROPERTY_INFO_ENTRY(AUTH_USERID)
        PROPERTY_INFO_ENTRY(INIT_DATASOURCE)
        PROPERTY_INFO_ENTRY(INIT_HWND)
        PROPERTY_INFO_ENTRY(INIT_LCID)
        PROPERTY_INFO_ENTRY(INIT_LOCATION)
        PROPERTY_INFO_ENTRY(INIT_MODE)
        PROPERTY_INFO_ENTRY(INIT_PROMPT)
        PROPERTY_INFO_ENTRY(INIT_PROVIDERSTRING)
        PROPERTY_INFO_ENTRY(INIT_TIMEOUT)
    END_PROPERTY_SET(DBPROPSET_DBINIT)
    CHAIN_PROPERTY_SET(CAProviderCommand)
END_PROPSET_MAP()
BEGIN_COM_MAP(CAProviderSource)
    COM_INTERFACE_ENTRY(IDBCreateSession)
    COM_INTERFACE_ENTRY(IDBInitialize)
    COM_INTERFACE_ENTRY(IDBProperties)
    COM_INTERFACE_ENTRY(IPersist)
    COM_INTERFACE_ENTRY(IInternalConnection)
END_COM_MAP()
public:
};

In addition to the data object, the ATL Object Wizard produces a command object and a rowset that both live within AProviderRS.H:

class ATL_NO_VTABLE CAProviderCommand : 
    public CComObjectRootEx<CComSingleThreadModel>,
    public IAccessorImpl<CAProviderCommand>,
    public ICommandTextImpl<CAProviderCommand>,
    public ICommandPropertiesImpl<CAProviderCommand>,
    public IObjectWithSiteImpl<CAProviderCommand>,
    public IConvertTypeImpl<CAProviderCommand>,
    public IColumnsInfoImpl<CAProviderCommand>
{
public:
BEGIN_COM_MAP(CAProviderCommand)
    COM_INTERFACE_ENTRY(ICommand)
    COM_INTERFACE_ENTRY(IObjectWithSite)
    COM_INTERFACE_ENTRY(IAccessor)
    COM_INTERFACE_ENTRY(ICommandProperties)
    COM_INTERFACE_ENTRY2(ICommandText, ICommand)
    COM_INTERFACE_ENTRY(IColumnsInfo)
    COM_INTERFACE_ENTRY(IConvertType)
END_COM_MAP()
// ICommand
public:
    HRESULT FinalConstruct()
    {
        HRESULT hr = CConvertHelper::FinalConstruct();
        if (FAILED (hr))
            return hr;
        hr = IAccessorImpl<CAProviderCommand>::FinalConstruct();
        if (FAILED(hr))
            return hr;
        return CUtlProps<CAProviderCommand>::FInit();
    }
    void FinalRelease()
    {
        IAccessorImpl<CAProviderCommand>::FinalRelease();
    }
    HRESULT WINAPI Execute(IUnknown * pUnkOuter, 
                           REFIID riid, DBPARAMS * pParams, 
                           LONG * pcRowsAffected, 
                           IUnknown ** ppRowset);
    static ATLCOLUMNINFO* GetColumnInfo(CAProviderCommand* pv,
                                        ULONG* pcInfo)
    {
        return CAProviderWindowsFile::GetColumnInfo(pv,pcInfo);
    }
BEGIN_PROPSET_MAP(CAProviderCommand)
    BEGIN_PROPERTY_SET(DBPROPSET_ROWSET)
        PROPERTY_INFO_ENTRY(IAccessor)
        PROPERTY_INFO_ENTRY(IColumnsInfo)
        PROPERTY_INFO_ENTRY(IConvertType)
        PROPERTY_INFO_ENTRY(IRowset)
        PROPERTY_INFO_ENTRY(IRowsetIdentity)
        PROPERTY_INFO_ENTRY(IRowsetInfo)
        PROPERTY_INFO_ENTRY(IRowsetLocate)
        PROPERTY_INFO_ENTRY(BOOKMARKS)
        PROPERTY_INFO_ENTRY(BOOKMARKSKIPPED)
        PROPERTY_INFO_ENTRY(BOOKMARKTYPE)
        PROPERTY_INFO_ENTRY(CANFETCHBACKWARDS)
        PROPERTY_INFO_ENTRY(CANHOLDROWS)
        PROPERTY_INFO_ENTRY(CANSCROLLBACKWARDS)
        PROPERTY_INFO_ENTRY(LITERALBOOKMARKS)
        PROPERTY_INFO_ENTRY(ORDEREDBOOKMARKS)
    END_PROPERTY_SET(DBPROPSET_ROWSET)
END_PROPSET_MAP()
};

class RAProviderRowset : public CRowsetImpl<RAProviderRowset, 
                                            CWindowsFile, 
                                            CAProviderCommand>
{
public:
    HRESULT Execute(DBPARAMS * pParams, LONG* pcRowsAffected)
    {
        USES_CONVERSION;
        BOOL bFound = FALSE;
        HANDLE hFile;
        LPTSTR  szDir = (m_strCommandText == _T("")) ? _T("*.*") : 
                         OLE2T(m_strCommandText);
        CAProviderWindowsFile wf;
        hFile = FindFirstFile(szDir, &wf);
        if (hFile == INVALID_HANDLE_VALUE)
            return DB_E_ERRORSINCOMMAND;
        LONG cFiles = 1;
        BOOL bMoreFiles = TRUE;
        while (bMoreFiles)
        {
            if (!m_rgRowData.Add(wf))
                return E_OUTOFMEMORY;
            bMoreFiles = FindNextFile(hFile, &wf);
            cFiles++;
        }
        FindClose(hFile);
        if (pcRowsAffected != NULL)
            *pcRowsAffected = cFiles;
        return S_OK;
    }
};

The ATL Object Wizard produces a session object in a file named AProviderSess.H as shown in this code:

class ATL_NO_VTABLE CAProviderSession : 
    public CComObjectRootEx<CComSingleThreadModel>,
    public IGetDataSourceImpl<CAProviderSession>,
    public IOpenRowsetImpl<CAProviderSession>,
    public ISessionPropertiesImpl<CAProviderSession>,
    public IObjectWithSiteSessionImpl<CAProviderSession>,
    public IDBSchemaRowsetImpl<CAProviderSession>,
    public IDBCreateCommandImpl<CAProviderSession, CAProviderCommand>
{
public:
    CAProviderSession()
    {
    }
    HRESULT FinalConstruct()
    {
        return FInit();
    }
    STDMETHOD(OpenRowset)(IUnknown *pUnk, DBID *pTID, 
                          DBID *pInID, REFIID riid,
                          ULONG cSets, DBPROPSET rgSets[], 
                          IUnknown **ppRowset)
    {
        CAProviderRowset* pRowset;
        return CreateRowset(pUnk, pTID, pInID, riid, 
                            cSets, rgSets, ppRowset, pRowset);
    }
BEGIN_PROPSET_MAP(CAProviderSession)
    BEGIN_PROPERTY_SET(DBPROPSET_SESSION)
        PROPERTY_INFO_ENTRY(SESS_AUTOCOMMITISOLEVELS)
    END_PROPERTY_SET(DBPROPSET_SESSION)
END_PROPSET_MAP()
BEGIN_COM_MAP(CAProviderSession)
    COM_INTERFACE_ENTRY(IGetDataSource)
    COM_INTERFACE_ENTRY(IOpenRowset)
    COM_INTERFACE_ENTRY(ISessionProperties)
    COM_INTERFACE_ENTRY(IObjectWithSite)
    COM_INTERFACE_ENTRY(IDBCreateCommand)
    COM_INTERFACE_ENTRY(IDBSchemaRowset)
END_COM_MAP()
BEGIN_SCHEMA_MAP(CAProviderSession)
    SCHEMA_ENTRY(DBSCHEMA_TABLES, CAProviderSessionTRSchemaRowset)
    SCHEMA_ENTRY(DBSCHEMA_COLUMNS, CAProviderSessionColSchemaRowset)
    SCHEMA_ENTRY(DBSCHEMA_PROVIDER_TYPES, CAProviderSessionPTSchemaRowset)
END_SCHEMA_MAP()
};
class CAProviderSessionTRSchemaRowset : 
    public CRowsetImpl< CAProviderSessionTRSchemaRowset, 
                        CTABLESRow, CAProviderSession>
{
public:
    HRESULT Execute(LONG* pcRowsAffected, ULONG, const VARIANT*)
    {
        USES_CONVERSION;
        CAProviderWindowsFile wf;
        CTABLESRow trData;
        lstrcpyW(trData.m_szType, OLESTR("TABLE"));
        lstrcpyW(trData.m_szDesc, OLESTR("The Directory Table"));
        HANDLE hFile = INVALID_HANDLE_VALUE;
        TCHAR szDir[MAX_PATH + 1];
        DWORD cbCurDir = GetCurrentDirectory(MAX_PATH, szDir);
        lstrcat(szDir, _T("\\*.*"));
        hFile = FindFirstFile(szDir, &wf);
        if (hFile == INVALID_HANDLE_VALUE)
            return E_FAIL; // User doesn't have a c:\ drive
        FindClose(hFile);
        lstrcpynW(trData.m_szTable, T2OLE(szDir), 
                  SIZEOF_MEMBER(CTABLESRow, m_szTable));
        if (!m_rgRowData.Add(trData))
            return E_OUTOFMEMORY;
        *pcRowsAffected = 1;
        return S_OK;
    }
};
class CAProviderSessionColSchemaRowset : 
    public CRowsetImpl< CAProviderSessionColSchemaRowset, 
                        CCOLUMNSRow, CAProviderSession>
{
public:
    HRESULT Execute(LONG* pcRowsAffected, ULONG, const VARIANT*)
    {
        USES_CONVERSION;
        CAProviderWindowsFile wf;
        HANDLE hFile = INVALID_HANDLE_VALUE;
           TCHAR szDir[MAX_PATH + 1];
        DWORD cbCurDir = GetCurrentDirectory(MAX_PATH, szDir);
        lstrcat(szDir, _T("\\*.*"));
        hFile = FindFirstFile(szDir, &wf);
        if (hFile == INVALID_HANDLE_VALUE)
            return E_FAIL; // User doesn't have a c:\ drive
        FindClose(hFile);// szDir has got the tablename
        DBID dbid;
        memset(&dbid, 0, sizeof(DBID));
        dbid.uName.pwszName = T2OLE(szDir);
        dbid.eKind = DBKIND_NAME;
        return InitFromRowset <RowsetArrayType> (m_rgRowData, 
                                                 &dbid, 
                                                 NULL, 
                                                 m_spUnkSite, 
                                                 pcRowsAffected);
    }
};
class CAProviderSessionPTSchemaRowset : 
    public CRowsetImpl<CAProviderSessionPTSchemaRowset, 
                       CPROVIDER_TYPERow, CAProviderSession>
{
public:
    HRESULT Execute(LONG* pcRowsAffected, ULONG, const VARIANT*)
    {
        return S_OK;
    }
};

As with most Wizard-generated code, the OLE DB Provider code generated by the ATL Object Wizard is just boilerplate code—it doesn't do very much. You need to take several steps to turn this boilerplate code into a real OLE DB Provider. The two critical pieces that need to be added to a provider are the user record and code to manage a data set and to set the data up as rows and columns.

• The user record The ATL Object Wizard provides a default user record named CAProviderWindowsFile. You don't really want to use this user record. You'll probably scrap it and replace it with something useful in your domain. As a simple example, imagine you want to write an OLE DB Provider that enumerates the compound file. Your user record might look like this:

  struct CStgInfo {
  BEGIN_PROVIDER_COLUMN_MAP(CStgInfo)
      PROVIDER_COLUMN_ENTRY("StgName", 1, szName)
      PROVIDER_COLUMN_ENTRY("Size", 2, cbSizeLow)
      PROVIDER_COLUMN_ENTRY("Size", 2, cbSizeHigh)
  
  END_PROVIDER_COLUMN_MAP()
  
      OLECHAR szName[256];
      long cbSizeLow;
      long cbSizeHigh;
  };

  This structure contains the data fields for the name and size of the substorage. The provider column map macros map the data into columns. You could actually derive the structure from a STATSTG structure (used to enumerate structured storages). You just need to add entries to the provider column map to handle the members.

• Code to open the data set The other important addition to the provider is the code necessary to open the data set. This happens in the rowset's Execute function. There are many different kinds of functionality that can go on here. For example, if you want to enumerate the top-level substorages in a compound file, you'd first open the storage and then enumerate the contents as shown in the following code snippet:

  class RStgInfoProviderRowset : 
      public CRowsetImpl<RStgInfoProviderRowset, 
                         CStgInfo, 
                         CStgInfoProviderCommand>
  {
  public:
      HRESULT Execute(DBPARAMS * pParams, LONG* pcRowsAffected)
      {
          USES_CONVERSION;
          LPTSTR  szFile = 
                 m_strCommandText == _T("")) ? _T("") :
                     OLE2T(m_strCommandText);
  
          IStorage* pStg = NULL;
  
          HRESULT hr = StgOpenStorage(szFile, NULL, 
                                      STGM_READ|STGM_SHARE_EXCLUSIVE, 
                                      NULL, NULL, &pStg);
  
          if(FAILED(hr))
              return DB_E_ERRORSINCOMMAND;
  
          LONG cStgs = 0;
  
          IEnumSTATSTG* pEnumSTATSTG;
  
          hr = pStg->EnumElements(0, 0, 0, &pEnumSTATSTG);
  
          if(pEnumSTATSTG) {
  
              STATSTG rgSTATSTG[100];
              ULONG nFetched;
  
              hr = pEnumSTATSTG->Next(100, rgSTATSTG, &nFetched);
  
              for(ULONG i = 0; i < nFetched; i++) {
                  CStgInfo stgInfo;
  
                  stgInfo.cbSizeLow = rgSTATSTG[i].cbSize.LowPart;
                  stgInfo.cbSizeHigh = rgSTATSTG[i].cbSize.HighPart;
  
                  wcsncpy(stgInfo.szName, 
                          rgSTATSTG[i].pwcsName,
                          255);
  
                  CoTaskMemFree(rgSTATSTG[i].pwcsName);
  
                  if (!m_rgRowData.Add(stgInfo))
                      return E_OUTOFMEMORY;
                  cStgs++;
              }
              pEnumSTATSTG->Release();
          }
  
          if(pStg)
              pStg->Release();
  
          if (pcRowsAffected != NULL)
              *pcRowsAffected = cStgs;
          return S_OK;
      }
  }

  When some client code tries to open the OLE DB data provider, the call ends up inside this function. This function simply opens the structured storage file passed in as the command text and uses the standard structured storage enumerator to find the top-level substorages. Then the Execute function stores the name of the substorage and the size of the substorage in an array. The OLE DB provider uses this array to fulfill requests for the column data.

Of course, there's a lot you can do to beef up this OLE DB provider. We've barely scratched the surface of what you can do with a provider. When the ATL Object Wizard pumps out the default provider, it's a read-only provider. That is, users cannot change the contents of the data. In addition, the OLE DB templates provide support for locating rowsets and setting bookmarks. In most cases, enhancing the provider is a matter of tacking on implementations of COM interfaces provided by the OLE DB templates.