Limitations of DAO, DAO SDK in NT Service or with Threads

The information in this article applies to:

• The Microsoft Foundation Classes (MFC), included with: Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1, and 4.2
• The DAO SDK, included with: Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1, and 4.2

SUMMARY

With the release of MFC ODBC in Visual C++ 4.2, you can use both MFC and the MFC ODBC classes within the multithreaded environment of an NT Service. Neither the MFC DAO classes nor DAO SDK are thread-safe because the underlying Jet engine is not thread-safe and cannot be used in an NT Service.

The techniques presented here are valid for any multithreaded environment with the MFC ODBC classes, not just an NT Service.

The remainder of this article explores the following three areas relating to use of MFC-based Database classes within an NT Service:

• How an NT Service works
• How to use MFC ODBC in a multithreaded environment
• Why MFC DAO or DAO SDK cannot be used in a multithreaded environment

MORE INFORMATION

How an NT Service Works

An NT Service consists of code that is roughly similar to the following:

   VOID WINAPI Main( VOID )
   {
       ...

       if (!StartServiceCtrlDispatcher(...))
           AddToMessageLog(TEXT("StartServiceCtrlDispatcher failed."));

       ...
   }

Any attempt to use database classes within a running service must not only be thread-safe, but they must also be able to operate in the secondary thread.

How to Use MFC ODBC in a Multithreaded Environment

With the release of Visual C++ 4.2, both MFC and the MFC ODBC database code is safe to use in a multithreaded environment. However, you should keep the following points in mind:

• Use an ODBC Driver That Is Thread-Safe.

  The multithreading support for the ODBC classes has some limitations. Because these classes wrap the ODBC API, they are restricted to the multithreading support of the components on which they are built. For example, there are many ODBC drivers that are not thread-safe; therefore, the MFC ODBC classes are not thread-safe if you use them with one of these drivers. You should verify whether your particular driver is thread-safe, as well as any components it uses.

• Use a Global Instance of CCriticalSection to Provide Synchronization when Using MFC ODBC Classes.

  While the MFC ODBC classes are thread-safe, it is the programmers job to ensure synchronization of the handle state. The safest way to do this is to use a CCriticalSection object, and Lock any methods that manipulate a handle in either CRecordset or CDatabase--in particular any method that modifies the CRecordset::m_hStmt member.

• Avoid Using MFC ODBC Globally or Across Multiple Threads.

  When creating a multithreaded application, you should be very careful in using multiple threads to manipulate the same object. For example, using the same CRecordset object in two threads may cause problems when retrieving data; a fetch operation in one thread may overwrite the data fetched in the other thread.

  A global Database object, even protected by a global instance of CCriticalSection, may not be safe.

• Use a System DSN for Your ODBC DataSource.

  Any ODBC DataSource used by the MFC ODBC classes within an NT Service must be a System DSN. Without a System DSN you see the following error when trying to open the data source:

        Data source name not found and no default driver specified
        State:IM002,Native:0,Origin:[Microsoft][ODBC Driver Manager]
  

  The process for creating a System DSN is identical to creating a regular data source. Open the 32-bit ODBC Administrator (in the Control Panel) and click the button that says "System DSN" to create or modify your System DataSources.

  See the KB article listed in the REFERENCES section below for details.

• It May Be Necessary to Impersonate a Valid NTUser.

  When using an ISAPI dynamic-link library (DLL) to get to a data source that is not on the same machine as the ISAPI DLL. For example, an ISAPI DLL using MFC ODBC to manipulate data on a SQL Server that is on a different machine requires a valid NT Domain userid and password. Otherwise, the NT machine running SQL Server does not allow the ISAPI DLL to make the connection.

  Win32 provides several functions allowing you to login with a valid userid and password, letting the ISAPI DLL "impersonate" a valid user.

  See the two KB articles listed in the REFERENCES section below for details.

• Using MFC ODBC in a NT Service Is Just Like Using MFC ODBC in a Console Application.

  An NT Service is similar to a console application in that it does not offer the standard windows Message Pump. The MFC ODBC classes, with Visual C++ 4.2, have been written to be more friendly to console applications. But there are some issues you need to be aware of.

  See the KB article listed in the REFERENCES section below for details.

DAO version 3.x is a single-threaded inproc server and was originally written for Access 1.x and 2.0 and Visual Basic 3.0--both of which are single-threaded. This means that clients (MFC DAO or DAO SDK) can use DAO via custom interfaces only from the first thread in the process that initialized COM—that is, 'primary' thread.

If the client has already initialized COM in the primary thread and then calls CoCreateInstance in the secondary thread asking for the IDAODBEngine interface, CoCreateInstance fails. This fails because COM sees that DAO is not marked as apartment or free threaded in the registry, calls DllGetClassObject from the primary thread, gets the IDAODBEngine interface, tries to marshal it back to the secondary thread, and fails.

See the KB article listed in the REFERENCES section below for details.

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q169395
   TITLE     : PRB: Thread Safety for DAO/Jet 3.5

System Data Sources

   ARTICLE-ID: Q136481
   TITLE     : INF: Running ODBC Applications as Windows NT Services

Impersonating a Valid NT User

   ARTICLE-ID: Q96005
   TITLE     : Validating User Accounts (Impersonation)

Using MFC Database Classes in a Console Application

   ARTICLE-ID: Q152696
   TITLE     : HOWTO: Using the MFC Database Classes in Console
               Applications

   ARTICLE-ID: Q151407
   TITLE     : PRB: DAO Must Be Used in Primary Thread

Using MFC ODBC with an ISAPI DLL

   ARTICLE-ID: Q160906
   TITLE     : HOWTO: Use MFC 4.2 ODBC Classes in an ISAPI DLL
               Password

Keywords          : MfcDAO MfcDatabase kbcode
Technology        : kbMfc
Version           : Winnt:4.0,4.1,4.2
Platform          : winnt
Issue type        : kbhowto