InformIT: Visual C++ 6 Unleashed > Bulk Row Operations

Table of Contents

Bulk Row Operations

Up until now, you have been looking at how to use the CRecordset class to retrieve the data for a single row at a time, which may not always be the most efficient way of doing things. You can often make your applications more efficient and easier to program by fetching a group of rows (rowset) all at once using bulk record field exchange (Bulk RFX).

When using Bulk RFX, the member variables of your recordset class should be pointers to the type of data expected. The following shows the declaration of a recordset class for use with Bulk RFX:

class CMyBulkSet : public CRecordset
{
public:
    CMyBulkSet(CDatabase* pDatabase = NULL);
    DECLARE_DYNAMIC(CMyBulkSet)
// Field/Param Data
    Cstring m_EmpName;
    Cstring m_Dept;
    Cstring m_Salary;
// Length buffer pointers
    long* m_EmpNameLen;
    long* m_DeptLen;
    long* m_SalaryLen;
// Overrides
    public:
    virtual CString GetDefaultConnect();    // Default connection string
    virtual CString GetDefaultSQL();    // Default SQL for Recordset
    virtual void DoBulkFieldExchange(CFieldExchange* pFX);  // RFX support
// Implementation
#ifdef _DEBUG
    virtual void AssertValid() const;
    virtual void Dump(CDumpContext& dc) const;
#endif
};

The constructor for this class should look something like the following:

CMyBulkSet::CMyBulkSet(CDatabase* pDatabase)
    : CRecordset(pDatabase)
{
    m_EmpName = NULL;
    m_Dept = NULL;
    m_Salary = NULL;

    m_EmpNameLen = NULL;
    m_DeptLen = NULL;
    m_SalaryLen = NULL;
    m_nFields = 3;
    m_nDefaultType = dynaset;
}

Opening a `CRecordset` for Bulk RFX

To enable bulk row fetching for your CRecordset, you specify the CRecordset:: useMultiRowFetch flag in the options parameter of CRecordset::Open(). You probably will also want to specify the number of rows to be retrieved in each rowset by calling CRecordset::SetRowsetSize() before calling Open()—otherwise, a default size of 25 rows is used. You can retrieve the current rowset size by calling CRecordset::GetRowsetSize().

The following example shows how to open a recordset for Bulk RFX, for which MFC will allocate data buffers:

// Allocate new recordset
CMyBulkSet* pBulkSet = new CMyBulkSet(m_pDatabase);
// Open Recordset, catching exceptions
try {
    // Set Number of rows in rowset
    pBulkSet->SetRowsetSize(5);

    // Open recordset with dynamic cursor
    bRc = pBulkSet->Open(CRecordset::dynamic,
            "SELECT EmpName, Dept, Salary FROM Employee",
            CRecordset::useMultiRowFetch);
    if(bRc)
        TRACE("Recordset Opened OK\n");
    else
        TRACE("Recordset Not Opened\n");
}
catch(CMemoryException *pEx) {
    pEx->ReportError();
}
catch(CDBException *pEx) {
    pEx->ReportError();
    TRACE("RetCode: %d strError: [%s] strState: [%s]\n",
        pEx->m_nRetCode, pEx->m_strError,
        pEx->m_strStateNativeOrigin);
}

Implementing Bulk Record Field Exchange

When fetching rows in bulk, you will use bulk record field exchange, which is similar to regular RFX, with the exception that it retrieves up to the number of rows in the rowset and places the data into array members of your recordset. For each column, you also want to allocate an array of longs that will receive the length of each field returned.

You can manually allocate these arrays, just as you would for RFX column variables as you saw earlier, or you can have MFC allocate the memory for you. If you are allocating your own buffers, you let MFC know by specifying the CRecordset:: userAllocMultiRowBuffers option when you call Open() for the recordset. By default, MFC allocates the memory for you. If you choose to let MFC allocate the arrays, you should declare your member variables as pointers to the appropriate type and initialize them to NULL.

`DoBulkFieldExchange()`

If you have enabled Bulk RFX, MFC calls the DoBulkFieldExchange() member of your recordset class instead of DoFieldExchange(). You will want to implement this function in your recordset class to move data from the database into the member arrays of your class. The implementation of DoBulkFieldExchange() is very similar to that of DoFieldExchange(), except that you should use the Bulk RFX macros. Instead of using RFX_Int(), for example, you should use RFX_Int_Bulk(). In addition, some of the bulk functions, such as RFX_Text_Bulk(), take a fifth parameter for the maximum allowable buffer size.

The only difference in the bulk macros is that they take a fourth parameter—the address of an array of longs that will receive the length of each field returned. This array will hold SQL_NULL_DATA for any field that has a NULL value. You should make sure that the pointer to the column data points to an array of variables, rather than the single variable used in plain RFX.

The following example shows an implementation of DoBulkFieldExchange():

void CMyBulkSet::DoBulkFieldExchange(CFieldExchange* pFX)
{
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long_Bulk(pFX, _T("[EmpName]"), &m_Num, &m_EmpNameLen);
    RFX_Text_Bulk(pFX, _T("[Dept]"), &m_Title, &m_DeptLen, 50);
    RFX_Long_Bulk(pFX, _T("[Salary]"), &m_Pages, &m_SalaryLen);
}

Fetching Bulk Records

When you call Open() with the CRecordset::useMultiRowFetch option set, the first rowset is fetched from the datasource and copied into the member variables. To retrieve the next complete rowset, you can call MoveNext(). You also may call Move() to fetch a new rowset; however, you should be aware that the parameter to Move specifies the number of rows to move, not the number of rowset blocks. If your rowset size is 10, for example, Open() fetches the first 10 rows, a call to MoveNext() fetches the next 10 (rows 11 to 20), and a call to Move(5) fetches rows 6 to 15.

You can retrieve the number of rows actually fetched in any call to Open(), Move(), or MoveNext() by calling CRecordset::GetRowsFetched(). You can get the status for an individual row by calling CRecordset::GetRowStatus(), which tells you things such as whether the row was successfully retrieved or whether an error occurred. This function also tells you whether the row has been updated, deleted, or added since the last fetch.

In addition, you can refresh the data and status for the current rowset by calling CRecordset::RefreshRowset().

The following example shows how you can use MoveNext() to dump the data returned by a recordset using Bulk RFX:

do {
    for(int i=0;i<5;i++) {
        if(pBulkSet->GetRowStatus(i+1) == SQL_ROW_SUCCESS)
            TRACE("Num: %d Title: [%s] Pages: %d\n",
                *(pBulkSet->m_Num + i),
                pBulkSet->m_Title + i*50,
                *(pBulkSet->m_Pages + i));
        else
            TRACE("No Row Fetched\n");
    }
    // Get Next rowset
    pBulkSet->MoveNext();
}
while(!pBulkSet->IsEOF());

`CheckRowsetError()`

When you call any of the cursor navigation functions, including Open(), Requery(), Move(), or MoveNext(), the MFC framework calls CRecordset::CheckRowsetError() to process any errors that may occur. If your application needs to do any special error processing, you can override this function.

Using Single-Row Functions

For functions such as CRecordset::GetFieldValue(), which work on a single row, you set the current row within the rowset. This is done with the SetRowsetCursorPosition() member of CRecordset.

Summary | Next Section

Previous Section