How To Invoke OLE DB Service Component Within an OLE DB Application (253803)

SUMMARY

In Microsoft OLE DB 1.x, consumers generally connect to a provider through the root enumerator, or they pass the provider's CLSID to CoCreateInstance, as in this example:

//Create an instance of CLSID_MSDASQL

CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize,(void**)pIDBInitialize);

To take advantage of the common services that are provided as part of OLE DB version 2.0 or OLE DB version 2.5 or 2.6, OLE DB consumers need to create provider instances by calling methods in either the IDataInitialize or the IDBPromptInitialize interfaces. The DataInitialize interface is supported through the Service Component Manager, which can be instantiated with CLSID_MSDAINITIALIZE. The IDBPromptInitialize interface is supported by the Data Links component, which can be instantiated with CLSID_DataLinks.

For example, to create an instance of a provider that can take advantage of OLE DB 2.0 services, based on the provider's CLSID, the code would look like this:

//Create an instance of the OLE DB Initialization Component

CoCreateInstance(CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER,
IID_IDataInitialize,(void**)&pIDataInitialize);

//Create an instance of CLSID_MSDASQL with supported Services
pIDataInitialize->CreateDBInstance(CLSID_MSDASQL, NULL,
CLSCTX_INPROC_SERVER,NULL, IID_IDBInitialize,(IUnknown**)&pIDBInitialize);

OLE DB services are automatically invoked any time the consumer creates an OLE DB data source object through IDataInitialize or IDBPromptInitialize.

NOTE: OLE DB Services are automatically invoked by default when you use Microsoft ActiveX Data Objects (ADO).

MORE INFORMATION

How To Enable or Disable Services for a Provider

You can enable or disable individual OLE DB services, by default, for all applications that access a single provider. To do this, add an OLEDB_SERVICES registry entry under the provider's CLSID, with a DWORD value that specifies the services to enable or disable as follows:

Default Services Enabled	Keyword Value
All services (the default)	0xffffffff
All except Pooling and AutoEnlistment	0xfffffffe
All except Client Cursor	0xfffffffb
All except Pooling, Enlistment, and Cursor	0xfffffff0
No services	0x00000000
No aggregation, all services disabled	<missing key>

How to Override Provider Service Defaults

The provider's registry value for OLEDB_SERVICES is returned as the default value for the DBPROP_INIT_OLEDBSERVICES initialization property on the data source object.

As long as the registry entry exists, the provider's objects are aggregated and the user can override the provider's default setting for enabled services by setting the DBPROP_INIT_OLEDBSERVICES property prior to initialization. To enable or disable a particular service, the user gets the current value of the DBPROP_INIT_OLEDBSERVICES property, sets or clears the bit for the particular property to be enabled or disabled, and then resets the property.

You can set DBPROP_INIT_OLEDBSERVICES directly in OLE DB, or in the connection string passed to ADO or IDataInitialize::GetDatasource. The values that correspond to enable or disable individual services are in the list that follows:

DBPROP_INIT_OLEDBSERVICES

