Database Access Interfaces with Microsoft Transaction Server

Microsoft Corporation

June 1997

Abstract

This article describes the database-access interface options for Microsoft® Transaction Server (MTS) applications.

Using Open Database Connectivity

You can use the Open Database Connectivity (ODBC) application programming interface (API) to access a resource manager (which is a system service that manages durable data), or a data-access model that functions over the ODBC layer. Because the ODBC version 3.0 Driver Manager is an MTS resource dispenser, data accessed via ODBC is automatically protected by your object's transaction. To handle object transactions, an ODBC-compliant database must support the following requirements:

The database's ODBC driver must be thread safe and must operate without thread affinity.
If ODBC is used from within a transactional component, then the ODBC driver must also support the SQL_ATTR_ENLIST_IN_DTC connection attribute. This is how the ODBC Driver Manager asks the ODBC driver to enlist a connection on a transaction. You can make your component transactional by setting the transaction property for your component in the MTS Explorer. If you are using a database that does not have a resource dispenser that can recognize MTS transactions, contact your database vendor to obtain the required support.

Database Requirements for Full MTS Support

Requirements	Description	Resources (if applicable)
Support for the OLE transactions specification, or support for XA protocol	Enables direct database interaction with Distributed Transaction Coordinator (DTC). Use the XA Mapper to interact with DTC.	MTS Beta SDK
ODBC driver	Platform requirement for MTS server components	ODBC version 3.0 SDK
ODBC driver support for the ODBC version 3.0 SetConnectAttr SQL_ATTR_ENLIST_ IN_DTC call.	MTS uses this call to pass the transaction identifier to the ODBC driver. The ODBC driver then passes the transaction identifier to the database engine.	ODBC version 3.0 SDK
Fully thread-safe ODBC driver	ODBC driver must be able to handle concurrent calls from any thread at any time.	ODBC version 3.0 SDK
ODBC driver must not require thread affinity	ODBC driver must be able to connect to the driver from one thread, use the connection from another thread, and disconnect from another thread.	ODBC version 3.0 SDK

If a memory access violation in the mtx.exe process occurs within the driver after 60 seconds of inactivity, you may be using an ODBC driver that is not thread-safe or requires thread affinity. The fault occurs in the driver when the inactive connections are being disconnected.

Microsoft Transaction Server Distributed Transaction Coordinator

MTS uses the services of Microsoft Distributed Transaction Coordinator (MS DTC) for transaction coordination. MS DTC is a system service that coordinates transactions that span multiple resource managers. Work can be committed as a single transaction, even if it spans multiple resource managers, potentially on separate computers. MS DTC was initially released as part of Microsoft SQL Server™ version 6.5, and is included as part of MTS. DTC implements a two-phase commit protocol that ensures that the transaction outcome (either commit or abort) is consistent across all resource managers involved in a transaction. MS DTC supports resource managers that implement OLE Transactions, X/Open XA protocols, and LU 6.2 Sync Level 2.

Choosing Your Data-Access Model

You can use a variety of different data-access models with your MTS application. The following table summarizes commonly used data-access models supported by MTS.

Interface	Description
Microsoft ActiveX™ Data Objects (ADO), Advanced Data Connector (ADC)	ADO offers one common yet extensible programming model for accessing data. ADO includes the ability to pass query results (Recordsets) between server and client, and the ability to pass updated Recordsets from client to server (using ADC).
OLE DB	OLE DB is a low-level interface that provides uniform access to any tabular data source. You cannot call OLE DB interfaces directly from Microsoft Visual Basic® because OLE DB is a pointer-based interface. A Visual Basic client can access an OLE DB data source through ADO.
Open DataBase Connectivity (ODBC)	ODBC is a recognized standard interface to relational data sources. ODBC is fast and provides a universal interface that is not optimized for any specific data source.
Remote Data Objects (RDO)	RDO is a thin object layer interface to the ODBC API. It is specifically designed to access remote ODBC relational data sources.

The following diagram illustrates how MTS components interact with the different data-access interfaces:

ADO is not specifically designed for relational or ISAM databases, but as an object interface to any data source. ADO can access relational databases, ISAM, text, hierarchical, or any type of data source, as long as a data-access provider exists. ADO is built around a set of "core" functions that all data sources are expected to implement. ADO is implemented to consume native OLE DB data sources, including a specific OLE DB provider that provides access to ODBC drivers. ADO ships with the OLE DB Software Development Kit (SDK).

RDO 2.0 does have some functionality that is not currently implemented in ADO 1.0, such as the following features:

Events on the Engine, Connection, Resultset, and Column objects
Asynchronous operations
Queries as methods
Enhanced batch-mode error and contingency handling
Tight integration with Visual Basic, as in the Query Connection designer and TSQL debugger.

Future versions of ADO will provide a superset of RDO version 2.0 functionality and provide a far more sophisticated interface, in addition to an easier programming model. Because ADO is an Automation-based component, any application or language capable of working with Automation objects can use it.