OLE DB: How to find the record when working via CTable<...> ?

Summary: How to move the cursor to the record identified by unique key when using the simple CTable<> ?

It is related to my earlier question http:Q_24934451.html

Hi experts,

I am able to get the rowset via SQL SELECT command executed via CCommand<> consumer template. However, I need to modify the record, and I would like to use the capability of accessors -- moving cursor to the record, getting the data to the accessor buffer, modifying some of the elements of the record, and transfering it to the table via .SetData() of the rowset.

I have implemented everything except the "moving cursor to the record identified by the key value". So far, I know only .MoveFirst() and .MoveNext(), testing the content of the record. However, the table has its own index. Is there any way to use the index to skip faster to the searched record?

The code below uses VFPOLEDB provider and creates the data.dbf table with data.cdx index just for demonstration purpose. Then it inserts one record, deletes the same one (marked as delete in the DBF file), inserts the record with the same key value (placed on another place in the table)... Now, think about it as about more general: many records, searching for the record that is not marked as deleted. Focus on the spot in the snippet that says "??? What code should I put here?" (near the end of the snippet).

How can I do that?

Thanks for your help and experience,
    Petr

#include <atldbcli.h>
#include <cassert>
#include <iostream>
#include <sstream>
#include <string> 
const std::string globalDataPath("d:/Data");        // the directory must exist
const std::string globalTableName("data");
const std::string globalProviderStr("VFPOLEDB.1");  // MS Visual FoxPro OLE DB 
using namespace std; 
/*! \brief Buffer and accessors for OLE DB Consumer Templates.
*/
class CBasicDataAccessor
{
public:
    long     code;          //!< key field of the record
    char     notes[51];     //!< some text 
    BEGIN_ACCESSOR_MAP(CBasicDataAccessor, 1)
        BEGIN_ACCESSOR(0, false)      // simple, non-automatic
            COLUMN_ENTRY(1, code)
            COLUMN_ENTRY(2, notes)
        END_ACCESSOR()
    END_ACCESSOR_MAP() 

    inline void ClearRecord()
    {
        code = 0;
        ZeroMemory(notes, sizeof(notes));
    }
}; 

/*! \brief Main body.
*/
int main()
{
    // Init the COM.
    HRESULT hr = ::CoInitialize(NULL);
    assert(SUCCEEDED(hr)); 
    // Open the data source and the session.
    CDataSource ds;
    CSession session; 
    CDBPropSet propSet(DBPROPSET_DBINIT);
    propSet.AddProperty(DBPROP_INIT_DATASOURCE, globalDataPath.c_str()); 
    hr = ds.Open(globalProviderStr.c_str(), &propSet);
    if (SUCCEEDED(hr))
    {
        hr = session.Open(ds);
        assert(SUCCEEDED(hr));
    }
    
    // Create the data table.
    CCommand<> cmd;  // No accessor neccessary.
    string SQL_COMMAND(
        "CREATE TABLE data CODEPAGE=1250"
        "(Code Integer NOT NULL, UNIQUE Code FOR NOT DELETED() TAG xCode,"
        " Notes Char(50)\n)"
        ); 
    hr = cmd.Open(session, SQL_COMMAND.c_str());
    assert(SUCCEEDED(hr));
    cmd.Close();
    cmd.ReleaseCommand(); 

    // The table object with accessors.
    CTable<CAccessor<CBasicDataAccessor> > table; 
    // Properties for inserting, deleting, ane changing the existing record.
    CDBPropSet ps(DBPROPSET_ROWSET);
    ps.AddProperty(DBPROP_IRowsetChange, true);
    ps.AddProperty(DBPROP_UPDATABILITY, 
        DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE); 
    // Open the table and search for the record.
    hr = table.Open(session, globalTableName.c_str(), &ps);
    assert(SUCCEEDED(hr)); 
    // Insert some records
    table.ClearRecord(); 
    for (int i = 0; i < 10; ++i)
    {
        table.code = i;
        string s("some text");
        strncpy_s(table.notes, s.c_str(), sizeof(table.notes)); 
        hr = table.Insert();
        assert(SUCCEEDED(hr));
    } 
    // Search for the record with code equal to 5.
    // Brute force here... How it should be done correctly?
    // How can I use the index via CTable<> ?
    long code = 5;
    hr = table.MoveFirst();
    table.GetData(0);
    while (SUCCEEDED(hr) && table.code != code)
    {
        hr = table.MoveNext();
        table.GetData(0);
    } 
    // Delete the record.
    hr = table.Delete();
    assert(SUCCEEDED(hr)); 
    // Insert the record with the same code. It is physically placed
    // at a different place.
    table.ClearRecord();
    table.code = code;
    string s = "some other text";
    strncpy_s(table.notes, s.c_str(), sizeof(table.notes));
    assert(SUCCEEDED(hr)); 
    hr = table.Insert();
    assert(SUCCEEDED(hr)); 
    // How to find the active record with the code to be deleted
    // again? It must be related to the usage of index probably. 
    // Is it possible with CTable object at all? 
    ??? What code should I put here? 
    // Delete the record.
    hr = table.Delete();
    assert(SUCCEEDED(hr)); 
    // Close the table object.
    table.Close(); 
    // Close the session and the data source objects.
    session.Close();
    ds.Close(); 
    // Finish the work with COM.
    ::CoUninitialize(); 
    return 0;
}

Select all Open in new window