Microsoft KB Archive/133311

= FIX: Errors Occur When SQL Server Binary Field Over 64K =

Article ID: 133311

Article Last Modified on 11/21/2006

-

APPLIES TO

 Microsoft Foundation Class Library 4.2, when used with:  Microsoft Visual C++ 1.52 Professional Edition

 Microsoft Visual C++ 2.0 Professional Edition

 Microsoft Visual C++ 2.1

 Microsoft Visual C++ 2.2</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q133311

<div class="symptoms_section">

SYMPTOMS
When you try to store more than 64K of data in a binary field using the SQL Server ODBC 2.0 driver version 2.00.1912, data is truncated. The following error may also occur with the 16-bit version of the driver:

<pre class="fixed_text">  Incorrect syntax near 'NULLNULL'. State:37000[Microsoft][ODBC SQL Server Driver][SQL Server]

NOTE: 32-bit applications using the SQL Server Driver delivered with Visual C++ 4.x (version 2.50.0126) should not exhibit this behavior.

<div class="cause_section">

CAUSE
The SQL Server driver fails to handle calls to SQLSetParam correctly.

Also, the data needs to be retrieved and sent in chunks less than 64K. The existing implementation for the 32-bit database classes reads and writes the data in one large chunk and encounters network errors with the SQL Server ODBC driver when sending chunks of data larger than 64K.

<div class="resolution_section">

RESOLUTION
The SQLSetParam ODBC 1.0 function has been replaced by the ODBC 2.0 SQLBindParameter function. The MFC database classes use SQLSetParam to maintain compatibility with ODBC 1.0 drivers. If you are using the SQL Server ODBC 2.0 driver, you can work around the SQLSetParam bug by calling SQLBindParameter instead.

If you are in a 32-bit environment, you'll also need to override some functions to read and write the binary data in chunks of less than 64K bytes.

To fix the MFC Database classes, perform the following steps:

<ol> Copy the RFX_LongBinary function from \MSVC\MFC\SRC\DBRFX.CPP into your own .CPP file, and rename the function to something like RFX_LongBinary2.</li>  Modify the RFX_LongBinary function by replacing the call to SQLSetParam with a call to SQLBindParameter. You'll see a line of code such as this: AFX_SQL_SYNC(::SQLSetParam(pFX->m_hstmt, (unsigned short int)pFX->m_nParamFields, SQL_C_DEFAULT, // Get actual SQL type - may differ from CType (e.g. DECIMAL) (short int)pFX->GetColumnType(nField), value.m_dwDataLength, 0, &value, plLength)); Replace it with this code: AFX_SQL_SYNC(::SQLBindParameter(pFX->m_hstmt, (unsigned short int)pFX->m_nParamFields,SQL_PARAM_INPUT, SQL_C_DEFAULT, // Get actual SQL type - may differ from CType (e.g. DECIMAL) (short int)pFX->GetColumnType(nField), value.m_dwDataLength, 0, &value, sizeof(value), plLength)); Replace the following code in the CFieldExchange::Value case: *plLength = SQL_DATA_AT_EXEC; with this code: *plLength=SQL_LEN_DATA_AT_EXEC(((LONG)value.m_dwDataLength)); </li>  For the 32-bit database classes, add code to the RFX_LongBinary2 function to get the binary data in chunks. Again, use the the RFX_LongBinary2 function below.

Replace this code: AFX_SQL_ASYNC(pFX->m_prs, ::SQLGetData(pFX->m_prs->m_hstmt, (UWORD)nField,SQL_C_BINARY, (UCHAR*)lpLongBinary, *plLength, plLength)); with this code: do     { DWORD dwChunkSize = value.m_dwDataLength - dwDataLength; if (dwChunkSize > 0x8000) dwChunkSize = 0x8000;

// Ignore expected data truncated warnings AFX_SQL_ASYNC(pFX->m_prs, ::SQLGetData(pFX->m_prs->m_hstmt, (unsigned short int)nField, SQL_C_BINARY, (UCHAR FAR*)lpLongBinary, dwChunkSize, plLength));

dwDataLength += dwChunkSize; lpLongBinary += dwChunkSize;

} while (nRetCode == SQL_SUCCESS || nRetCode ==        SQL_SUCCESS_WITH_INFO); And do the following:

<ol>  At the top of the RFX_LongBinary2 function, declare the DWORD variable dwDataLength and initialize it to zero:

