HOWTO: Using the ADO OpenSchema Method from Visual C++ID: Q182831
|
This article demonstrates how to use the ActiveX Data Objects (ADO) OpenSchema method from Visual C++ using the #import feature. OpenSchema is a method of ADO the Connection obtains database schema information from the OLEDB provider.
OpenSchema returns information about the data source, such as information
about 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 value
specifying the type of the schema required. The second parameter is a
variant array. The number of elements and the contents in the array depend
on the type of the schema query to be run. You can use this parameter to
restrict the number of rows returned in the resultset. However, you cannot
limit the number of columns returned by OpenSchema. The third parameter is
required 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:
Q184968 FILE: Adovcsp.exe Demonstrates Using Stored Procedures with ADOThe following code demonstrates using OpenSchema to get information about the primary key of a table (#IMPORT statement is not shown here):
// Code showing usage of OpenSchema to get the primary key of a table.
void OpenSchemaForPrimaryKey()
{
ADODB::_ConnectionPtr Con;
ADODB::_RecordsetPtr Rs1;
CString csTemp;
_variant_t varCriteria[3];
LONG lIndex = 0;
HRESULT hr = 0;
int nIndex;
// Getting primary key information for table "Authors".
_bstr_t mydatabase("pubs");
_bstr_t mydbo("dbo");
_bstr_t mytable("authors");
varCriteria[0].vt = VT_BSTR;
varCriteria[0].bstrVal = mydatabase;
varCriteria[1].vt = VT_BSTR;
varCriteria[1].bstrVal = mydbo;
varCriteria[2].vt = VT_BSTR;
varCriteria[2].bstrVal = 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(Connection));
Con->Open(L"DSN=pubs", L"", L"", -1L);
Rs1 = Con->OpenSchema(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.
// Function that gets all table names & excludes System tables and views
void OpenSchemaTables()
{
ADODB::_ConnectionPtr Con;
ADODB::_RecordsetPtr Rs;
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].vt = VT_BSTR;
varCriteria[3].bstrVal = 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(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();
}
OutDebugString(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);
} For additional information about using ADO with Visual C++, please see the following articles in the Microsoft Knowledge Base:
Q182389 FILE: Adovcbm.exe ADO 1.5 with #import and Getrows/Bookmarks
Q184968 FILE: Adovcsp.exe Demonstrates Using Stored Procedures with ADO
Q186387 SAMPLE: Ado2atl.exe Returns ADO Interfaces from COM
Q181733 FILE: Adovcbtd.exe #import Using UpdateBatch and CancelBatch
Additional query words:
Keywords : kbADO150 kbADO200 kbDatabase kbSweepNext
Version : WINDOWS:1.5,2.0
Platform : WINDOWS
Issue type : kbhowto Last Reviewed: May 24, 1999