How To
How to retrieve rows using bookmarks (OLE DB)
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 an an offset from a bookmark.
To retrieve rows using bookmarks
- Establish a connection to the data source.
- Set the rowset property DBPROP_IRowsetLocate property to VARIANT_TRUE.
- Execute the command.
- Set the dwFlags field of the binding structure to DBCOLUMNSINFO_ISBOOKMARK flag for the column that will be used as a bookmark.
- Use IRowsetLocate::GetRowsAt to fetch rows, starting with the row specified by an offset from the bookmark.
The following example shows how to fetch rows using a bookmark. In this example, the5th 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";
} //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 5th 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.