Custom CRecordset class does not call DoFieldExchange() when useMultiRowFetch is specified

Question

I've implemented a custom CRecordset class, and have code similar to the following:

ASSERT(prs->GetRowsetSize() == 25);
while (!prs->IsEOF())
{
    for (int i = 1; i <= prs->GetRowsFetched(); i++)
    {
        prs->SetRowsetCursorPosition((WORD)i);

        // Inspecting data here...

    }
    prs->MoveNext();
}
prs->Close();

Apparently, when using multi-row fetch, CRecordset does not call my DoFieldExchange override as it does when not using multi-row fetch, and that is by design. And so my data isn't automatically populated. So the question is how do I get the data?

The answer appears to be by calling GetFieldValue(). But I get an Invalid cursor position error when I do! (GetFieldValue() works fine when I'm not using multi-row fetch.)

Below is a streamlined version of my recordset class. In addition, @EylM was good enough to create a sample in the answers below that he says does work for him. However, when I copied his code exactly and just changed what was needed to connect to and query my database, I still get an Invalid cursor position when I call GetFieldValue().

I don't know what else could be different. I see he's using MySQL where I'm using SQL Server. But surely CRecordset works with SQL Server. I've also tried all the available SQL Server ODBC drivers, but the result is always the same.

class CRS : public CRecordset
{
public:
    // Data variables
    int m_nId;
    TCHAR m_szName[CUSTOMER_NAME_MAXLENGTH + 1];

    // Bulk data variables
    int* m_pnIds;
    long* m_pnIdLengths;
    LPTSTR m_pszNames;
    long* m_pnNameLengths;

    // Constructor
    CRS(CDatabase* pDatabase = NULL)
        : CRecordset(pDatabase)
    {
        m_nFields = 2;

        m_nId = 0;
        m_szName[0] = '\0';

        m_pnIds = NULL;
        m_pnIdLengths = NULL;
        m_pszNames = NULL;
        m_pnNameLengths = NULL;
    }

    CString GetDefaultSQL()
    {
        return CCustomerData::m_szTableName;
    }

    // This method is never called when
    // CRecordset::useMultiRowFetch is specified!
    void DoFieldExchange(CFieldExchange* pFX)
    {
        pFX->SetFieldType(CFieldExchange::outputColumn);
        RFX_Int(pFX, _T("Id"), m_nId);
        RFX_Text(pFX, _T("Name"), m_szName, CUSTOMER_NAME_MAXLENGTH);
    }

    // This method is called several times
    void DoBulkFieldExchange(CFieldExchange* pFX)
    {
        pFX->SetFieldType(CFieldExchange::outputColumn);
        RFX_Int_Bulk(pFX, _T("Id"), &m_pnIds, &m_pnIdLengths);
        RFX_Text_Bulk(pFX, _T("Name"), &m_pszNames, &m_pnNameLengths, (CUSTOMER_NAME_MAXLENGTH + 1) * 2);
    }
};

UPDATE:

Spending more time on this, I have been able to write code that reads the data directly from the rowset data (in my case, from m_pnIds, m_pnIdLengths, m_pszNames and m_pnNameLengths). Perhaps that's the approach I need to take.

But the question still stands. Why can't I use GetFieldValue() on a SQL Server database? And what is the point of SetRowsetCursorPosition()?

Answer 1

From documentation of CRecordset::DoFieldExchange:

When bulk row fetching is not implemented, the framework calls this member function to automatically exchange data between the field data members of your recordset object and the corresponding columns of the current record on the data source.

DoFieldExchange is called only if CRecordset::useMultiRowFetch is not specified in the Open function. Looking at MFC code CRecordset::BindFieldsToColumns, dbcore.cpp using VS 2019 (14.22.27905):

// Binding depends on fetch type
if (m_dwOptions & useMultiRowFetch)
    DoBulkFieldExchange(&fx);
else
    DoFieldExchange(&fx);

Sounds like that behaviour your are getting is by design.

Edit:

Here is working example for multi row fetch. The thing that did the trick is CRecordset::useExtendedFetch in the opening flags.

Database: I used MySQL with a simple table with 2 columns. Here is the creation script.

CREATE TABLE `categories` (
  `CatID` int(11) NOT NULL,
  `Category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`CatID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

MFC:

CMultiRowSet.h
class CMultiRowSet : public CRecordset
{   
public:
    CMultiRowSet(CDatabase* pDB);

    virtual void DoBulkFieldExchange(CFieldExchange* pFX);

    // Field/Param Data
    // field data members
    long* m_rgID;
    LPSTR m_rgName;

    // pointers for the lengths
    // of the field data
    long* m_rgIDLengths;
    long* m_rgNameLengths;
};

CMultiRowSet.cpp
void CMultiRowSet::DoBulkFieldExchange(CFieldExchange* pFX)
{
    // call the Bulk RFX functions
    // for field data members
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long_Bulk(pFX, _T("[CatID]"),
        &m_rgID, &m_rgIDLengths);
    RFX_Text_Bulk(pFX, _T("[Category]"),
        &m_rgName, &m_rgNameLengths, 30);
}

Usage:

CDatabase database; 
CString sCatID, sCategory;

TRY
{
    CString connStr = (_T("Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=XXXX;User=XXX; Password=XXXX; Option = 3;"));

    // Open the database
    database.OpenEx(connStr,CDatabase::noOdbcDialog);

    // Allocate the recordset
    CMultiRowSet recset(&database);     

    // Execute the query
    // make sure you use CRecordset::useExtendedFetch.
    recset.Open(CRecordset::forwardOnly, _T("SELECT CatID, Category FROM Categories"), CRecordset::readOnly|CRecordset::useMultiRowFetch|CRecordset::useExtendedFetch);

    // Loop through each record
    while (!recset.IsEOF())
    {       
        // The default `GetRowsetSize` is 25. I have 4 rows in my database. 
        // GetRowsFetched returns 4 in my case.
        for (int rowCount = 1; rowCount <= (int)recset.GetRowsFetched(); rowCount++)
        {           
            recset.SetRowsetCursorPosition(rowCount);

            // Copy each column into a variable
            recset.GetFieldValue(_T("CatID"), sCatID);
            recset.GetFieldValue(_T("Category"), sCategory);
        }

        // goto next record
        recset.MoveNext();
    }

    recset.Close();

    // Close the database
    database.Close();
}
CATCH(CDBException, e)
{
    // If a database exception occured, show error msg
    AfxMessageBox(_T("Database error: ") + e->m_strError);
}
END_CATCH;