Using the Seek Method with OLE DB

Article
06/28/2007

The Seek method is the most widely used method by clients on indexes in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition). Seek lets you find rows on a cursor very quickly.

Using Seek

The Seek method requires that an index be defined on the columns in the search key to work correctly. Most seek operations are for a particular value, but it is also possible to seek using other comparison operators, such as "greater than" or "less than".

IRowsetIndex::Seek passes values to the provider by using the accessor mechanism in OLE DB that is used to obtain and set data. Accessors used against the Seek method have additional restrictions over accessors for IRowset::GetData and IRowset::SetData. The accessor must bind columns in the order they appear in the index key.

The IRowsetPosition interface is only supported on scrollable cursors.

Examples

The following example shows how to use IRowsetIndex::Seek on an index using the OLE DB provider for SQL Server Compact Edition. The example contains only the sections of the function that relate to the Seek method.

TableID.eKind            = DBKIND_NAME;
TableID.uName.pwszName    = (WCHAR*)TABLE_EMPLOYEE;

IndexID.eKind            = DBKIND_NAME;
IndexID.uName.pwszName    = L"PK_Employees";

// Request ability to use IRowsetChange interface.
rowsetpropset[0].cProperties     = 1;
rowsetpropset[0].guidPropertySet = DBPROPSET_ROWSET;
rowsetpropset[0].rgProperties    = rowsetprop;

rowsetprop[0].dwPropertyID       = DBPROP_IRowsetIndex;
rowsetprop[0].dwOptions          = DBPROPOPTIONS_REQUIRED;
rowsetprop[0].colid              = DB_NULLID;
rowsetprop[0].vValue.vt          = VT_BOOL;
rowsetprop[0].vValue.boolVal     = VARIANT_TRUE;

// Open the table using the index.
hr = pIOpenRowset->OpenRowset(NULL, &TableID, &IndexID,
    IID_IRowsetIndex, sizeof(rowsetpropset)/sizeof(rowsetpropset[0]),
    rowsetpropset, (IUnknown**) &pIRowsetIndex);
if(FAILED(hr))
{
    goto Exit;
}

// Get the IRowset interface.
hr = pIRowsetIndex->QueryInterface(IID_IRowset, (void**) &pIRowset);
if(FAILED(hr))
{
    goto Exit;
}

///////////////////////////////////////////////////////////////////////
// Steps to get column data using IcolumnsInfo have been removed
///////////////////////////////////////////////////////////////////////

// Create a DBBINDING array.
dwBindingSize = sizeof(pwszEmployees)/sizeof(pwszEmployees[0]);
prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
if (NULL == prgBinding)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Set initial offset for binding position.
dwOffset = 0;

// Prepare structures to create an accessor for each index.
for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
{
    if (!GetColumnOrdinal(pDBColumnInfo, ulNumCols, pwszEmployees[dwIndex], &dwOrdinal))
    {
        hr = E_FAIL;
        goto Exit;
    }

    prgBinding[dwIndex].iOrdinal  = dwOrdinal;
    prgBinding[dwIndex].dwPart    = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
    prgBinding[dwIndex].obLength  = dwOffset;                                     
    prgBinding[dwIndex].obStatus  = prgBinding[dwIndex].obLength + sizeof(ULONG);  
    prgBinding[dwIndex].obValue   = prgBinding[dwIndex].obStatus + sizeof(DBSTATUS);
    prgBinding[dwIndex].pTypeInfo  = NULL;
    prgBinding[dwIndex].pBindExt   = NULL;
    prgBinding[dwIndex].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
    prgBinding[dwIndex].dwFlags    = 0;
    prgBinding[dwIndex].bPrecision = pDBColumnInfo[dwOrdinal].bPrecision;
    prgBinding[dwIndex].bScale     = pDBColumnInfo[dwOrdinal].bScale;

///////////////////////////////////////////////////////////////////////
// Case-specific binding information has been removed.
///////////////////////////////////////////////////////////////////////

    prgBinding[dwIndex].pObject    NULL;
    prgBinding[dwIndex].wType     = pDBColumnInfo[dwOrdinal].wType;
    if(DBTYPE_WSTR == pDBColumnInfo[dwOrdinal].wType)
    {
        prgBinding[dwIndex].cbMaxLen  = pDBColumnInfo[dwOrdinal].ulColumnSize 
            * sizeof(WCHAR); 
    }
    else
    {
        prgBinding[dwIndex].cbMaxLen  = pDBColumnInfo[dwOrdinal].ulColumnSize; 
    }

    // Calculate and align the offset. 
}

// Get IAccessor interface.
hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
if(FAILED(hr))
{
    goto Exit;
}

// Create the accessor.
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, dwBindingSize, 
    prgBinding, 0, &hAccessor, NULL);
if(FAILED(hr))
{
    goto Exit;
}

// Allocate data buffer for seek and retrieve operation.
pData = (BYTE*)CoTaskMemAlloc(dwOffset);
if (NULL == pData)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Set data buffer to zero.
//
memset(pData, 0, dwOffset);

// Set data buffer for seek operation by specifying the 
// dwEmployeeID variable that is passed to the function.
*(ULONG*)(pData+prgBinding[0].obLength)    = 4;
*(DBSTATUS*)(pData+prgBinding[0].obStatus) = DBSTATUS_S_OK;
*(int*)(pData+prgBinding[0].obValue)       = dwEmployeeID;

// Seek for the first row where the value of the selected column 
// is dwEmployeeID. 
hr = pIRowsetIndex->Seek(hAccessor, 1, pData, DBSEEK_FIRSTEQ);
if(FAILED(hr))
{
    goto Exit;    
}

// Retrieve a row handle for the row resulting from the seek.
hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, 
    &prghRows);
if(FAILED(hr))
{
    goto Exit;    
}

///////////////////////////////////////////////////////////////////////
// Perform programming logic here on the 
// returned rowset, and then release the rowset.
///////////////////////////////////////////////////////////////////////

Exit:

///////////////////////////////////////////////////////////////////////
// This is where the resources are released.
///////////////////////////////////////////////////////////////////////

return hr;

Using the Seek Method with OLE DB

Using Seek

Examples

See Also

Concepts

Help and Information

Additional resources