HOWTO: Using the ADO OpenSchema Method from Visual C++

This article was previously published under Q182831
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
This article demonstrates how to use the ActiveX Data Objects (ADO)OpenSchema method from Visual C++ using the #import feature. OpenSchema isa method of ADO the Connection object that obtains database schemainformation from the OLEDB provider.
MORE INFORMATION
OpenSchema returns information about the data source, such as informationabout the tables on the server and the columns in the tables. OpenSchema returns the schema information as a resultset.

OpenSchema has three parameters. The first parameter is an enumerated valuespecifying the type of the schema required. The second parameter is avariant array. The number of elements and the contents in the array dependon the type of the schema query to be run. You can use this parameter torestrict the number of rows returned in the resultset. However, you cannotlimit the number of columns returned by OpenSchema. The third parameter isrequired only if the first parameter is set to adSchemaProviderSpecific;otherwise, it is not used.

For additional information about Using Stored Procedures with ADO, please see the following article(s) in the Microsoft Knowledge Base:
184968 FILE: Adovcsp.exe Demonstrates Using Stored Procedures with ADO
The following code demonstrates using OpenSchema to get information aboutthe primary key of a table (#IMPORT statement is not shown here):
   void OpenSchemaForPrimaryKey()      {      ADODB::_ConnectionPtr  Con;      ADODB::_RecordsetPtr   Rs1;      CString csTemp;      _variant_t varCriteria[3];      LONG lIndex = 0;      HRESULT hr = 0;      int nIndex;      try      {      // Getting primary key information for table "Authors".      _bstr_t  mydatabase("pubs");      _bstr_t  mydbo("dbo");      _bstr_t  mytable("authors");      varCriteria[0] = mydatabase;      varCriteria[1] = mydbo;      varCriteria[2] = mytable;      // Creating a safearray of variants with three elements.      VARIANT varData;      SAFEARRAY FAR* psa;      SAFEARRAYBOUND rgsabound[1];      rgsabound[0].lLbound = 0;      rgsabound[0].cElements = 3;      psa = SafeArrayCreate(VT_VARIANT, 1, rgsabound);      // Fill the safe array.      for( lIndex = 0 ; lIndex < 3 ;lIndex++)      {        hr  = SafeArrayPutElement(psa, &lIndex,&varCriteria[lIndex]);      }      // Initialize the safearray.      varData.vt = VT_VARIANT | VT_ARRAY;      V_ARRAY(&varData) = psa;        Con.CreateInstance(__uuidof(ADODB::Connection));        Con->Open(L"DSN=pubs", L"", L"", -1L);                             Rs1 = Con->OpenSchema(ADODB::adSchemaPrimaryKeys,varData);        int nFieldCount = Rs1->Fields->GetCount();        VARIANT varValue;        while (VARIANT_TRUE != Rs1->GetadoEOF())        {          csTemp.Empty();          // Traversing through the Fields collection to get the values.          for(nIndex = 0 ; nIndex < nFieldCount ; nIndex++)          {         if(!csTemp.IsEmpty())         csTemp += " ; ";         csTemp += Rs1->Fields->GetItem(_variant_t((long)nIndex))->Name;           csTemp += " = ";         varValue = Rs1->Fields->GetItem(_variant_t((long)nIndex))->Value;              if(varValue.vt == VT_BSTR)                 csTemp += varValue.bstrVal;              else              if(varValue.vt == VT_UI4)                  csTemp.Format("%s %l",csTemp,varValue.lVal);      } // End of For loop.      AfxMessageBox(csTemp);      Rs1->MoveNext();       } // End of While loop.       } // End of Try block.      catch(_com_error &e)       {       // Exception handling.       DumpError(e);       }      } // End of function.				
The following code demonstrates using the OpenSchema method to retrieve all table names present in the database. The function excludes system tables and views.
    // Function that gets all table names & excludes System tables and views      void OpenSchemaTables()      {          ADODB::_ConnectionPtr  Con;          ADODB::_RecordsetPtr   Rs1;          CString csTemp;          _variant_t varCriteria[4];          LONG lIndex = 0;          HRESULT hr = 0;          try          {             // Getting primary key information for table "Authors".             _bstr_t bstrTableType("Table");             varCriteria[0].vt = VT_EMPTY;             varCriteria[1].vt = VT_EMPTY;             varCriteria[2].vt = VT_EMPTY;             varCriteria[3] = bstrTableType;             VARIANT varData;             SAFEARRAY FAR* psa;             SAFEARRAYBOUND rgsabound[1];             rgsabound[0].lLbound = 0;             rgsabound[0].cElements = 4;             psa = SafeArrayCreate(VT_VARIANT, 1, rgsabound);             // Fill the safe array.             for( lIndex = 0 ; lIndex < 4 ;lIndex++)             {                hr  = SafeArrayPutElement(psa, &lIndex,&varCriteria[lIndex]);             }             // Initialize variant with safearray.             varData.vt = VT_VARIANT | VT_ARRAY;             V_ARRAY(&varData) = psa;                          Con.CreateInstance(__uuidof(ADODB::Connection));             Con->Open(L"DSN=pubs", L"", L"", -1L);             Rs1 = Con->OpenSchema(ADODB::adSchemaTables,varData);             int nFieldCount = Rs1->Fields->GetCount();             VARIANT varValue;             csTemp.Empty();             while (VARIANT_TRUE != Rs1->GetadoEOF())             {                // Traversing through the Fields collection to get the values.                if(!csTemp.IsEmpty())                  csTemp += " ; ";                varValue = Rs1->Fields->GetItem(_variant_t((long)2))->Value;                if(varValue.vt == VT_BSTR)                  csTemp += varValue.bstrVal;                else                if(varValue.vt == VT_UI4)                  csTemp.Format("%s %l",csTemp,varValue.lVal);                Rs1->MoveNext();             }             OutputDebugString(csTemp);          }          catch(_com_error &e)          {             // Exception handling.             DumpError(e);          }      }      // Utility function that gets error information from _com_error and      // displays an error message box.      void DumpError(_com_error &e)      {         _bstr_t bstrSource(e.Source());         _bstr_t bstrDescription(e.Description());         CString str;         str.Format("\tCode = %08lx", e.Error());         str += " Msg: ";   str += e.ErrorMessage();         str += " Source: "; str += bstrSource;         str += " Description: "; str += bstrDescription;         AfxMessageBox(str);      }				
REFERENCES
For additional information about using ADO with Visual C++, please see the following articles in the Microsoft Knowledge Base:
182389 FILE: Adovcbm.exe ADO 1.5 with #import and Getrows/Bookmarks
184968 FILE: Adovcsp.exe Demonstrates Using Stored Procedures with ADO
186387 SAMPLE: Ado2atl.exe Returns ADO Interfaces from COM
181733 FILE: Adovcbtd.exe #import Using UpdateBatch and CancelBatch
Properties

Article ID: 182831 - Last Review: 02/24/2014 08:32:39 - Revision: 2.2

  • Microsoft ActiveX Data Objects 1.5
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Data Access Components 2.7
  • kbnosurvey kbarchive kbdatabase kbhowto KB182831
Feedback