PRB: Access Violation in ODBC Cursor Library in MFC Application (185864)

RESOLUTION

If you use the MFC database classes, there are two possible workarounds:

• Do not use the cursor library.
• Override the MFC code to add three spaces to the end of each SQL statement before calling the ODBC SQLPrepare() function.

It might not be necessary to use the ODBC Cursor Library. If you mainly add, update, or delete records then you should use dynasets because dynasets do not use the Cursor Library and they give the best performance for these scenarios. If you are fetching data frequently, then you can use snapshots without using the Cursor Library. You can avoid using the Cursor Library by specifying FALSE for the last argument (bUseCursorLib) of CDatabase::Open(), or use the CDatabase::OpenEx() member function which doesn't load the Cursor Library by default.

Use the Cursor Library where you need to modify records and need the fetching advantages of a snapshot. To use the Cursor Library, you must override the two areas in MFC where SQLPrepare() is called. You need to add three spaces to the end of the SQL string that gets passed to SQLPrepare. To do this, create a new CRecordset-derived class and perform the following steps:

1. Copy the code in the CRecordset class (present in dbcore.cpp) for the following member functions into your new CRecordset-derived class:

         CRecordset::Open()
         CRecordset::Update()
         CRecordset::PrepareAndExectue()
         CRecordset::BuildUpdateSQL()
         CRecordset::UpdateInsertDelete()
         CRecordset::Delete()
   						

2. Make the following modifications to BuildUpdateSQL() and PrepareAndExecute() in the new CRecordset class.
   
   
   1. For BuildUpdateSQL(), replace this statement:

       
               AFX_ODBC_CALL(::SQLPrepare(m_hstmtUpdate,
                   (UCHAR*)T2A((LPTSTR)(LPCTSTR)(m_strUpdateSQL), SQL_NTS));
            
      								

      with this code:

               AFX_ODBC_CALL(::SQLPrepare(m_hstmtUpdate,
                   (UCHAR*)T2A((LPTSTR)(LPCTSTR)(m_strUpdateSQL+"   ")),
                   SQL_NTS));
           
      								

      This adds three spaces to the SQL statement.
   2. For PrepareAndExectue(), replace this statement:

       
               LPCSTR lpszWSQL = T2CA(m_strSQL);
           
      								

      with this code:

       
               CString strSQLTmp = m_strSQL + "   "; // Added three spaces.
               LPCSTR lpszWSQL = T2CA(strSQLTmp);