Article ID: 198485
Article Last Modified on 1/9/2006
APPLIES TO
- Microsoft Visual C++ 2005 Express Edition
- Microsoft Visual C++ .NET 2003 Standard Edition
- Microsoft Visual C++ .NET 2002 Standard Edition
- Microsoft Visual C++ 6.0 Enterprise Edition
- Microsoft Visual C++ 6.0 Professional Edition
- Microsoft Visual C++ 6.0 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q198485
Note Microsoft Visual C++ .NET 2002 and Microsoft Visual C++ .NET 2003 support both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code. Microsoft Visual C++ 2005 supports both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model.
SUMMARY
The sample below is an OLE DB Consumer Application that demonstrates how to perform a distributed query with Visual C++ 6.0 ATL Consumer templates to retrieve data from SQL Server (using SQLOLEDB Provider) and Microsoft Access Database (using Jet OLEDB Provider) by joining the two NorthWind databases.
The application extracts the PRODUCT ID and PRODUCT NAME from a Products table stored in NorthWind Jet database, joins it with the Products table stored in NorthWind Microsoft SQL Server 7.0 Database, and applies SQL Server ORDER BY to sort the final results.
Briefly, create a .cpp file using the code given below in Visual C++ 6.0 and compile/build from the Build menu (it will prompt for creating a new workspace--this should build and run without adding new code). Change the initialization properties to connect to your SQL 7.0 server. In the OpenRoswset call, change the path to point to NorthWind ("nwind.mdb") sample database on your machine. The sample database is installed by OLE DB SDK, Access, and Visual Basic.
MORE INFORMATION
Microsoft SQL Server 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider',
'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
The SELECT statement performs the SQL statement against the OLE DB Provider.
The following sample code is an OLE DB Consumer Application that uses OLE DB Consumer Template classes. It uses OpenRowset Transact-SQL function and does not require a linked server definition.
Sample code
#include <iostream>
using namespace std ;
#include <atlbase.h>
#include <atlimpl.cpp> // obsolete in versions later than 6.0
CComModule _Module;
#define DBINITCONSTANTS
#include <atlcom.h>
#include <atldbcli.h>
#include <msdaguid.h>
//Accessor Class
class CProductsAccessor
{
public:
LONG m_ProductID;
TCHAR m_ProductName[41];
//output binding map
BEGIN_COLUMN_MAP(CProductsAccessor)
COLUMN_ENTRY(1, m_ProductID)
COLUMN_ENTRY(2, m_ProductName)
END_COLUMN_MAP()
};
void main()
{
HRESULT hr ;
//Initialize COM
hr = CoInitialize(NULL);
CDataSource connection ;
CSession session ;
CCommand<CAccessor<CProductsAccessor> > product ;
// Open connection and session
// ===================================================
// Setting OLE DB Initialization Properties
// ===================================================
CDBPropSet propset(DBPROPSET_DBINIT);
propset.AddProperty(DBPROP_INIT_DATASOURCE, L"<SQL Server Name>");
propset.AddProperty(DBPROP_INIT_CATALOG, L"NorthWind");
propset.AddProperty(DBPROP_AUTH_USERID, L"<User ID>");
propset.AddProperty(DBPROP_AUTH_PASSWORD, L"<Password>");
hr = connection.Open("SQLOLEDB", &propset);
// Open a session.
hr = session.Open(connection) ;
// Perform a distributed join using OpenRowset T-SQL function.
//Note the drive C:\ referenced in the query resides on the server.
hr = product.Open(session, "select P1.ProductID, P1.ProductName
from NorthWind..Products P1,
OpenRowset('Microsoft.Jet.OLEDB.4.0','<nwind.mdb path>';
'<User ID>';'<Password>', 'select ProductID, ProductName from products
where UnitsInStock between 1 and 5')P2
where P1.ProductID = P2.ProductID order by P1.ProductName");
//Get data from the rowset.
while(product.MoveNext() == S_OK)
{
cout << product.m_ProductID << " ";
cout << product.m_ProductName << endl ;
}
}
REFERENCES
For more details on setting up and using distributed queries, see "sp_addlinkedserver," "OpenQuery," "OpenRowset," and related topics in SQL Server 7.0 Books Online.
To learn more about the Accessor used in the code above, run the ATL Consumer Wizard on SQL Server 7 NorthWind database, Products Table.
For more information about the requirements to add OLE DB template support to Visual C++, click the following article number to view the article in the Microsoft Knowledge Base:
190959 OLE DB Consumer Template Wizard requires ATL project
For more information, see the support webcast at the following Microsoft Web site:
Keywords: kbhowto kbdatabase kbdtl KB198485
