MFC CRecordset - How to Bypass the 255 Column Limit

Using the Code

The native CRecordset is limited with 255 columns. See code from dbcore.cpp.

UINT CRecordset::BindFieldsToColumns()
{
ASSERT_VALID(this);
ASSERT(m_hstmt != SQL_NULL_HSTMT);

ASSERT(m_nFieldsBound == 0);
ASSERT(m_nFields != 0 && m_nFields <= 255);
...
}

m_nFields is short, which means, it should handle more than 255.

Answer by Microsoft:

The reason is historical, I think. Prior to SQL 7, the 255 column limit was one that a lot of databases had, and the MFC developers had to pick some limit, and I guess that was the one that they chose. One workaround may be to specify fields rather than doing a Select *, and get the first 200 fields in one recordset, the next 200 fields in the second, etc. Be aware that if you do this, there is a limit to how large or complex a select can be, and you may run into it.. Another workaround would be to use ODBC API, which doesn't have this limitation. You may need to add memory though. ODBC is limited only by the system memory, but it's going to take a LOT of memory to load 100000 rows of 1500 columns. I hope this helps, Russ Gray Microsoft Developer Support

Reading the CRecordset definition in ...\MFC\include\AFXDB.H, I noticed that BindFieldsToColumns() is called at one point. The functions are virtual, so if you derive the CRecordset class to another one, you can easily bypass the 255 limit.

First, override the two functions Move(..) and SetRowsetSize(..) and just change calls to InitRecord() and BindFieldsToColumns() to your own functions (for example, _InitRecord(..) and _BindFieldsToColumns(..))

myRecordSet::myRecordSet(...): CRecordset(pdb)

// 2 Virtual functions overridden
// copy from dbcore.cpp and change one call in each only
void myRecordSet::Move(long nRows, WORD wFetchType)
{
	ASSERT_VALID(this);
	ASSERT(m_hstmt != SQL_NULL_HSTMT);

	// First call - fields haven't been bound (m_nFieldsBound will change)
	if (m_nFieldsBound == 0)
	{
		_InitRecord(); // <= initially call to InitRecord()
		ResetCursor();
	}

	if (m_nFieldsBound > 0)
	{
		// Reset field flags - mark all clean, all non-null
		memset(m_pbFieldFlags, 0, m_nFields);

		// Clear any edit mode that was set
		m_nEditMode = noMode;
	}

	// Check scrollability, EOF/BOF status
	CheckRowsetCurrencyStatus(wFetchType, nRows);

	RETCODE nRetCode;

	// Fetch the data, skipping deleted records if necessary
	if ((wFetchType == SQL_FETCH_FIRST ||
		wFetchType == SQL_FETCH_LAST ||
		wFetchType == SQL_FETCH_NEXT ||
		wFetchType == SQL_FETCH_PRIOR ||
		wFetchType == SQL_FETCH_RELATIVE) &&
		m_dwOptions & skipDeletedRecords)
	{
		SkipDeletedRecords(wFetchType, nRows, &m_dwRowsFetched, &nRetCode);
	}
	else
		// Fetch the data and check for errors
		nRetCode = FetchData(wFetchType, nRows, &m_dwRowsFetched);

	// Set currency status and increment the record counters
	SetRowsetCurrencyStatus(nRetCode, wFetchType, nRows, m_dwRowsFetched);

	// Need to fixup bound fields in some cases
	if (m_nFields > 0 && !IsEOF() && !IsBOF() &&
		!(m_dwOptions & useMultiRowFetch))
	{
		Fixups();
	}
}

void myRecordSet::SetRowsetSize(DWORD dwNewRowsetSize)
{
	ASSERT_VALID(this);
	ASSERT(dwNewRowsetSize > 0);

	// If not yet open, only set expected length
	if (!IsOpen())
	{
		m_dwRowsetSize = dwNewRowsetSize;
		return;
	}

	if (!(m_dwOptions & useMultiRowFetch))
	{
		// Only works if bulk row fetching!
		ASSERT(FALSE);
		return;
	}

	// Need to reallocate some memory if rowset size grows
	if (m_dwAllocatedRowsetSize == 0 ||
		(m_dwAllocatedRowsetSize < dwNewRowsetSize))
	{
		// If rowset already allocated, delete old and reallocate
		FreeRowset();
		m_rgRowStatus = new WORD[dwNewRowsetSize];

		// If not a user allocated buffer grow the data buffers
		if (!(m_dwOptions & userAllocMultiRowBuffers))
		{
			// Allocate the rowset field buffers
			m_dwRowsetSize = dwNewRowsetSize;
			CFieldExchange fx(CFieldExchange::AllocMultiRowBuffer, this);
			DoBulkFieldExchange(&fx);

			m_dwAllocatedRowsetSize = dwNewRowsetSize;

			// Set bound fields to zero, rebind and reset bound field count
			int nOldFieldsBound = m_nFieldsBound;
			m_nFieldsBound = 0;
			_InitRecord(); // <= initially call to InitRecord()
			m_nFieldsBound = nOldFieldsBound;
		}
	}
	else
	{
		// Just reset the new rowset size
		m_dwRowsetSize = dwNewRowsetSize;
	}

	RETCODE nRetCode;
	AFX_SQL_SYNC(::SQLSetStmtOption(m_hstmt, SQL_ROWSET_SIZE, m_dwRowsetSize));
}

Nothing changes in this one except the number of maximum of columns set here to 1024 or it can be set in a global variable with a call to SQLGetInfo(...) with SQL_MAX_COLUMNS_IN_TABLE.

// new function in the derived class
// copy from dbcore.cpp and change number limit
UINT myRecordSet::_BindFieldsToColumns() // previously BindFieldsToColumns() 
                                         // in CRecordSet class
{
	ASSERT_VALID(this);
	ASSERT(m_hstmt != SQL_NULL_HSTMT);

	ASSERT(m_nFieldsBound == 0);
	ASSERT(m_nFields != 0 && m_nFields <= 1024); // initially 255 in dbcore.cpp

	CFieldExchange fx(CFieldExchange::BindFieldToColumn, this);
	fx.m_hstmt = m_hstmt;

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

	return fx.m_nFields;
}

And finally, change the call from BindFieldsToColumns() to _BindFieldsToColumns(). That's all. :)

// new function in the derived class
// copy from dbcore.cpp and change one call only
void myRecordSet::_InitRecord() // previously InitRecord() in CRecordSet class
{
	// fields to bind
	if (m_nFields != 0)
	{
		m_nFieldsBound = _BindFieldsToColumns(); // <= initially call to BindFieldsToColumns()
		// m_nFields doesn't reflect number of
		// RFX_ output column calls in Do[Bulk]FieldExchange
		ASSERT((int)m_nFields == m_nFieldsBound);

		// Allocate the data cache if necessary
		if (m_nFields > 0 && m_bCheckCacheForDirtyFields)
			AllocDataCache();
	}
	else
		// No fields to bind, don't attempt to bind again
		m_nFieldsBound = -1;
}

Points of Interest

It took me fifteen minutes to do this. Four functions to copy, and only change three calls, instead of writing my own class from scratch in native ODBC API as Microsoft says.

History

• 21^st May, 2021: First version & should be the only one