MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server
To obtain a forward-only, read-only cursor, set the rowset properties DBPROP_SERVERCURSOR, DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, and DBPROP_OWNUPDATEDELETE to VARIANT_TRUE.
To obtain a FAST_FORWARD cursor
The following example shows how to set the rowset properties to obtain a FAST_FORWARD cursor. After the properties are set, a SELECT statement is executed to find the first and last names of authors in the pubs database.
#define INITGUID
#define DBINITCONSTANTS
#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <sqloledb.h>
#include <oledberr.h>
IDBInitialize* pIDBInitialize = NULL;
ICommandText* pICommandText = NULL;
// Connect to the server and create a command object.
void InitializeAndConnect();
// Set the properties to get a FAST_FORWARD cursor.
void SetRowsetProperties();
// This function executes a command and displays the results.
void ExecuteAndDisplay();
// Clean up the memory.
void Cleanup();
void main()
{
// Initialize.
InitializeAndConnect();
// Set the row properties to FAST_FORWARD cursor.
SetRowsetProperties();
// Execute a command and display the results.
ExecuteAndDisplay();
// Cleanup.
Cleanup();
}
void InitializeAndConnect()
{
HRESULT hr = S_OK;
IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
DBPROPSET dbPropSet;
DBPROP dbProp[4];
// Initialize OLE.
if( FAILED( hr = OleInitialize( NULL ) ) )
{
// Handle errors here.
}
// Create an instance of Microsoft OLE DB Provider for SQL Server.
if(FAILED(hr = CoCreateInstance(CLSID_SQLOLEDB, NULL,
CLSCTX_INPROC_SERVER, IID_IDBProperties, (void **)
&pIDBProperties)))
{
// Handle errors here.
}
// Set up the connection properties.
dbProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[0].vValue)) = SysAllocString( L"server " );
dbProp[1].dwPropertyID = DBPROP_AUTH_USERID;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[1].vValue)) = SysAllocString( L"login" );
dbProp[2].dwPropertyID = DBPROP_AUTH_PASSWORD;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[2].vValue)) = SysAllocString( L"" );
dbProp[3].dwPropertyID = DBPROP_INIT_CATALOG;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[3].vValue)) = SysAllocString( L"pubs" );
dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 4;
dbPropSet.guidPropertySet = DBPROPSET_DBINIT;
if( FAILED( hr = pIDBProperties->SetProperties( 1, &dbPropSet )))
{
// Handle errors here.
}
SysFreeString( V_BSTR(&(dbProp[0].vValue)) );
SysFreeString( V_BSTR(&(dbProp[1].vValue)) );
SysFreeString( V_BSTR(&(dbProp[2].vValue)) );
SysFreeString( V_BSTR(&(dbProp[3].vValue)) );
// Get an IDBInitialize interface.
if( FAILED( hr = pIDBProperties->QueryInterface( IID_IDBInitialize,
(void **) &pIDBInitialize )))
{
// Handle errors here.
}
// Call Initialize.
if( FAILED( hr = pIDBInitialize->Initialize()))
{
// Handle errors here.
}
// Get the IDBCreateSession interface.
if( FAILED( hr = pIDBInitialize->QueryInterface( IID_IDBCreateSession,
(void **) &pIDBCreateSession )))
{
// Handle errors here.
}
// Create a session.
if( FAILED( hr = pIDBCreateSession->CreateSession( NULL,
IID_IDBCreateCommand, (IUnknown **) &pIDBCreateCommand)))
{
// Handle errors here.
}
// Create a command.
if( FAILED( hr = pIDBCreateCommand->CreateCommand( NULL,
IID_ICommandText, (IUnknown **) &pICommandText)))
{
// Handle errors here.
}
// Release all the objects not needed anymore.
pIDBProperties->Release();
pIDBCreateSession->Release();
pIDBCreateCommand->Release();
}
void SetRowsetProperties()
{
HRESULT hr = S_OK;
ICommandProperties* pICommandProperties = NULL;
DBPROPSET dbPropSet;
DBPROP dbProp[5];
// Get an ICommandProperties object.
if( FAILED( hr = pICommandText->QueryInterface(
IID_ICommandProperties, (void **) &pICommandProperties )))
{
// Handle errors here.
}
// Set up the properties to get a FAST_FORWARD cursor.
dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[0].vValue)) = VARIANT_TRUE;
dbProp[1].dwPropertyID = DBPROP_OTHERINSERT;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[1].vValue)) = VARIANT_TRUE;
dbProp[2].dwPropertyID = DBPROP_OTHERUPDATEDELETE;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[2].vValue)) = VARIANT_TRUE;
dbProp[3].dwPropertyID = DBPROP_OWNINSERT;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[3].vValue)) = VARIANT_TRUE;
dbProp[4].dwPropertyID = DBPROP_OWNUPDATEDELETE;
dbProp[4].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[4].colid = DB_NULLID;
V_VT(&(dbProp[4].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[4].vValue)) = VARIANT_TRUE;
dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 5;
dbPropSet.guidPropertySet = DBPROPSET_ROWSET;
if( FAILED( hr = pICommandProperties->SetProperties( 1,
&dbPropSet)))
{
// Handle errors here.
}
// Release the ICommandProperties object.
pICommandProperties->Release();
}
void ExecuteAndDisplay()
{
HRESULT hr = S_OK;
IRowset* pIRowset = NULL;
IAccessor* pIAccessor = NULL;
BYTE* pData = NULL;
ULONG cRowsObtained = 0;
ULONG cCount = 0;
HROW* pRows = new HROW[10];
HACCESSOR hAccessor;
DBBINDING Bind[2];
// Set the command text.
if( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL,
L"select au_lname, au_fname from authors")))
{
// Handle errors here.
}
// Execute the command.
if( FAILED( hr = pICommandText->Execute( NULL, IID_IRowset,
NULL, NULL, (IUnknown **) &pIRowset )))
{
// Handle errors here.
}
// Set up the binding structure for au_lname (varchar(40)).
Bind[0].dwPart = DBPART_VALUE;
Bind[0].eParamIO = DBPARAMIO_NOTPARAM;
Bind[0].iOrdinal = 1;
Bind[0].pTypeInfo = NULL;
Bind[0].pObject = NULL;
Bind[0].pBindExt = NULL;
Bind[0].dwFlags = 0;
Bind[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bind[0].obLength = 0;
Bind[0].obStatus = 0;
Bind[0].obValue = 0;
Bind[0].cbMaxLen = 40;
Bind[0].wType = DBTYPE_STR;
Bind[0].bPrecision = 0;
Bind[0].bScale = 0;
// Set up the binding structure for au_fname (varchar(20)).
Bind[1].dwPart = DBPART_VALUE;
Bind[1].eParamIO = DBPARAMIO_NOTPARAM;
Bind[1].iOrdinal = 2;
Bind[1].pTypeInfo = NULL;
Bind[1].pObject = NULL;
Bind[1].pBindExt = NULL;
Bind[1].dwFlags = 0;
Bind[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bind[1].obLength = 0;
Bind[1].obStatus = 0;
Bind[1].obValue = 50;
Bind[1].cbMaxLen = 20;
Bind[1].wType = DBTYPE_STR;
Bind[1].bPrecision = 0;
Bind[1].bScale = 0;
// Get an IAccessor interface.
if( FAILED( hr = pIRowset->QueryInterface( IID_IAccessor,
(void **) &pIAccessor)))
{
// Handle errors here.
}
// Create an accessor.
if( FAILED( hr = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 2,
Bind, 0, &hAccessor, NULL)))
{
// Handle errors here.
}
// Allocate memory for the data.
pData = new BYTE[100];
// Loop through all of the rows.
while( TRUE )
{
if( FAILED( hr = pIRowset->GetNextRows( NULL, 0, 10,
&cRowsObtained, &pRows ) ) )
{
// Handle errors here.
}
// Make sure some rows were obtained.
if( cRowsObtained == 0 )
{
break;
}
// Get the data for each of the rows.
for( cCount = 0; cCount < cRowsObtained; cCount++ )
{
// Get the row data needed.
if( FAILED( hr = pIRowset->GetData( pRows[cCount],
hAccessor, pData )))
{
// Handle errors here.
}
// Display row data.
printf( "%s, %s\n", pData, ( pData + 50 ));
}
// Release the rows.
if( FAILED( hr = pIRowset->ReleaseRows( cRowsObtained, pRows,
NULL, NULL, NULL )))
{
// Handle errors here.
}
}
// Free the memory allocated for the data.
delete [] pData;
// Release the HACCESSOR.
if( FAILED( hr = pIAccessor->ReleaseAccessor( hAccessor, NULL )))
{
// Handle errors here.
}
// Release the IAccessor object.
pIAccessor->Release();
// Release the rowset.
pIRowset->Release();
}
void Cleanup()
{
HRESULT hr = S_OK;
// Release the ICommandText object.
pICommandText->Release();
// Uninitialize the IDBInitialize object.
if( FAILED( hr = pIDBInitialize->Uninitialize() ) )
{
// Handle errors here.
}
// Release the IDBInitialize object.
pIDBInitialize->Release();
// Uninitialize OLE.
OleUninitialize();
}