void AFXAPI RFX_LongBinary2(CFieldExchange* pFX,      const char *szName, CLongBinary& value) {        DWORD dwDataLength = 0;     // Add this line! .     .      .                                </li> Include <limits.h> in the .CPP file that contains RFX_LongBinary2. You need this because the RFX_LongBinary code that you copied over has a reference to the LONG_MAX constant.</li>  Add a global static char* called szComma, and initialize it to point to a string literal that contains a single comma: static char* szComma = ","; Do this because RFX_LongBinary refers to this variable, which is in the file DBRFX.CPP. Because it is declared static in that file, it cannot be seen outside of it, so it must be supplied in the file that contains RFX_LongBinary2. </li></ol> </li> Change the call to RFX_LongBinary in your CRecordset DoFieldExchange function so that the new RFX_LongBinary2 function is called.</li>  For the 32-bit database classes, copy the code for Update, UpdateInsertDelete, ExecuteUpdateSQL, ExecuteSetPosUpdate, and SendLongBinaryData into your own CRecordset functions. You'll need to add code to the SendLongBinaryData function. The only reason the other functions must be copied to your recordset is because the Update function is virtual. By doing this, you make certain that the corrected SendLongBinaryData function is called.

Replace the following code in SendLongBinaryData: AFX_SQL_ASYNC(this, ::SQLPutData(hstmt, (PTR)lpData, pLongBinary->m_dwDataLength)); ::GlobalUnlock(pLongBinary->m_hData);

if (!Check(nRetCode)) {        // cache away error CDBException* pException = new CDBException(nRetCode); pException->BuildErrorString(m_pDatabase, hstmt);

// then cancel Execute operation Cancel; THROW(pException); } with this code: DWORD dwDataLength = 0; while (dwDataLength != pLongBinary->m_dwDataLength) {        DWORD dwSend = pLongBinary->m_dwDataLength-dwDataLength; if (dwSend > 0x8000) dwSend = 0x8000; AFX_SQL_ASYNC(this, ::SQLPutData(m_hstmtUpdate, (PTR)lpData, dwSend)); if (!Check(nRetCode)) {           ::GlobalUnlock(pLongBinary->m_hData); // Cache away error CDBException* pException = new CDBException(nRetCode); pException->BuildErrorString(m_pDatabase, m_hstmtUpdate); // Then cancel Execute operation Cancel; THROW(pException); }        lpData += dwSend; dwDataLength += dwSend; }                           </li></ol>

Correction for SendLongBinaryData Code
BOOL CMyRecordset::Update {  // Insert the CRecordset::Update code from MFC\SRC\DBCORE.CPP } BOOL CMyRecordset::UpdateInsertDelete {  // Insert the CRecordset::UpdateInsertDelete code // from MFC\SRC\DBCORE.CPP }

void CMyRecordset::ExecuteUpdateSQL {  // Insert the CRecordset::ExecuteUpdateSQL code // from MFC\SRC\DBCORE.CPP }

void CMyRecordset::ExecuteSetPosUpdate {  // Insert the CRecordset::ExecuteSetPosUpdate code // from MFC\SRC\DBCORE.CPP }

void CMyRecordset::SendLongBinaryData(HSTMT hstmt) {  RETCODE nRetCode; void* pv; AFX_SQL_ASYNC(this, ::SQLParamData(hstmt, &pv)); if (!Check(nRetCode)) {     // cache away error CDBException* pException = new CDBException(nRetCode); pException->BuildErrorString(m_pDatabase, hstmt);

// then cancel Execute operation Cancel; THROW(pException); }

while (nRetCode == SQL_NEED_DATA) {     CLongBinary* pLongBinary = (CLongBinary*)pv; ASSERT_VALID(pLongBinary);

const BYTE* lpData = (const BYTE*)::GlobalLock(pLongBinary->m_hData); ASSERT(lpData != NULL);

DWORD dwDataLength = 0; while (dwDataLength != pLongBinary->m_dwDataLength) {        DWORD dwSend = pLongBinary->m_dwDataLength-dwDataLength; if (dwSend > 0x8000) dwSend = 0x8000; AFX_SQL_ASYNC(this,         ::SQLPutData(m_hstmtUpdate, (PTR)lpData, dwSend)); if (!Check(nRetCode)) {           ::GlobalUnlock(pLongBinary->m_hData); // Cache away error CDBException* pException = new CDBException(nRetCode); pException->BuildErrorString(            m_pDatabase, m_hstmtUpdate); // Then cancel Execute operation Cancel; THROW(pException); }        lpData += dwSend; dwDataLength += dwSend; }

// Check for another DATA_AT_EXEC AFX_SQL_ASYNC(this, ::SQLParamData(hstmt, &pv)); if (!Check(nRetCode)) {        TRACE0("Error: failure handling long binary"                "value during update.\n"); ThrowDBException(nRetCode, hstmt); }  } }

<div class="status_section">

STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This problem has been corrected in the 32- bit SQL Server driver that ships with Visual C++ 4.0 and 4.1 (driver version 2.50.0126).

<div class="references_section">