Default Services Enabled	Property Value
All services	DBPROPVAL_OS_ENABLEALL
All except Pooling and AutoEnlistment	(DBPROPVAL_OS_ENABLEALL&~DBPROPVAL_OS_RESOURCEPOOLING)
All except Client Cursor	(DBPROPVAL_OS_ENABLEALL&~DBPROPVAL_OS_CLIENTCURSOR)
All except pooling, enlistment, and cursor	(DBPROPVAL_OS_ENABLEALL&~DBPROPVAL_OS_RESOURCEPOOLING
No services	~DBPROPVAL_OS_ENABLEALL

Default Services Enabled	Value in Connection String
All services (the default)	"OLE DB Services = -1;"
All except Pooling and AutoEnlistment	"OLE DB Services = -2;"
All except Client Cursor	"OLE DB Services = -5;"
All except pooling, enlistment, and cursor	"OLE DB Services = -7;"
No services	"OLE DB Services = 0;"

If the registry entry does not exist for the provider, the Component Managers does not aggregate the provider's objects, and no services are invoked, even if explicitly requested by the user.

Here is a code example that illustrates how to invoke the service component from an OLE DB application. This example creates a CLSID_MSDAINITIALIZE provider and implements session pooling, which is one of the service components that OLE DB provides.

NOTE:

This program assumes you use a single processor computer.
You must have OLEDB_SERVICES set to 0xffffffff or 0xfffffffb under the SQLOLEDB for session pooling to work.
If you set DBPROP_INIT_PROMPT to DBPROMPT_PROMPT, you will not get session pooling. This is because the provider by default assumes a different connection when you are opening an INIT PROMPT dialog box

#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID

#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <oledberr.h>
#include <msdaguid.h>
#include <oleauto.h>
#include <msdasc.h>



void main(int argc, char* argv[])
{	
IUnknown *	pIUnknown = NULL;
BOOL *		pbValue = FALSE;
CLSID		clsid;
HRESULT		hr;
IDataInitialize*        pIDataInitialize = NULL;
IDBInitialize *		pIDBInitialize = NULL;
IDBProperties*		pIDBProperties = NULL;



hr = CoInitialize(NULL);

//Invoke the OLE DB Service Components with the MSDAINITIALIZE
//provider. Session pooling is enabled while the
//IDataInitialize interface pointer is held.
hr = CoCreateInstance(CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER,	IID_IDataInitialize, (void**)&pIDataInitialize);


if( FAILED(hr = CLSIDFromProgID(L"SQLOLEDB", &clsid) ) )
	return;


	const ULONG nProps = 5;
	DBPROP InitProperties[nProps];
	DBPROPSET rgInitPropSet;

	for (ULONG i = 0; i < nProps; i++)
	{
		VariantInit(&InitProperties[i].vValue);
		InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
		InitProperties[i].colid = DB_NULLID;
	}

	//Level of prompting performed to complete the connection process.
	InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
	InitProperties[0].vValue.vt = VT_I2;
	InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;

	//Datasource name.
	InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
	InitProperties[1].vValue.vt = VT_BSTR;
	InitProperties[1].vValue.bstrVal = SysAllocString(OLESTR("YourServerName"));

	//Userid.
	InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
	InitProperties[2].vValue.vt = VT_BSTR;
	InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR("YouUserId"));

	//Password.
	InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
	InitProperties[3].vValue.vt = VT_BSTR;
	InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR("YourPassword"));

	//Catalog : Your database name.
	InitProperties[4].dwPropertyID = DBPROP_INIT_CATALOG;
	InitProperties[4].vValue.vt = VT_BSTR;
	InitProperties[4].vValue.bstrVal = SysAllocString(OLESTR("YourDatabaseName"));


	//Assign the property structures to the property set.
	rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
	rgInitPropSet.cProperties = nProps;
	rgInitPropSet.rgProperties = InitProperties;


       //Here in a loop you connect to the database 10 times and
       //each time you release the connection at the end. Note, you                //only see one connection in the server for these 10 
       //connections. 
	for (int j = 0; j < 10; j++)
	{
		
		
		//Use the IDataInitialize interface to load the SQLOLEDB provider and create an IDBInitialize interface.
		hr = pIDataInitialize->CreateDBInstance(clsid, NULL, CLSCTX_INPROC_SERVER, NULL, IID_IDBInitialize, (IUnknown**)&pIDBInitialize);

		hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
		hr = pIDBProperties->SetProperties(1, &rgInitPropSet);
		pIDBProperties->Release();

		hr = pIDBInitialize->Initialize();
		if (FAILED(hr))
			return;


		//These variables are for Command creation.

		IDBCreateSession*       pIDBCreateSession;
		IDBCreateCommand*       pIDBCreateCommand;
		IRowset*                pIRowset;
		ICommandText*           pICommandText;
                //Here is a sample Select statement. Change it as                 //neccessary:
		LPCTSTR wSQLString = OLESTR("Select au_lname, au_fname from authors");
		LONG                    cRowsAffected;

		//Get the DB Session object.
		hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,(void**)&pIDBCreateSession);
		if (FAILED(hr))
			return;


		//Create the session, and get an interface for command creation.
		hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,(IUnknown**)&pIDBCreateCommand);
		pIDBCreateSession->Release();
		if (FAILED(hr))
			return;

		//Create the command object.
		hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,(IUnknown**)&pICommandText);
		pIDBCreateCommand->Release();
		if (FAILED(hr))
			return;

		hr = pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);
			
		//Execute the command.
		hr = pICommandText->Execute(NULL,IID_IRowset,NULL,&cRowsAffected,(IUnknown**)&pIRowset);
		pICommandText->Release();
		if (FAILED(hr))
			return;
		pIRowset->Release();



		//NOTE: Uncommenting the next line disables session pooling, and prevents the sessions 
		//from being pooled. Per the MSDASDK Help file:
		//"Initialization information is changed by calling IDBInitialize::Uninitialize." 

		//pIDBInitialize->Uninitialize();



		// Simply Release() the sessions and they return to the pool.
		// If pooling works properly, the connections to the server are
		// still alive after the IDBInitialize interface is released.
		pIDBInitialize->Release();

	}
	

	// The connections should be closed after the IDataInitialize interface is released.
	pIDataInitialize->Release();

CoUninitialize();

}