INF: Microsoft SQL Server DB-Library Has Limited Extensibility

• Microsoft SQL Server versions 6.5, 7.0

SUMMARY

When you work with technologies like COM, ISAPI, Microsoft Transaction Server (MTS), and other extensible architectures, you should strongly consider using ODBC 3.0 instead of DB-Library. Extensible architectures allow the developer to extend, enhance, and customize the environment. The design of DB-Library does not always lend itself well to these technologies. This article discusses some specific extensibility pitfalls of DB-Library and how ODBC or OLEDB addresses them differently.

MORE INFORMATION

There are currently two native ways to connect and work with SQL Server: DB-Library and the SQL Server ODBC driver. The SQL Server ODBC driver does not convert function calls into DB-Library statements. The SQL Server ODBC driver performs just as fast as (or faster than) DB-Library in head-to-head tests.

The goal of COM, MTS, ISAPI, and Extended Stored Procedure (XPROC) is to allow extensibility. DB-Library maintains some information on a per-process basis rather than a per-connection basis, which makes this difficult.

The dbinit() and dbexit() calls should be made when you start and exit the application. If you are building COM components or an ISAPI extension, it becomes difficult if you do not control the entire environment.

Internally, DB-Library maintains an array of valid SQL Server connections. When dbclose() is invoked, the entry is removed from the array. When dbexit() is called, the array is used to close all connections that remain open. If two COM components or ISAPI extension DLLs are not working together, the DB-Library environment may not be set up correctly, and may become corrupted unexpectedly. If you add vendor components to this environment, synchronization of the DB-Library environment becomes very difficult.

Within a SQL Server XPROC you do not have to call dbinit() or dbexit() because it was called by SQL Server process at startup. The environment has already been initialized with configuration parameters set by SQL Server. It is difficult to find out what values have been set; it is not possible to change these values. For example, the maximum open DBPROCESS configuration value is set based on the "user connections" SQL Server configuration value. There are some DB-Library calls available to query these configuration values. Access to these global calls must be thread safe and synchronized. For example, the dbsetmaxprocs() call is one that must be synchronized. For more information about writing ODBC-based XPROCS see the Samples\ODS\XP_ODBC sample that is included with the Microsoft SQL Server Programmer's Toolkit (PTK).

ODBC 3.0 corrects many environment problems by localizing the HENV pointer. Each component can call SQLAllocEnv(). ODBC 3.0 also has a new feature called Connection Pooling. The MTS documentation suggests that you open the connection, do your work and close the connection as quickly as possible. This design model leads to increased scalability of your MTS components.

To handle messages received from the SQL Server, DB-Library uses a callback model and ODBC uses a message retrieval model. This makes DB-Library difficult to integrate into a class/component design. You must install the per-process error and message handlers and make a call to dbsetuserdata() and give it the proper "this or me" pointer. Then in your error or message handler you can use the dbgetuserdata() call to retrieve the proper pointer. This approach can leave gaps in your error and message handling. If you install a global error or message handler, you can easily replace a previously installed handler. You do not get a valid DBPROCESS until you are logged in to SQL Server. This makes it difficult to set or establish a design which allows you to capture errors prior to the dbopen call.

The ODBC model of direct retrieval allows you to handle all errors and messages inline, where they are encountered. This lends itself to class and component design as well as the "throw" construct. If you use the throw operator in a DB-Library error or message handler you will cause internal DB-Library routines to be skipped and subsequently cause DB-Library to stop responding at the client. You simply cannot throw reliably from any callback function.

The SQL-DMO, ESQL for C, and VBSQL products are currently based on the DB-Library. The issues discussed earlier are valid considerations for developing scalable business solutions.

Currently the Microsoft Transaction Server Context object does not handle transactional operations for DB-Library components. If you are developing components with MTS you should use ODBC. Transactions are not maintained for DB-Library connections.

This is not a complete description of issues that you may experience when developing scalable solutions for Microsoft SQL Server using DB-Library or DB-Library-based connectivity methods (such as SQL-DMO). You should consult the Microsoft Knowledge Base for additional reference information.

Other issues regarding the dbsettime(),dbgettime(), and dbsetlogintime() calls are not directly covered here, but are worth considering. For additional information, consult the SQL Server Books Online.

It is highly recommended you use ODBC or OLEDB to develop extensible components. ODBC and OLEDB have been redesigned with scalability in mind. They present a better development environment than that offered by DB- Library and should therefore be considered for all such development projects.

Additional query words: ole db dblib db-lib

Keywords : SSrvProg
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbinfo