PRB: CLongBinary Field Truncated with SQL Server ODBC Driver

The information in this article applies to:

• The Microsoft Foundation Classes (MFC), included with: Microsoft Visual C++ for Windows, versions 1.5 and 1.51

SYMPTOMS

When trying to update a CLongBinary field using the ODBC SQL Server driver, the data is truncated.

CAUSE

The RFX_LongBinary code tries to transfer the CLongBinary field data in 32K byte chunks, but the default maximum amount of data the SQL Server ODBC driver version can transfer at a time is 4K bytes. This only applies to driver manager (ODBC.DLL), cursor library (ODBCCURS.DLL), and SQL server driver (SQLSRVR.DLL) that came with Visual C++ version 1.50 or 1.51.

RESOLUTION

The newer ODBC SQL Server driver doesn't have a limitation on the transfer size. It is included with Visual C++ version 1.52, with the ODBC SDK version 2.1, and with the ODBC Driver pack version 2.0.

If you aren't using the newest SQL Server driver, you can work around the problem by using the ODBC API SQLSetStmtOptions() function to set the maximum amount of data that the SQL Server driver can transfer at one time. Set the value to something larger than 32K bytes.

STATUS

This behavior is by design.

MORE INFORMATION

The MFC CRecordset class has a virtual function OnSetOptions() that is a good place to make the SQLSetStmtOptions() call. In that call, specify SQL_MAX_LENGTH as the second parameter and a value of 32K bytes or greater as the third parameter. The override of the OnSetOptions() function should look something like this:

   void CMyRecordset::OnSetOptions(HSTMT hstmt)
   {
     SQLSetStmtOption(hstmt,SQL_MAX_LENGTH,(UDWORD)100000);
     CRecordset::OnSetOptions(hstmt);
   }

REFERENCES

For more information about other problems with CLongBinary fields, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q118758
   TITLE     : BUG: & FIX: Problems that Occur When Using CLongBinary Field