Microsoft KB Archive/139511

= FIX: Text Data Truncated When Using optimizeBulkAdd =

Article ID: 139511

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft Foundation Class Library 4.2

-



This article was previously published under Q139511



SYMPTOMS
When you try to use the optimizeBulkAdd flag with a CRecordset object, text fields may be truncated. For example, if the first insertion in the table inserts a text value in field1 that is four characters long and a second insertion inserts a text value in field1 that is five characters, the text in the second insertion will be truncated to four characters, the length of the text from the first insertion.



CAUSE
MFC is incorrectly binding the length of the parameters it uses in the INSERT INTO statement it constructs for adding a record.



RESOLUTION
Use either of these two workarounds:

 As the first record you add, insert a dummy record that contains strings with the maximum length you want for the text fields in the recordset. Every record after this first insertion will always have text values with lengths that are less than or equal to the length of the text fields in the dummy record. When you have finished inserting records, delete the dummy record. -or-

  Replace the RFX_Text function calls in your CRecordset's DoFieldExchange with the following RFX_Text2 function, which performs the correct binding for the parameters used in the INSERT INTO statement used internally by MFC to add a record. In your recordset's header file add: void RFX_Text2(CFieldExchange* pFX, const char* szName,                 CString& value, int nMaxLength=255,                  int nColumnType=SQL_VARCHAR);

In your recordset's .cpp file add:

void RFX_Text2(CFieldExchange* pFX, const char* szName,    CString& value, int nMaxLength /*=255*/, int nColumnType                                             /* =SQL_VARCHAR */) {    if (pFX->m_nOperation != CFieldExchange::NameValue &&         pFX->m_nOperation != CFieldExchange::Value &&         pFX->m_nOperation != CFieldExchange::BindParam) {       RFX_Text(pFX, szName, value, nMaxLength, nColumnType); return; }

ASSERT(AfxIsValidAddress(pFX, sizeof(CFieldExchange))); ASSERT(AfxIsValidString(szName)); ASSERT(AfxIsValidAddress&value, sizeof(CString)));

RETCODE nRetCode; UINT nField; if (!pFX->IsFieldType(&nField)) return;

LONG* plLength = pFX->m_prs->GetFieldLength(pFX); switch (pFX->m_nOperation) {      case CFieldExchange::NameValue: if (pFX->m_prs->IsFieldFlagDirty(nField,pFX->m_nFieldType)) {          *pFX->m_pstr += pFX->m_prs->m_pDatabase->QuoteName(szName); *pFX->m_pstr += "="; }          // Fall through

case CFieldExchange::Value: if (pFX->m_prs->IsFieldFlagDirty(nField, pFX->m_nFieldType)) {

// If user marked column NULL, reflect this in length if (pFX->m_prs->IsFieldFlagNull(nField, pFX->m_nFieldType)) *plLength = SQL_NULL_DATA; else *plLength = SQL_NTS;

// If optimizing for bulk add, just set length. if (!(pFX->m_prs->m_dwOptions & CRecordset::optimizeBulkAdd)) {             *pFX->m_pstr += "?"; *pFX->m_pstr += pFX->m_lpszSeparator; pFX->m_nParamFields++;

AFX_SQL_SYNC(::SQLSetParam(pFX->m_hstmt, (unsigned short int)pFX->m_nParamFields, SQL_C_CHAR, (short int)nColumnType, nMaxLength, 0,               value.GetBuffer(0), plLength)); value.ReleaseBuffer;

if (nRetCode != SQL_SUCCESS) pFX->m_prs->ThrowDBException(nRetCode, pFX->m_hstmt); }          }           return;

case CFieldExchange::BindParam: *plLength = SQL_NTS; // Preallocate to nMaxLength value.GetBufferSetLength(nMaxLength); AFX_SQL_SYNC(::SQLSetParam(pFX->m_hstmt, (unsigned short int)nField, (short int)SQL_C_CHAR, (short int)nColumnType, nMaxLength, 0,           value.GetBuffer(0), plLength)); value.ReleaseBuffer;

if (nRetCode != SQL_SUCCESS) pFX->m_prs->ThrowDBException(nRetCode, pFX->m_hstmt); return; }  } 



STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. The code has been corrected in Visual C++ version 2.2 and later.



Sample Code to Reproduce Problem
CMyRecordSet rs; rs.Open(CRecordset::snapshot, NULL, CRecordset::optimizeBulkAdd); rs.AddNew; rs.m_field1="1234"; rs.Update; rs.AddNew; rs.m_field1="12345"; rs.Update; rs.Close; The table will look like this:   field1 --  1234   1234 instead of this:   field1 --  1234   12345

