OLEDB Providers Enumeration

 

By: AdvanCode [2004-12-01]
Read: 3350 times

The question is: how can we get all OLEDB providers available? This could be done, relatively simply using VC++, but there aren't a simply solution for VB users. So, we'll try to make an ATL component to be used both in VC++ or VB projects.

There are at least 2 solutions for the problem - the first one is to use ADO and the other is using OLE DB interfaces.

Using ADO is simply to return an ADODB.Recordset containing all the informations. An example could be found here (Copyright 2002 Bob Beauchemin). Mainly, this is done like this (after you added ADO support):

STDMETHODIMP CGetProvRS::GetProvRS(VARIANT *pvRecordset){Recordset20Ptr spRs;ADORecordsetConstructionPtr spADOsCt;VariantInit(pvRecordset);CEnumerator cenum;HRESULT hr;// Root enumerator is the defaulthr = cenum.Open();if (FAILED(hr)) goto cleanup;hr = spRs.CreateInstance(__uuidof(Recordset));if (FAILED(hr))goto cleanup;    hr = spRs->QueryInterface(__uuidof(ADORecordsetConstruction), (void **)&spADOsCt);if (FAILED(hr))goto cleanup;hr = spADOsCt->put_Rowset(cenum.m_spRowset);if (FAILED(hr))goto cleanup;pvRecordset->vt = VT_DISPATCH;pvRecordset->pdispVal = (IDispatch*)spRs.Detach();cleanup:cenum.Close();return hr;}

Doing so, the method is returning an ADODB.Recordset that can be used in VB applications, for instance. The names of the fields returned are:

col 0:                           size 4    type DBTYPE_UI4col 1: name SOURCES_NAME         size 255  type DBTYPE_WSTRcol 2: name SOURCES_PARSENAME    size 255  type DBTYPE_WSTRcol 3: name SOURCES_DESCRIPTION  size 255  type DBTYPE_WSTRcol 4: name SOURCES_TYPE         size 2    type DBTYPE_UI2col 5: name SOURCES_ISPARENT     size 2    type DBTYPE_BOOLcol 6: name SOURCES_CLSID        size 255  type DBTYPE_WSTR 

All the details can be found here.

A VB example of using the component could be as below:

    Dim rs As Recordset    Dim provrs As New GetProvRS    Dim i As Integer        Set rs = provrs.GetProvRS()        While Not rs.EOF        For i = 0 To rs.Fields.Count - 1            Debug.Print "Field: " & rs(i).Name & " = " & rs(i).Value        Next i        Debug.Print            rs.MoveNext    Wend

The other solution is by using OLEDB interfaces, directly, as presented in "Teach Yourself Database Programming with Visual C++ 6 in 21 Days" (cap. 17), from Sams Publishing or this example from Microsoft KB (in italian).

Below, there is the source code for an ATL component that return all providers as variant-array, which is simply to use in VB. Create a new component using ATL COM AppWizard and add a new ATL object called "Providers". Include the libraries and the constants as below (in Providers.cpp):

#include <oledb.h>#include <oledberr.h>#include <MSDAGUID.H>typedef struct EnumProvidersRow{   UINT num;   char SourceName[255];   char ParseName[255];   char SourceDesc[255];   USHORT SourceType;   BOOL IsParent;   char SourceCLSID[255];} ENUMPROVIDERSROW;#define COLS 7#define ROWS 10

Then add the following code in a new created method called GetProviders([out, retval] VARIANT* res):

STDMETHODIMP CProviders::GetProviders(VARIANT *res){// TODO: Add your implementation code here   ISourcesRowset* pSrcRowset;   IRowset* pRowset;   IAccessor* pAccessor;   HACCESSOR hAccessor;   ENUMPROVIDERSROW Rows;   DBBINDING Binding[COLS];   DBBINDSTATUS BindingStatus[COLS];   ULONG Feched;   HROW* phRow = new HROW[ROWS];   ZeroMemory( &Binding[0], sizeof(DBBINDING) * COLS);   HRESULT hr = CoCreateInstance(CLSID_OLEDB_ENUMERATOR, NULL, CLSCTX_INPROC_SERVER, IID_ISourcesRowset, (LPVOID*)&pSrcRowset);   if( hr == S_OK )   {      hr = pSrcRowset->GetSourcesRowset( NULL, IID_IRowset, 0, NULL, (LPUNKNOWN*)&pRowset);      pSrcRowset->Release();            if(  hr == S_OK )      {         hr = pRowset->QueryInterface(IID_IAccessor, (LPVOID*)&pAccessor);         if(  hr == S_OK )         {            Binding[0].iOrdinal = 0;            Binding[0].obValue = offsetof(ENUMPROVIDERSROW, num);            Binding[0].dwPart = DBPART_VALUE;            Binding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[0].eParamIO = DBPARAMIO_NOTPARAM;            Binding[0].wType = DBTYPE_UI4;            Binding[1].iOrdinal = 1;            Binding[1].obValue = offsetof(ENUMPROVIDERSROW, SourceName);            Binding[1].dwPart = DBPART_VALUE;            Binding[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[1].eParamIO = DBPARAMIO_NOTPARAM;            Binding[1].wType = DBTYPE_STR;            Binding[1].cbMaxLen = 255;            Binding[2].iOrdinal = 2;            Binding[2].obValue = offsetof(ENUMPROVIDERSROW, ParseName);            Binding[2].dwPart = DBPART_VALUE;            Binding[2].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[2].eParamIO = DBPARAMIO_NOTPARAM;            Binding[2].wType = DBTYPE_STR;            Binding[2].cbMaxLen = 255;            Binding[3].iOrdinal = 3;            Binding[3].obValue = offsetof(ENUMPROVIDERSROW, SourceDesc);            Binding[3].dwPart = DBPART_VALUE;            Binding[3].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[3].eParamIO = DBPARAMIO_NOTPARAM;            Binding[3].wType = DBTYPE_STR;            Binding[3].cbMaxLen = 255;            Binding[4].iOrdinal = 4;            Binding[4].obValue = offsetof(ENUMPROVIDERSROW, SourceType);            Binding[4].dwPart = DBPART_VALUE;            Binding[4].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[4].eParamIO = DBPARAMIO_NOTPARAM;            Binding[4].wType = DBTYPE_UI2;            Binding[5].iOrdinal = 5;            Binding[5].obValue = offsetof(ENUMPROVIDERSROW, IsParent);            Binding[5].dwPart = DBPART_VALUE;            Binding[5].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[5].eParamIO = DBPARAMIO_NOTPARAM;            Binding[5].wType = DBTYPE_BOOL;            Binding[6].iOrdinal = 6;            Binding[6].obValue = offsetof(ENUMPROVIDERSROW, SourceCLSID);            Binding[6].dwPart = DBPART_VALUE;            Binding[6].dwMemOwner = DBMEMOWNER_CLIENTOWNED;            Binding[6].eParamIO = DBPARAMIO_NOTPARAM;            Binding[6].wType = DBTYPE_STR;            Binding[6].cbMaxLen = 255;                        hr = pAccessor->CreateAccessor( DBACCESSOR_ROWDATA, COLS, &Binding[0], 0, &hAccessor, &BindingStatus[0]);                        if(  hr == S_OK )            {               hr = pRowset->RestartPosition( DB_NULL_HCHAPTER );               while(true)               {                  hr = pRowset->GetNextRows( DB_NULL_HCHAPTER, 0, ROWS, &Feched, &phRow);                  if(  (hr == S_OK || hr == DB_S_ENDOFROWSET) && Feched )                  { SAFEARRAYBOUND MyBound[2]; MyBound[0].cElements = Feched-2; //first item lost MyBound[1].cElements = Feched-2; MyBound[0].lLbound  = 0; MyBound[1].lLbound = 0;  res->vt = VT_VARIANT | VT_ARRAY;  res->parray = SafeArrayCreate(VT_VARIANT,2,MyBound); long MyPosition[2]; VARIANT tmp; VariantInit(&tmp);                     for(int i=0; i<(int)Feched; i++)                     {                        pRowset->GetData( phRow[i], hAccessor, (LPVOID)&Rows);MyPosition[0] = i;MyPosition[1] = 0;tmp.vt =VT_BSTR;tmp.bstrVal = SysAllocString(A2BSTR(Rows.SourceName));SafeArrayPutElement(res->parray,MyPosition,&tmp);MyPosition[1] = 1;SysFreeString(tmp.bstrVal);tmp.bstrVal = SysAllocString(A2BSTR(Rows.ParseName));SafeArrayPutElement(res->parray,MyPosition,&tmp);MyPosition[1] = 2;SysFreeString(tmp.bstrVal);tmp.bstrVal = SysAllocString(A2BSTR(Rows.SourceDesc));SafeArrayPutElement(res->parray,MyPosition,&tmp);MyPosition[1] = 3;SysFreeString(tmp.bstrVal);tmp.vt = VT_I2;tmp.iVal = Rows.SourceType ;SafeArrayPutElement(res->parray,MyPosition,&tmp);MyPosition[1] = 4;tmp.vt = VT_BOOL;tmp.boolVal  = Rows.IsParent ;SafeArrayPutElement(res->parray,MyPosition,&tmp);MyPosition[1] = 5;tmp.vt = VT_BSTR;tmp.bstrVal = SysAllocString(A2BSTR(Rows.SourceCLSID));SafeArrayPutElement(res->parray,MyPosition,&tmp);SysFreeString(tmp.bstrVal);                     }                     pRowset->ReleaseRows( Feched, phRow, NULL, NULL, NULL);                     if( hr == DB_S_ENDOFROWSET)                        break;                  }                  else                     break;               }               pAccessor->ReleaseAccessor( hAccessor, NULL);            }            pAccessor->Release();         }         pRowset->Release();      }   }   delete[] phRow;   return hr;}

To use it in a VB project is straightforward - add a reference to "AllProvs 1.0 Type Library" and add the code:

Dim x As New ALLPROVSLib.ProvidersPrivate Sub Command1_Click()Dim a As Varianta = x.GetProvidersFor i = 0 To UBound(a, 1)    For j = 0 To UBound(a, 2)        Debug.Print a(i, j) & " - ";    NextDebug.PrintNextEnd Sub

原文网址：http://www.advancode.com/articles.php?page=oledbproviders