MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server
The consumer sets the dwFlag field value of the binding structure to DBCOLUMNSINFO_ISBOOKMARK to indicate that the column is used as bookmark. The consumer also sets the rowset property DBPROP_BOOKMARKS to VARIANT_TRUE. This allows column 0 to be present in the rowset. IRowsetLocate::GetRowsAt is then used to fetch rows starting with the row specified by an offset from a bookmark.
To retrieve rows using bookmarks
The following example shows how to fetch rows using a bookmark. In this example, the fifth row is retrieved from the result set produced from the execution of a SELECT statement.
/*
How to use bookmarks
*/
void InitializeAndEstablishConnection();
void ProcessResultSet();
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID
#include <stdio.h>
#include <tchar.h>
#include <stddef.h>
#include <windows.h>
#include <iostream.h>
#include <oledb.h>
#include <sqloledb.h>
IDBInitialize* pIDBInitialize = NULL;
IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandProperties* pICommandProperties = NULL;
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
IColumnsInfo* pIColumnsInfo = NULL;
DBCOLUMNINFO* pDBColumnInfo = NULL;
IAccessor* pIAccessor = NULL;
IRowsetLocate* pIRowsetLocate = NULL;
DBPROP InitProperties[4];
DBPROPSET rgInitPropSet[1];
DBPROPSET rgPropSets[1];
DBPROP rgProperties[1];
ULONG i, j;
HRESULT hr;
LONG cNumRows = 0;
ULONG lNumCols;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
ULONG ConsumerBufferColOffset = 0;
HACCESSOR hAccessor;
ULONG lNumRowsRetrieved;
HROW hRows[5];
HROW* pRows = &hRows[0];
char* pBuffer;
void main() {
// The command to execute.
WCHAR* wCmdString = OLESTR(" SELECT title_id, title FROM titles ");
// Initialize and establish a connection to the data source.
InitializeAndEstablishConnection();
// Create a session object.
if(FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession,
(void**) &pIDBCreateSession)))
{
cout << "Failed to obtain IDBCreateSession interface.\n";
}
if(FAILED(pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand)))
{
cout << "pIDBCreateSession->CreateSession failed.\n";
}
// Access the ICommandText interface.
if(FAILED(pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText)))
{
cout << "Failed to access ICommand interface.\n";
}
// Set DBPROP_IRowsetLocate.
if(FAILED(pICommandText->QueryInterface( IID_ICommandProperties,
(void **) &pICommandProperties )))
{
cout << "Failed to obtain ICommandProperties interface.\n";
}
/*
Set DBPROP_IRowsetLocate to VARIANT_TRUE to
get the IRowsetLocate interface.
*/
VariantInit(&rgProperties[0].vValue);
rgPropSets[0].guidPropertySet = DBPROPSET_ROWSET;
rgPropSets[0].cProperties = 1;
rgPropSets[0].rgProperties = rgProperties;
// Set properties in the property group (DBPROPSET_ROWSET).
rgPropSets[0].rgProperties[0].dwPropertyID = DBPROP_IRowsetLocate;
rgPropSets[0].rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgPropSets[0].rgProperties[0].colid = DB_NULLID;
rgPropSets[0].rgProperties[0].vValue.vt = VT_BOOL;
rgPropSets[0].rgProperties[0].vValue.boolVal = VARIANT_TRUE;
// Set the rowset properties.
hr = pICommandText->QueryInterface(IID_ICommandProperties,
(void **)&pICommandProperties);
if (FAILED(hr))
{
printf("Failed to get ICommandProperties to set rowset
properties.\n");
// Release any references and return.
} // End if.
hr = pICommandProperties->SetProperties(1, rgPropSets);
if (FAILED(hr))
{
printf("Execute failed to set rowset properties.\n");
// Release any references and return.
} // End if.
pICommandProperties->Release();
// Specify the command text.
if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString)))
{
cout << "Failed to set command text.\n";
}
// Execute the command.
if(FAILED(hr = pICommandText->Execute(NULL, IID_IRowset, NULL,
&cNumRows, (IUnknown **) &pIRowset)))
{
cout << "Failed to execute command.\n";
}
ProcessResultSet();
pIRowset->Release();
// Free up memory.
pICommandText->Release();
pIDBCreateCommand->Release();
pIDBCreateSession->Release();
if(FAILED(pIDBInitialize->Uninitialize()))
{
/*
Uninitialize is not required, but it fails if an interface
has not been released. This can be used for debugging.
*/
cout << "Problem uninitializing.\n";
} // End if.
pIDBInitialize->Release();
// Release COM library.
CoUninitialize();
};
//--------------------------------------------------------------------
void InitializeAndEstablishConnection()
{
/ /Initialize the COM library.
CoInitialize(NULL);
// Obtain access to the SQLOLEDB provider.
CoCreateInstance(CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void **) &pIDBInitialize);
// Initialize the property values that are the same for each property.
for(i = 0; i < 5; i++)
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// Server name
InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[0].vValue.vt = VT_BSTR;
InitProperties[0].vValue.bstrVal =
SysAllocString((LPOLESTR)L"server");
// Database
InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal = SysAllocString((LPOLESTR)L"pubs");
// Login
InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString((LPOLESTR)L"login");
// Password
InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal =
SysAllocString((LPOLESTR)"password");
// Construct the PropertySet array.
rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet[0].cProperties = 4;
rgInitPropSet[0].rgProperties = InitProperties;
// Set initialization properties.
pIDBInitialize->QueryInterface(IID_IDBProperties,
(void **)&pIDBProperties);
hr = pIDBProperties->SetProperties(1, rgInitPropSet);
if(FAILED(hr))
{
cout << "Failed to set initialization properties.\n";
}
pIDBProperties->Release();
// Call the initialization method to establish the connection.
if(FAILED(pIDBInitialize->Initialize()))
{
cout << "Problem initializing and connecting to the data
source.\n";
}
} // End of InitializeAndEstablishConnection.
//-------------------------------------------------------------------
void ProcessResultSet()
{
// Retrieve fifth row from the rowset (for example).
int iBookmark = 5;
pIRowset->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
pIColumnsInfo->GetColumnInfo(&lNumCols, &pDBColumnInfo,
&pStringsBuffer);
// Create a DBBINDING array.
pBindings = new DBBINDING[lNumCols];
// Using the ColumnInfo strucuture, fill out the pBindings array.
for(j=0; j<lNumCols; j++)
{
pBindings[j].iOrdinal = j;
pBindings[j].obValue = ConsumerBufferColOffset;
pBindings[j].pTypeInfo = NULL;
pBindings[j].pObject = NULL;
pBindings[j].pBindExt = NULL;
pBindings[j].dwPart = DBPART_VALUE;
pBindings[j].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
pBindings[j].eParamIO = DBPARAMIO_NOTPARAM;
pBindings[j].cbMaxLen = pDBColumnInfo[j].ulColumnSize + 1;
// + 1 for null terminator.
pBindings[j].dwFlags = 0;
pBindings[j].wType = pDBColumnInfo[j].wType;
pBindings[j].bPrecision = pDBColumnInfo[j].bPrecision;
pBindings[j].bScale = pDBColumnInfo[j].bScale;
// Recalculate the next buffer offset.
ConsumerBufferColOffset = ConsumerBufferColOffset +
pDBColumnInfo[j].ulColumnSize;
};
/*
Indicate that the first field is used as a bookmark by setting
dwFlags to DBCOLUMNFLAGS_ISBOOKMARK.
*/
pBindings[0].dwFlags = DBCOLUMNFLAGS_ISBOOKMARK;
// Get IAccessor interface.
hr = pIRowset->QueryInterface(IID_IAccessor, (void **)&pIAccessor);
if (FAILED(hr))
{
printf("Failed to get IAccessor interface.\n");
}
// Create accessor.
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, lNumCols,
pBindings,0,&hAccessor,NULL);
if(FAILED(hr))
{
printf("Failed to create an accessor.\n");
}
HRESULT hr = pIRowset->QueryInterface(IID_IRowsetLocate,
(void **) &pIRowsetLocate);
if (FAILED(hr))
{
printf("Failed to get IRowsetLocate interface.\n");
}
hr = pIRowsetLocate->GetRowsAt(0, NULL, sizeof(int),
(BYTE *) &iBookmark, 0, 1, &lNumRowsRetrieved, &pRows);
if (FAILED(hr))
{
printf("Calling the GetRowsAt method failed.\n");
}
// Create buffer and retrieve data.
pBuffer = new char[ConsumerBufferColOffset];
memset(pBuffer, 0, ConsumerBufferColOffset);
hr = pIRowset->GetData(hRows[0], hAccessor, pBuffer);
if (FAILED(hr))
{
printf("Failed GetDataCall.\n");
}
printf("%d\t%s%s\n", &pBuffer[pBindings[0].obValue],
&pBuffer[pBindings[1].obValue],
&pBuffer[pBindings[2].obValue]);
pIRowset->ReleaseRows(lNumRowsRetrieved, hRows, NULL, NULL, NULL);
// Free all allocated memory.
delete [] pBuffer;
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pBindings;
} // ProcessResultSet.