5
Programming with Microsoft Transaction Server and an Oracle Database Server

This chapter describes how to program with Microsoft Transaction Server and an Oracle database server.

This chapter contains these topics:

• COM Component Integration in a Transaction Overview

• Microsoft Transaction Server Application Development Overview

• OCI Integration with Microsoft Transaction Server Overview

• ODBC Integration with Microsoft Transaction Server Overview

• OO4O Integration with Microsoft Transaction Server Overview

• Oracle Provider for OLE DB Integration with Microsoft Transaction Server Overview

• Other API Integration with Microsoft Transaction Server Overview

COM Component Integration in a Transaction Overview

The focal point of the transaction process is a component of Microsoft Transaction Server called Microsoft Distributed Transaction Coordinator (MS DTC). When a client computer starts a business method on a transactional component, Microsoft Transaction Server begins a transaction coordinated by the MS DTC. The Oracle connection pooling layer enables the database server to act as a resource manager (RM) in the MS DTC-coordinated transaction. Figure 5-1 and Table 5-1 provide an overview of how these and other components perform a transaction.

Figure 5-1 Component Integration in a Transaction

Text description of the illustration ntmts008.gif

Table 5-1 Component Integration in a Transaction

Component	Major Responsibilities
Client computer connection	Activates the application components through a Web browser or through the component object model (COM)/distributed component object model (DCOM)
Transactional application logic COM components	Embed the business logic (If the component is transactional, Microsoft Transaction Server starts a transaction for every method invocation on that component.) Acquire pooled connections to a database server through the Oracle resource dispenser and Oracle Call Interface (OCI), Oracle Open Database Connectivity (ODBC) Driver, Oracle Provider for OLE DB, or Oracle Objects for OLE (OO4O) Decide the outcome of the operation by notifying Microsoft Transaction Server of its decision to commit or abort the changes to all RMs
Oracle ODBC Driver, OO4O, Oracle Provider for OLE DB, and OCI	Obtain a service context to the database server through the OCI connection pooling component Provide connection pooling resources, if necessary (through Oracle Provider for OLE DB or Oracle ODBC Driver). The Oracle ODBC Driver provides pooled ODBC connections. Oracle Provider for OLE DB provides pooled data source objects. OO4O uses the OCI connection pool.
OCI connection pool	Performs the following for transaction components: Enlists the RM (database server) in the component's Microsoft Transaction Server transaction Starts an Oracle global transaction corresponding to the Microsoft Transaction Server transaction of which the component is a part Acts as a resource dispenser to perform client-side connection pooling
Oracle MTS Recovery Service	Recovers Microsoft Transaction Server-related, in-doubt Oracle transactions that originated from the host computer
MS DTC (part of Microsoft Transaction Server)	Commits and aborts transactions using the two-phase commit protocol Monitors transactions that require recovery. Multiple MS DTCs can be involved in a single transaction. When a transactional Microsoft Transaction Server component on computer A invokes another transactional Microsoft Transaction Server component on computer B, a connection is opened between the MS DTC on computer A and the MS DTC on computer B. When the root MS DTC commits or aborts a transaction, it sends the request through all involved MS DTCs. The transaction request is then passed to the OCI connection pooling/Microsoft Transaction Server integration, which sends it to the database server.
Database server	Acts as an RM for Microsoft Transaction Server. This is the database on which the client transaction request is performed.

Microsoft Transaction Server Application Development Overview

Regardless of the application program interface (API) you use, OCI connection pooling is used in nearly all cases to coordinate a transaction. Review the following sections for information about how a transaction is registered and OCI connection pooling coordinates the transaction:

• Microsoft Transaction Server Component Registration Overview

• Microsoft Transaction Server-Coordinated Component Transaction Overview

• MS DTC-Coordinated Component Transaction Overview

  See Also: Microsoft Transaction Server Programmer's Guide in the Microsoft Transaction Server's Help file for an explanation of how to develop application components for Microsoft Transaction Server

Microsoft Transaction Server Component Registration Overview

Application components that run in the Microsoft Transaction Server environment are created as dynamic link libraries (DLLs). Application components are registered with Microsoft Transaction Server using the Microsoft Transaction Server Explorer graphical user interface (GUI) tool. When you register the application component, you mark it as one of the types described in Table 5-2.

Table 5-2 Microsoft Transaction Server Component Registration

Type	The Component...
Requires a transaction	Must run in a transaction. If the transaction does not currently exist, Microsoft Transaction Server automatically creates a new transaction for each method invocation on the component.
Requires a new transaction	Must run within their own transaction. Microsoft Transaction Server automatically creates a new transaction for each method invocation on the component.
Supports transactions	Can run within the client's transaction. When a new component is created, its context inherits the transaction from the context of the invoking client. If the client does not have a transaction, the new context is also created without one.
Does not support transactions	Does not run within a transaction. Each method invocation on the component is performed without a surrounding transaction, regardless of whether the invoking client includes a transaction.

How you register an application component determines if it runs in a Microsoft Transaction Server-coordinated transaction. Table 5-3 describes this process.

Table 5-3 Running Components in a Microsoft Transaction Server Transaction

If the Application Component...	Then...
Runs in a Microsoft Transaction Server-coordinated transaction	OCI connection pooling is always used and Microsoft Transaction Server and its MS DTC component coordinate the creation, startup, management, and commitment phases of the transaction. Microsoft Transaction Server ensures that all changes made by the component are committed if the transaction succeeds, or are aborted if the transaction fails. See Also: "Microsoft Transaction Server-Coordinated Component Transaction Overview"
Does not run in a Microsoft Transaction Server-coordinated transaction	The component runs in a Microsoft Transaction Server environment, but the database servers that it accesses may or may not take part in MS DTC-coordinated transactions. If the transaction is not MS DTC-coordinated, the client application must create, start, manage, and commit the transaction. OCI connection pooling may be used, depending upon the interface accessing the database server (such as Oracle Provider for OLE DB, Oracle ODBC Driver, OO4O, or others). See Also: "MS DTC-Coordinated Component Transaction Overview"

Microsoft Transaction Server-Coordinated Component Transaction Overview

This section describes how OCI connection pooling, Microsoft Transaction Server, and MS DTC operate with application components in a Microsoft Transaction Server-coordinated transaction environment.

1. The client API being used (Oracle ODBC Driver, OCI, OO4O, or Oracle Provider for OLE DB) calls OCI function OraMTSSvcGet() to obtain a service context from the OCI connection pooling component.

2. The OCI connection pooling component enlists the transaction, which is to be coordinated by the MS DTC component of Microsoft Transaction Server.

   These actions return OCI service and environment handles to client applications.

3. The client application:
   1. Performs the database operations.

   2. Calls OCI function OraMTSSvcRel() to release the OCI pooling connection obtained at the beginning of the transaction.

   3. Calls SetComplete (to commit database operations) or SetAbort (to abort database operations) on the Microsoft Transaction Server context object associated with the component.

4. MS DTC performs the two-phase commit protocol to prepare and commit (or abort) the transaction, which notifies the OCI connection pooling component and ends the transaction.

5. OCI connection pooling is notified and performs the necessary steps to complete phase one (the prepare phase) and phase two (the commit or abort phase).

MS DTC-Coordinated Component Transaction Overview

This section describes how OCI connection pooling, Microsoft Transaction Server, and MS DTC operate with application components not running in a Microsoft Transaction Server-coordinated transaction, but using MS DTC.

1. The client application starts an MS DTC transaction and connects to the database server. This is either a:
   • Nonpooled OCI connection obtained through OCI logon calls (for example, OCIServerAttach() and OCISessionBegin()). For these connections, the application calls OraMTSEnlCtxGet() to associate the OCI service context with an Microsoft Transaction Server enlistment context.

   • Connection pool obtained by calling OraMTSSvcGet(..,..,ORAMTS_CFLG_NOIMPLICIT), and not yet released with OraMTSSvcRel()

2. For nonpooled connections, the client application passes in the enlistment context to OraMTSJoinTxn().

3. For pooled connections, the client application passes the OCI service context into OraMTSSvcEnlist().

4. The OCI connection pooling component enlists the connection (pooled or nonpooled) in the transaction coordinated by the MS DTC component of Microsoft Transaction Server.

5. The client application:
   1. Performs database operations.

   2. Calls OraMTSSvcEnlist() with a NULL transaction reference to de-enlist from an MS DTC coordinated transaction. For nonpooled connections, OraMTSTxnJoin() is invoked with a NULL transaction reference to perform the de-enlistment.

   3. Calls OraMTSSvcRel() to release a pooled connection back to the pool. For nonpooled connections, the client calls OraMTSEnlCtxRel() to release the enlistment context and then logs off the database.

   4. Calls the commit or abort method on the MS DTC transaction object (for example, pTransaction->Commit() or pTransaction->Abort()).

6. MS DTC performs the two-phase commit protocol to commit the transaction.

7. OCI connection pooling is notified and performs the necessary steps to complete phase one (the prepare phase) and phase two (the commit or abort phase).

OCI Integration with Microsoft Transaction Server Overview

The following OCI functions enable you to integrate the OCI client application with Microsoft Transaction Server and a database server. Review the following sections for information about this integration:

• OCI and Microsoft Transaction Server Function Overview

• OraMTSSvcGet() Function

• OraMTSSvcRel() Function

• OraMTSSvcEnlist() Function

• OraMTSSvcEnlistEx() Function

• OraMTSEnlCtxGet() Function

• OraMTSEnlCtxRel() Function

• OraMTSJoinTxn() Function

• OraMTSTransTest() Function

• OraMTSOCIErrGet() Function

OCI and Microsoft Transaction Server Function Overview

You must use OCI release 8.1 or higher. OCI releases earlier than 8.1 are not supported.

Caution: As with any C++ Microsoft Transaction Server component, obtain the object context and call SetAbort(), SetComplete(), EnableCommit(), or DisableCommit(), depending on the state of the component's work. Do not make any OCI transaction calls such as OCITransCommit() or OCITransAbort(); this corrupts the data!

The only code change to make is in obtaining and releasing the OCI service context handle. An OCI service context handle and environment handle are acquired when you obtain a pooled OCI connection to the database with the OCI function OraMTSSvcGet(). Include the oramts.h header and link with the oramts.lib library. When you are finished, call OCI function OraMTSSvcRel() to release the service context handle and environment handle. Using OraMTSSvcGet() enables you to receive connection pooling and implicit transaction support (if you registered the application component to run in a Microsoft Transaction Server transaction). See "OraMTSSvcGet() Function" and "OraMTSSvcRel() Function" for more information.

In releases prior to 9.0, OraMTSSvcEnlist() and OraMTSSvcEnlistEx() enlisted nonpooled OCI connections in Microsoft Transaction Server transactions. This is no longer supported. These functions are still available to enlist pooled connections in MS DTC-coordinated transactions. To enlist nonpooled OCI connections in Microsoft Transaction Server-started and MS DTC-coordinated transactions, the client must use OraMTSJoinTxn(). See "OraMTSJoinTxn() Function" for more information.

Ensure that for each process, you call OCIInitialize at least once before executing any other OCI calls. This initializes the OCI process environment. In addition, you must pass it the OCI_THREADED flag. If you are using Microsoft's Internet Information Server (IIS) and the components are being called as in-process libraries, then OCIInitialize is already called for you.

#include <oci.h> 
#include  <oramts.h> 
#include  <xolehlp.h> 
// other MTS relevant includes ... 
 
// prototype for the error handler. 
BOOL Chekerr(sword swOCIStat, OCIError *OCIErrh); 
 
// MTS component method 
HRESULT OCITestMethod() 
{ 
 IObjectContext *pObjectContext = NULL; 
 OCIEnv    *myenvh = NULL; 
 OCISvcCtx *mysvch = NULL; 
 OCIError  *myerrh = NULL; 
 OCIStnt   *mystmh = NULL; 
 DWORD      dwStat; 
 HRESULT    hRes = S_OK; 
 sword      swOCIStat; 
 BOOL       bCommit = FALSE; 
 char      *lpzStmt = "UPDATE EMP SET SAL = SAL + 1000"; 
 
 // Initialize the OCI environment first -- request OCI_THREADED 
 OCIInitialize(OCI_THREADED, (dvoid*)NULL,NULL,NULL,NULL);  
 // attempt to get a connection to the database through the resource dispenser 
 OraMTSSvcGet( 
"scott","tiger","finprod_db",&mysvch, &myenvh, ORAMTS_CFLG_ALLDEFAULT);  
 // validate return status 
 if(dwStat != ORAMTS_ERR_NOERROR) 
 { 
   printf("error: failed to obtain a connection to the database - %ld", 
dwStat); 
   goto cleanup; 
 } 
 // successful logon and enlistment in the MTS transaction. allocate statement 
 // handles and other handles using the OCI environment handle myenvh .... 
 swOCIStat = OCIHandleAlloc(myenvh, (void *)&myerrh,OCI_HTYPE_ERROR, 0 , NULL); 
 if (Checkerr(swOCIStat, myerrh)) goto cleanup; 
 swOCIStat = OCIHandleAlloc(myenvh, (dvoid *)&mystmh,OCI_HTYPE_STMT, 0,NULL); 
 if (Checkerr(swOCIStat, myerrh)) goto cleanup;
 // prepare a DML statement 
 OCIStmtPrepare(mystmh, myerrh, lpzStmt, lstrlen(lpzStmt), OCI_NTV_SYNTAX, 
OCI_DEFAULT) 
 Checkerr(swOCIStat, myerrh);  
 // execute the statement -- ensure that AUTOCOMMIT is not requested. 
 OCIStmtExecute(mysvch, mystmh, myerrh, 1, 0, NULL, NULL, OCI_DEFAULT); 
 if (Checkerr(swOCIStat, myerrh)) goto cleanup;  
 // all's well so far choose to go for a commit 
 bCommit = TRUE;  
cleanup: 
 if (mystmh) OCIHandleFree((void*)mystmh, OCI_HTYPE_STMT); 
 if (myerrh  OCIHandleFree((void*)myerrh, OCI_HTYPE_ERROR); 
 if (mysvch) OraMTSSvcRel(mysvch);  
 if (bCommit)  
     pObjectContext->SetComplete();  
 else 
     pObjectContext->Abort();   
 return(bCommit ? S_OK : E_FAIL); 
}

See Also: The files in the ORACLE_BASE\ORACLE_HOME\oramts\samples\account.vc directory for code samples

Figure 5-2 provides a high-level overview of how to use the OCI functions OraMTSSvcGet(), OraMTSSvcRel(), and OraMTSJoinTxn.

Figure 5-2 Using OCI Functions

Text description of the illustration ntmts005.gif

COM applications not hosted by the Microsoft Transaction Server environment (also known as standalone applications) can also use methods 2, 3, and 4 in Figure 5-2. However, these application types cannot use declarative transactions (through the Microsoft Transaction Server Explorer Microsoft Management Console snap-in).

OraMTSSvcGet() Function

Purpose

OraMTSSvcGet() obtains a pooled connection (also known as an OCI service context) from the OCI connection pool. The pooled connection includes an OCI service context handle and OCI environment handle.

Syntax

DWORD  OraMTSSvcGet( 

                 text*       lpUname,
                 text*       lpPsswd,
                 text*       lpDbnam,
                 OCISvcCtx** pOCISvc,
                 OCIEnv**    pOCIEnv,
                 ub4         dwConFlgs

                   );

Parameters

Table 5-4 describes the OraMTSSvcGet() parameters.

Table 5-4 OraMTSSvcGet() Parameters

Datatype	Parameter	Description
text*	lpUname(IN)	Username for connecting to the database server
text*	lpPsswd(IN)	Password for the username
text*	lpDbnam(IN)	The net service name for connecting to the database (created with Oracle Net Manager or Oracle Net Configuration Assistant)
OCISvcCtx**	pOCISvc(OUT)	Pointer to the OCI service context handle
OCIEnv**	pOCIEnv(OUT)	Pointer to the OCI environment handle
ub4	dwConFlgs(IN)	Connection flags, for which there are the following possible values:
		ORAMTS_CFLG_ALLDEFAULT Obtains a pooled connection and enlists the connection in any Microsoft Transaction Server transaction, if one exists. If the component is nontransactional, no enlistment request is dispensed.
		ORAMTS_CFLG_NOIMPLICIT Obtains a pooled connection, but does not enlist the resource in any Microsoft Transaction Server transaction even if the component is transactional. Use this flag if the component enlists the connection resource later using OraMTSSvcEnlist(). Prior to releasing a connection obtained in this fashion, the client must de-enlist the resource if enlisted.
		ORAMTS_CFLG_UNIQUESRVR Requests a single OCI session for each OCI Server. In this release, multiplexing is not supported. Therefore, this option is always used.
		ORAMTS_CFLG_SYSDBALOGN Use this flag if connecting as SYSDBA.
		ORAMTS_CFLG_SYSOPRLOGN Use this flag if connecting as SYSOPER.
		ORAMTS_CFLG_PRELIMAUTH Use this flag if connecting as the user INTERNAL to pre-Oracle9i databases. The INTERNAL account is no longer valid as of Oracle9i. Instead, log on with a SYSDBA or SYSOPER account using the ORAMTS_CFLG_SYSOPRLOGN or ORAMTS_CFLG_SYSDBALOGN flag.

Returns

Returns ORAMTSERR_NOERROR upon successful acquisition of an OCI pooling connection (OCI service context).

Comments

OraMTSSvcGet() returns a pooled OCI connection to the caller, enabling a database transaction using OCI to begin. Use OraMTSSvcGet() to implicitly enlist the OCI connection in a transaction coordinated by Microsoft Transaction Server. In this type of transaction, Microsoft Transaction Server controls the creation, startup, management, and commitment phases of the transaction through its MS DTC component.

OraMTSSvcGet() also provides connection pooling without enlisting the database server in a Microsoft Transaction Server transaction. This is done by setting OraMTSSvcGet() as follows:

OraMTSSvcGet(...,ORAMTS_CFLG_NOIMPLICIT)

In all cases where OraMTSSvcGet() is used, you must always use OraMTSSvcRel() to release the connection when finished.

Note: Connection pooling is used regardless of whether you enlist or do not enlist the COM component in a transaction.

Use the flags ORAMTS_CFLG_SYSDBALOGN and ORAMTS_CFLG_SYSOPRLOGN when connecting as SYSDBA and SYSOPER, respectively.

To obtain a nonenlisted connection using the scott/tiger account, call OraMTSSvcGet() as follows:

OraMTSSvcGet("SCOTT", "TIGER", "oracle", &OCISvc, &OCIEnv, ORAMTS_CFLG_
ALLDEFAULT | ORAMTS_CFLG_NOIMPLICIT);

OraMTSSvcGet() does not support placing the username (lpUname), password (lpPsswd), and net service name syntax (lpDbname) together in the username argument (for example, scott/tiger@prod_fin). Instead, the caller must fill in lpUname, lpPsswd, and lpDbname separately (as shown in the previous syntax example). Calling OraMTSSvcGet() with the username and password as NULL strings uses external authentication (operating system authentication) for the connection.

OraMTSSvcRel() Function

Purpose

OraMTSSvcRel() releases a pooled OCI connection (OCI service context) back to the connection pool. Use OraMTSSvcRel() to release connections that were acquired with OraMTSSvcGet().

Syntax

DWORD OraMTSSvcRel(OCISvcCtx* OCISvc);

Parameters

Table 5-5 describes the OraMTSSvcRel() parameters.

Table 5-5 OraMTSSvcRel() Parameters

Datatype	Parameter	Description
OCISvcCtx*	OCISvc(IN)	OCI service context for a pooled connection

Returns

Returns ORAMTSERR_NOERROR upon successful release of a pooled OCI connection.

Comments

An OCI pooled connection obtained through a previous call to OraMTSSvcGet() is released back to the connection pool. Once released back to the connection pool, the OCI service context, its environment handle, and all child handles are invalid.

A nontransactional client component must explicitly call OCITransCommit() or OCITransAbort() prior to releasing a connection obtained through OraMTSSvcGet(...,...,ORAMTS_CFLG_ALLDEFAULT) back to the pool. Otherwise, all changes made in that session are rolled back. A transaction component uses the SetComplete or SetAbort methods on its Microsoft Transaction Server object context.

Components that have called OraMTSSvcGet(...,...,ORAMTS_CFLG_NOIMPLICIT) to obtain a connection resource must first de-enlist the resource if enlisted. If the connection was enlisted explicitly, pTransaction->Commit() or pTransaction->Abort() must be called. Otherwise, OCITransCommit() or OCITransAbort() must be called before releasing the connection back to the pool.

OraMTSSvcEnlist() Function

Purpose

OraMTSSvcEnlist() enlists or de-enlists an OCI connection in a transaction coordinated by MS DTC. Use this call to explicitly enlist pooled connections. Nonpooled connections must enlist with OraMTSJoinTxn().

Syntax

DWORD OraMTSSvcEnlist(

                   OCISvcCtx*  OCISvc, 
                   OCIError*   OCIErr, 
                   void*       lpTrans, 
                   unsigned    dwFlags
                  );

Parameters

Table 5-6 describes the OraMTSSvcEnlist() parameters.

Table 5-6 OraMTSSvcEnlist() Parameters

Datatype	Parameter	Description
OCISvcCtx*	OCISvc(IN)	OCI service context for pooled connections obtained by calling OraMTSSvcGet()
OCIError*	OCIErr(IN/OUT)	OCI error handle (ignored)
void*	lpTrans(IN)	Pointer to the MS DTC-controlled transaction in which to enlist. If NULL, the OCI connection is de-enlisted from the MS DTC-controlled transaction.
unsigned	dwFlags(IN)	Flag used for enlisting in a transaction. Use the ORAMTS_ENFLG_DEFAULT value. If enlisting, then start a new Oracle global transaction. If de-enlisting, then detach from any global Oracle transaction and delete the context object if the OCI service context represents a nonpooled connection.

Returns

Returns ORAMTSERR_NOERROR on success.

Comments

Use this call to explicitly enlist or de-enlist a pooled connection. For enlisting and de-enlisting nonpooled connections, use OraMTSSvcRel().

OraMTSSvcEnlist() enlists (or de-enlists) pooled OCI connections obtained previously through OraMTSSvcGet() with the ORAMTS_CFLG_NOIMPLICIT flag and not yet released with OraMTSSvcRel(). The pooled OCI connections must be explicitly enlistable. When the transaction is complete, you must de-enlist OraMTSSvcEnlist(), passing NULL as the transaction pointer as follows:

OraMTSSvcEnlist (OCISvc, OCIErr, NULL, ORAMTS_ENFLG_DEFAULT)

You must use OraMTSSvcRel() to release the connection when done.

Callers must:

1. Allocate a connection.

2. Enlist the connection.

3. Perform work.

4. De-enlist the connection.

5. Release the connection.

6. Attempt to commit or abort.

OraMTSSvcEnlistEx() Function

Purpose

OraMTSSvcEnlistEx() enlists an OCI connection or service context in an MS DTC transaction. Use this call only to explicitly enlist pooled connections. Nonpooled connections must enlist with OraMTSJoinTxn().

Syntax

DWORD OraMTSSvcEnlistEx(

                     OCISvcCtx* OCISvc, 
                     OCIError*  OCIErr, 
                     void*      lpTrans, 
                     unsigned   dwFlags,
                     char*      lpDBName
                  );

Parameters

Table 5-7 describes the OraMTSSvcEnlistEx() parameters.

Table 5-7 OraMTSSvcEnlistEx() Parameters

Datatype	Parameter	Description
OCISvcCtx*	OCISvc	OCI service context for a pooled connection obtained by calling OraMTSSvcGet()
OCIError*	OCIErr	OCI error handle (ignored)
void*	lpTrans	Pointer to an MS DTC-controlled transaction. If NULL, the OCI connection is de-enlisted from the MS DTC-controlled transaction.
unsigned	dwFlags	Enlistment flag, for which there is the ORAMTS_ENFLG_DEFAULT value. If enlisting, then start a new Oracle global transaction. If de-enlisting, then exit any global Oracle transaction and delete the context object if the OCI service context represents a nonpooled connection.
char*	lpDBName	Net service name for connecting to the database (created with Oracle Net Manager or Oracle Net Configuration Assistant)

Returns

Returns ORAMTSERR_ILLEGAL_OPER.

Comments

Use OraMTSSvcEnlistEx() for pooled connections or OraMTSJoinTxn() for nonpooled connections.

OraMTSEnlCtxGet() Function

Purpose

OraMTSEnlCtxGet() creates an enlistment context for a nonpooled OCI connection.

Syntax

DWORD OraMTSEnlCtxGet(

                     text*       lpUname,
                     text*       lpPsswd,
                     text*       lpDbnam,              
                     OCISvcCtx*  pOCISvc,
                     OCIError*   pOCIErr,
                     ub4         dwFlags,
                     void**      pCtxt
                  );

Parameters

Table 5-8 describes the OraMTSEnlCtxGet() parameters.

Table 5-8 OraMTSEnlCtxGet() Parameters

Datatype	Parameter	Description
text*	lpUname (IN)	Username for connecting to the database server
text*	lpPsswd (IN)	Password for connecting to the database server
text*	lpDbnam (IN)	Net service name for connecting to a database server
OCISvcCtx*	pOCISvc (IN)	OCI service context for a nonpooled connection
OCIError*	pOCIErr (IN)	OCI error handle
ub4	dwFlags(IN)	Enlistment flags. The only value currently permitted is 0.
void**	pCtxt (OUT)	Enlistment context to be created

Returns

Returns ORAMTSERR_NOERROR on success.

Comments

This call sets up an enlistment context for a nonpooled connection. This call must be started just after the caller establishes the OCI connection to the database. Once created, this context can be passed into OraMTSJoinTxn() calls. Prior to deleting the OCI connection, OraMTSEnlCtxRel() must be called to delete the enlistment context.

Callers must:

1. Allocate a nonpooled connection through OCI.

2. Create an enlistment context by calling OraMTSEnlCtxGet().

3. Enlist the connection by calling OraMTSJoinTxn().

4. Perform database work.

5. De-enlist the connection by calling OraMTSJoinTxn() with a NULL transaction pointer.

6. Attempt to commit or abort work.

7. Release the enlistment context by calling OraMTSEnlCtxRel().

8. Release the nonpooled OCI connection and delete its associated OCI environment handle.

OraMTSEnlCtxRel() Function

Purpose

OraMTSEnlCtxRel() eliminates a previously set up enlistment context for a nonpooled OCI connection.

Syntax

DWORD OraMTSEnlCtxRel(void* pCtxt); 

Parameters

Table 5-9 describes the OraMTSEnlCtxRel() parameters.

Table 5-9 OraMTSEnlCtxRel() Parameters

Datatype	Parameter	Description
void*	pCtxt (IN)	Enlistment context to eliminate

Returns

Returns ORAMTSERR_NOERROR on success.

Comments

Before dropping a nonpooled OCI connection, a client must call OraMTSEnlCtxRel() to eliminate any enlistment context it may have created for that connection. The enlistment context can maintain OCI handles allocated off the connection's OCI environment handle. This makes it imperative that the environment handle is not deleted for the associated enlistment context.

OraMTSJoinTxn() Function

Purpose

OraMTSJoinTxn() enlists a nonpooled OCI connection in an MS DTC transaction.

Syntax

DWORD OraMTSJoinTxn(void*  pCtxt, void*  pTrans);

Parameters

Table 5-10 describes the OraMTSJoinTxn() parameters.

Table 5-10 OraMTSJoinTxn() Parameters

Datatype	Parameter	Description
void*	pCtx (IN)	Enlistment context for the OCI connection
void*	pTrans (IN)	Reference to the MS DTC transaction object

Returns

Returns ORAMTSERR_NOERROR on success.

Comments

Clients use this call with nonpooled OCI connections to enlist connections in MS DTC-coordinated transactions. The client passes in the wide reference to the enlistment context representing the OCI connection, along with a reference to an MS DTC transaction object. If pTrans is NULL, the OCI connection is de-enlisted from any MS DTC transaction in which it is currently enlisted. You can enlist a previously-enlisted OCI connection in a different MS DTC transaction.

OraMTSTransTest() Function

Purpose

OraMTSTransTest() tests if you are running inside a Microsoft Transaction Server-started transaction.

Syntax

BOOL OraMTSTransTest();

Parameters

None.

Returns

Returns true if running inside a Microsoft Transaction Server transaction. Otherwise, false is returned.

Comments

Microsoft Transaction Server transactional components use OraMTSTransTest() to check if a component is running within the context of a Microsoft Transaction Server transaction. Note that this call can only test Microsoft Transaction Server-started transactions. Transactions started by directly calling the MS DTC are not detected.

OraMTSOCIErrGet() Function

Purpose

OraMTSOCIErrGet() retrieves the OCI error code and message text (if any) from the last OraMTS function operation, typically OraMTSSvcGet() or OraMTSJoinTxn().

Syntax

BOOL OraMTSOCIErrGet(DWORD* dwErr, LPCHAR lpcEMsg, DWORD* lpdLen);

Parameters

Table 5-11 describes the OraMTSOCIErrGet() parameters.

Table 5-11 OraMTSOCIErrGet() Parameters

Datatype	Parameter	Description
DWORD*	dwErr	Error code
LPCHAR	lpcEMsg	Buffer for the error message, if any
DWORD*	lpdLen	Set to the actual number of message bytes

Returns

Returns true if an OCI error is encountered. Otherwise, false is returned. If true is returned and lpcEMsg and lpdLen are valid, and there is a stashed error message, up to lpdLen bytes are copied into lpcEMsg. lpdLen is set to the actual number of message bytes.

Comments

OraMTSOCIErrGet() retrieves the OCI error code and OCI error message text, if any, from the last OraMTSSvc operation on this thread. For example:

DWORD dwStat = OraMTSSvcGet("scott", "invalid_password","fin_prod",
db",&mysvch, &myenvh, ORAMTS_CFLG_ALLDEFAULT);
        if (dwStat != ORAMTS_ERR_NOERROR)
        {
                DWORD   dwOCIErr;
                char    errBuf[MAX_PATH];
                DWORD   errBufLen = sizeof(effBuf);

                if (OraMTSOCIErrGet(&dwOCIErr, &errBuf, &errBufLen))
                        printf("OCIError %d: %s"\n);
        }

ODBC Integration with Microsoft Transaction Server Overview

This section describes how to use Oracle ODBC Driver with Microsoft Transaction Server and a database server. Specific topics discussed are:

• Setting the Connection Attribute

• Using the Oracle ODBC Driver (recommended)

• Using Microsoft's Oracle ODBC Driver

OCI connection pooling operates as described in "Microsoft Transaction Server Application Development Overview", with no changes to OCI code required for ODBC to operate.

Setting the Connection Attribute

To use Microsoft Transaction Server with either Oracle ODBC Driver 9.0 or Microsoft's Oracle ODBC driver, you must set the connection attribute. Use the function SQLSetConnectAttr to call the parameter SQL_ATTR_ENLIST_IN_DTC in the ODBC code. This enables you to receive connection pooling and implicit transaction support. See "Setting Up MTS to Access Oracle" in the Microsoft Transaction Server online Help for instructions.

Using the Oracle ODBC Driver

The ODBC Driver Manager distributed with ODBC 3.0 is a Resource Dispenser that supports connection pooling. (See the Microsoft Transaction Server SDK for information.) Oracle ODBC Driver release 9.0 integrates with the ODBC 3.0 Driver Manager by supporting the SQLSetConnectAttr(...,..., SQL_ATTR_ENLIST_IN_DTC) call to enlist or de-enlist the ODBC connection in or from MS DTC-coordinated transactions.

Use the Oracle ODBC Driver 9.0 with:

• Applications you develop

• The sample banking application that Microsoft provides with Microsoft Transaction Server (See Chapter 4, "Running the Microsoft Application Demo" for more information.)

To configure Oracle ODBC Driver 9.0:

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click ODBC.

   The ODBC Data Source Administrator dialog box appears.

3. Choose the File DSN tab.

4. To make Oracle's ODBC Driver work with Microsoft's sample banking application demo, follow Substeps 4a through 4d. Otherwise, go to Step 5.
   1. Back up Microsoft's mtssamples.dsn file. This file is located in ROOTDRIVE:\program files\common files\odbc\data sources.

   2. Select mtssamples.dsn and click Remove.

   3. Click Yes when prompted.

      This deletes the configuration file that enables the Microsoft Transaction Server sample application demo to use Microsoft's ODBC driver.

   4. Go to step 5.

5. Click Add to create a new File data source name (DSN).

   The Create New Data Source wizard appears.

6. Select Oracle ODBC Driver 9.0.

7. Click Advanced.

8. Add the following information in the keywords and values field:

   SERVER=database_alias
   USERNAME=scott 
   PASSWORD=tiger
   
   

   The following table describes the keywords and values.

   Where...	Is...
   SERVER	The database alias used by the demo to access the database server (mtsdemo)
   USERNAME	scott (database server username for this application)
   PASSWORD	tiger (database server password for username scott, unless you changed it)

9. Click OK.

10. Click Next to continue with the Create New Data Source wizard.

11. Review the following table and enter the name of the file DSN to which to save this connection information:

    If Using Oracle's ODBC For...	Then Enter...
    Microsoft's sample application	mtssamples.dsn (Microsoft's ODBC name). This name must exactly match the name you removed in Substep 4b.
    Applications you develop	Any appropriate name

12. Complete the remaining Create New Data Source wizard pages.

13. Click OK to exit the ODBC Data Source Administrator dialog box.

14. Exit the Control Panel window.

Using Microsoft's Oracle ODBC Driver

If the database server release is 8.0.5 or earlier, you cannot use the integration information described in this guide. However, there is a solution if you use Microsoft's Oracle ODBC driver. No other APIs are supported.

You can use Microsoft's Oracle ODBC Driver included in Windows NT Option Pack 4 to enable applications to interact with Microsoft Transaction Server and a database server. If you use this driver, the rest of the information in this guide does not apply and you do not receive the following:

• Performance benefits

• Other API support of Oracle integration

• Oracle 9.0 client support

See "Setting Up MTS to Access Oracle" in the Microsoft Transaction Server online Help for instructions on enabling Microsoft's Oracle ODBC Driver. After following those instructions, perform these additional steps:

To configure Microsoft's Oracle ODBC Driver:

1. Install Oracle Required Support Files (RSF) release 7.3.4 and SQL*Net 2.3 on the computer where Microsoft's Oracle ODBC Driver is operating.

2. Run the ORACLE_BASE\ORACLE_HOME\oramts\samples\ sql\omtssamp.sql script.

3. Use SQL*Net Easy Config to set up a database alias connection. This is the alias that the mtssamples.dsn file uses.

4. If you installed the release 7.3.4 RSFs in a home with Oracle Net installed, be sure to set the following registry parameter at HKEY_LOCAL_MACHINE\ SOFTWARE\ORACLE:

   ORAOCI = ORA73.DLL
   

OO4O Integration with Microsoft Transaction Server Overview

There are no special requirements for using OO4O. You must use release 8.1 or higher of OO4O. See the OO4O online Help file for additional information about using OO4O with Microsoft Transaction Server.

See Also: "Microsoft Transaction Server Application Development Overview" for information about how connection pooling operates (with no changes required to the OO4O code)

Oracle Provider for OLE DB Integration with Microsoft Transaction Server Overview

See the Oracle Provider for OLE DB Developer's Guide for information about using Oracle Provider for OLE DB with Microsoft Transaction Server.

Other API Integration with Microsoft Transaction Server Overview

Other APIs are not currently supported, unless they use Oracle's ODBC Driver 8.1 or higher, such as ActiveX Data Objects.