Implementing bulk record fetching

Question

At the start of my program, I need to read data from a MS Access database (.mdb) into a drop down control. This is done so that whenever the user types in that control, the application can auto-complete.

Anyway, the reading from database took forever so I thought I'd implement bulk row fetching.

This is the code I have:

CString sDsn;
CString sField;
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
TRY
{
    // Open the database
    database.Open(NULL,false,false,sDsn);

    // Allocate the rowset
    CMultiRowset recset( &database );

    // Build the SQL statement
    SqlString =  "SELECT NAME "
            "FROM INFOTABLE";

    // Set the rowset size. These many rows will be fetched in one bulk operation
    recset.SetRowsetSize(25);

    // Open the rowset
    recset.Open(CRecordset::forwardOnly, SqlString, CRecordset::readOnly | CRecordset::useMultiRowFetch);

    // Loop through each rowset
    while( !recset.IsEOF() )
    {
        int rowsFetched = (int)recset.GetRowsFetched(); // This value is always 1 somehow
        for( int rowCount = 1; rowCount <= rowsFetched; rowCount++ )
        {
            recset.SetRowsetCursorPosition(rowCount);
            recset.GetFieldValue("NAME",sField);
            m_nameDropDown.AddString(sField);
        }

        // Go to next rowset
        recset.MoveNext();
    }

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

MultiRowset.cpp looks like:

#include "stdafx.h"
#include "afxdb.h"
#include "MultiRowset.h"

// Constructor
CMultiRowset::CMultiRowset(CDatabase *pDB)
   : CRecordset(pDB)
{
    m_NameData = NULL;
    m_NameDataLengths = NULL;

    m_nFields = 1;
    CRecordset::CRecordset(pDB);
}

void CMultiRowset::DoBulkFieldExchange(CFieldExchange *pFX)
{
   pFX->SetFieldType(CFieldExchange::outputColumn);
   RFX_Text_Bulk(pFX, _T("[NAME]"), &m_NameData, &m_NameDataLengths, 30);
}

MultiRowset.h looks like:

#if !defined(__MULTIROWSET_H_AD12FD1F_0566_4cb2_AE11_057227A594B8__)
#define __MULTIROWSET_H_AD12FD1F_0566_4cb2_AE11_057227A594B8__

class CMultiRowset : public CRecordset
{
public:
      // Field data members
      LPSTR m_NameData;

      // Pointers for the lengths of the field data
      long* m_NameDataLengths;

      // Constructor
      CMultiRowset(CDatabase *);

      // Methods
      void DoBulkFieldExchange(CFieldExchange *);
};

#endif

And in my database, the INFOTABLE looks like:

NAME    AGE
----    ---
Name1   Age1
Name2   Age2
      .
      .
      .
      .

All I need to do is only read the data from the database. Can someone please tell me what I'm doing wrong? My code right now behaves exactly like a normal fetch. There's no bulk fetching happening.

EDIT:

I just poked around in DBRFX.cpp and found out that RFX_Text_Bulk() initializes my passed m_NameData as new char[nRowsetSize * nMaxLength]!

This means m_NameData is only a character array! I need to fetch multiple names, so wouldn't I need a 2D character array? The strangest thing is, the same RFX_Text_Bulk() initializes my passed m_NDCDataLengths as new long[nRowsetSize]. Why in the world would a character array need an array of lengths?!

In fact, m_NameData represents an array of character arrays. m_NDCDataLengths represents the lenghts of each strings in m_NameData — Goldorak84, Apr 02 '13 at 13:59
@Goldorak84, But `m_nameData` is initialized as `new char[nRowsetSize * nMaxLength];`. Doesn't that make it a character array of length `nRowsetSize * nMaxLength`? — Anish Ramaswamy, Apr 02 '13 at 15:09
There's something wrong with the CMultiRowset constructor. You should remove CRecordset::CRecordset(pDB); at the end of the function. It may reset m_nFields to 0 — Goldorak84, Apr 02 '13 at 15:42

AnatolyS · Answer 1 · 2013-03-05T09:26:49.937

3

According to http://msdn.microsoft.com/en-us/library/77dcbckz.aspx#_core_how_crecordset_supports_bulk_row_fetching you have to open CRecordset with CRecordset::useMultiRowFetch flag before call SetRowsetSize:

To implement bulk row fetching, you must specify the CRecordset::useMultiRowFetch option in the dwOptions parameter of the Open member function. To change the setting for the rowset size, call SetRowsetSize.

edited Mar 05 '13 at 09:26

answered Mar 05 '13 at 09:18

AnatolyS

4,249
18
28

That link seems to be broken. Also from [this MSDN article](http://msdn.microsoft.com/en-us/library/77dcbckz(v=vs.80).aspx), `After you have initialized the rowset size, call the Open member function. Here you must specify the CRecordset::useMultiRowFetch option`. This seems contradictory. – Anish Ramaswamy Mar 05 '13 at 09:22
1

I have fixed the link. Yes, you are correct. Try to check that bulk fetching is implemented - call GetRowsetSize before SetRowsetSize: {quote} Before opening your recordset object, you can define a rowset size with the SetRowsetSize member function. The rowset size specifies how many records should be retrieved during a single fetch. When bulk row fetching is implemented, the default rowset size is 25. If bulk row fetching is not implemented, the rowset size remains fixed at 1.{quote} – AnatolyS Mar 05 '13 at 09:32
The rowset size seems to be set correctly. I'm setting it to 25 currently. `GetRowsetSize()` returns 25 too. – Anish Ramaswamy Mar 05 '13 at 09:52

Goldorak84 · Accepted Answer · 2013-04-02T16:53:58.860

2

You almost got it right. To fetch the values, I would change your

        for( int rowCount = 1; rowCount <= rowsFetched; rowCount++ )
        {
            recset.SetRowsetCursorPosition(rowCount);
            recset.GetFieldValue("NAME",sField);
            m_nameDropDown.AddString(sField);
        }

by something like this

for( int nPosInRowset = 0; nPosInRowset < rowsFetched; nPosInRowset++ )
{
    //Check if value is null
    if (*(recset.m_NameDataLengths + nPosInRowset) == SQL_NULL_DATA)
        continue;    

    CString csComboString;
    csComboString = (recset.m_NameData + (nPosInRowset * 30)); //Where 30 is the size specified in RFX_Text_Bulk

    m_nameDropDown.AddString(csComboString);
}

EDIT: To fetch more than one row, remove the CRecordset::forwardOnly option

EDIT 2 : You can also keep CRecordset::forwardonly, but add the CRecordset::useExtendedFetch option

edited Apr 02 '13 at 16:53

answered Apr 02 '13 at 14:10

Goldorak84

3,714
3
38
62

I did try this. Problem is, `rowsFetched` is always 1! – Anish Ramaswamy Apr 02 '13 at 15:10
On what kind of database are you connecting to? – Goldorak84 Apr 02 '13 at 15:31
It's an MS Access database (.mdb) – Anish Ramaswamy Apr 02 '13 at 15:37
1

I edited my post : To fetch more than one row, remove the CRecordset::forwardOnly option – Goldorak84 Apr 02 '13 at 16:27
You can also keep CRecordset::forwardonly, but add the CRecordset::useExtendedFetch option – Goldorak84 Apr 02 '13 at 16:54
Okay so now, `rowsFetched` = 25. [From the MSDN documentation for `CRecordset::Open`](http://msdn.microsoft.com/en-us/library/1hkkwdf0(v=vs.80).aspx), _"If you specify the option CRecordset::useMultiRowFetch on a forward-only recordset, then CRecordset::useExtendedFetch will be turned on automatically."_. That's why I didn't use it. Oh well. – Anish Ramaswamy Apr 04 '13 at 08:06
There's a fundamental problem with the way these text strings are treated. `m_nameData` is of type `LPSTR` (AKA `char *`). `RFX_Text_Bulk` is called by passing `&m_nameData` into the `prgStrVals` argument. `RFX_Text_Bulk` then allocates memory for `*prgStrVals` as `new char[nRowsetSize * nMaxLength]`. By my understanding, __this means `m_nameData` is a character array!__ Shouldn't `m_nameData` be of type `LPSTR *` (AKA `char **`)? – Anish Ramaswamy Apr 04 '13 at 08:23
1

It would have been more logical to use LPSTR *, but if we look for performance, it's faster to allocate only one new char[nRowsetSize * nMaxLength] instead of (nRowsetSize) times new char[nMaxLength]. If you use the code provided above, you should be able to fill your combo box. Does this answer your problem? – Goldorak84 Apr 04 '13 at 12:28
1

Okay I just modified your `CString` assignment to `csComboString.Format("%s", (recset.m_NameData + (nPosInRowset * 30)));` and bingo! This works like a charm. Thank you so much! – Anish Ramaswamy Apr 04 '13 at 12:55
Also, what rowset size would you suggest? – Anish Ramaswamy Apr 04 '13 at 12:57
I use a rowset of 50. I made tests with 100 and 1000, and didn't notice a difference. Glad I could help! – Goldorak84 Apr 04 '13 at 13:02
After all this, I notice that there is no noticeable difference in my application .__. – Anish Ramaswamy Apr 04 '13 at 13:10
A way to solve this is to, when your form first loads, fill your combobox only with the selected value. Then, implement the ON_CBN_SETFOCUS event to fill completely your combobox. Your combo will fill only when the user focuses (before click) on it. It works very well in my application – Goldorak84 Apr 04 '13 at 13:29

score 0 · Answer 3 · edited Feb 23 '16 at 14:30

Just faced the same problem. You should use in recset.Open() call for dwOptions parameter only CRecordset::useMultiRowFetch, and not CRecordset::readOnly | CRecordset::useMultiRowFetch. Hope this helps someone...

EDIT:- After re-check here is the situation - when using bulk recordset and opening with CRecordset::forwardOnly and CRecordset::readOnly, you must also specify CRecordset::useExtendedFetch in dwOptions. For other types of scrolling, using CRecordset::readOnly | CRecordset::useMultiRowFetch is just fine.

Implementing bulk record fetching

3 Answers3

Linked