FIX: Syntax Error in MFC ODBC Recordset with SQL Server 6.5

• Microsoft Visual C++ for Windows, versions 1.51, 1.52, 1.52b, 1.52c
• Microsoft Visual C++, 32-bit Edition, versions 2.0, 2.1, 2.2, 4.0, 4.1

SYMPTOMS

When you open a dynaset or dynamic recordset that contains an image field and that connects to a SQL Server datasource by way of the 2.65.0201 driver that ships with SQL Server 6.5, you may observe an exception thrown indicating:

   Line 1: Incorrect syntax near 'OF'.
   State:37000,Native:170,Origin:[Microsoft][ODBC SQL Server Driver]
   [SQL Server]

CAUSE

The MFC database classes are designed to adhere to the ODBC specification. The grammar for the specification contains a error that indicates that all compliant drivers must support the appearance of 'FOR UPDATE OF' in SELECT statements. In reality, the specification grammar (available in the ODBC Programmer's Reference in Visual C++ Books Online) should make support of the 'OF' keyword optional:

   select-for-update-statement ::=
        SELECT [ALL | DISTINCT] select-list
        FROM table-reference-list
        [WHERE search-condition]
        FOR UPDATE [OF column-name [, column-name]...]

• If you are using snapshots, the cursor library will intercept the SELECT statement and remove the 'FOR UPDATE OF' before forwarding the SQL statement on to the driver.
• If you are using dynasets, the MFC database classes will usually use SQLSetPos instead of SQL UPDATE statements in which case the 'FOR UPDATE OF' is not appended to the initial SELECT statement.
• If you are using dynasets that contain long binary fields and the driver that is in use does not support calling SQLGetData for bound columns, SQL UPDATE statements are used instead of SQLSetPos. However, a special function that checks if the driver is the SQL Server driver and if the version is 2.50 or earlier prevents the use of the 'FOR UPDATE OF' syntax. This check is performed to avoid a bug in earlier versions of the driver. It is implemented in the function:

        inCRecordset::ValidateSelectForUpdateSupport().
  

RESOLUTION

32 bit

While it might seem logical to simply override the function CRecordset::ValidateSelectForUpdateSupport() so that it informs MFC that the 2.65.0210 driver does not support FOR UPDATE OF, this is not easily done because that function is not virtual.

Instead, you can override CRecordset::OnSetOptions() and, following the call to the base class, reset the value of m_dwDriverPositionedStatements to clear the bit that corresponds to SQL_PS_SELECT_FOR_UPDATE:

   void CDerivedSet::OnSetOptions(HSTMT hstmt)
   {
       CRecordset::OnSetOptions(hstmt);
       m_dwDriverPositionedStatements &= ~SQL_PS_SELECT_FOR_UPDATE;
   }

16 bit

If you are using dynasets by way of the DYNSET sample code in 16-bit Visual C, your application actually contains the code that appends the FOR UPDATE OF, so you can simply modify the Dynacore.cpp file to remove the OF from the szForUpdateOf string.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This problem was corrected in Visual C++ version 4.2.

REFERENCES

ODBC Programmer's Reference, Appendix C: SQL Grammar