5
Programming with Microsoft Transaction Server and an Oracle8 Database

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

Specific topics discussed are:

Component Integration In a Transaction

When a client computer initiates a transaction request, Microsoft Transaction Server enlists the Oracle8 database to act as a resource manager (RM) in the transaction process. The focal point of the transaction process is a component of Microsoft Transaction Server called Microsoft Distributed Transaction Coordinator (MS DTC). The figure and table below provide an overview of how these and other components perform a transaction:

Component Major Responsibilities

Client Computer connection

Activates the application components using a Web browser or some other remote connection.

Application logic components

Embed the business logic. If the component is transactional, Microsoft Transaction Server initiates a transaction.

Acquire pooled connections to an Oracle8 database through the Oracle resource dispenser and OCI, Oracle's 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.

OO4O, ODBC, OCI, and Oracle Provider for OLE DB

Obtain a service context to the Oracle8 database through the OCI connection pooling component.

Provide connection pooling resources, if necessary (ODBC).

OCI Connection pooling

Performs the following for transaction components:

Contacts the Oracle8 database for the location of its Oracle Service for MTS.

Connects and sends a message to the Oracle Service for MTS requesting to enlist the RM (Oracle8 database) in the transaction.

Starts an Oracle global transaction corresponding to the Microsoft Transaction Server transaction of which the component is a part.

and also:

Acts as a resource dispenser to perform client-side connection pooling.

Oracle Service for MTS

Enlists the Oracle8 database in the transaction.

Provides the COM communication interface between Microsoft Transaction Server (and its MS DTC component) and the Oracle8 database. MS DTC commits and aborts transactions through the Oracle Service for MTS.

Provides transaction support in the MS DTC.

Performs recovery of transactions in case of failure (for example, Microsoft Transaction Server goes down, the Oracle8 database goes down, the client application goes down, etc.).

Runs on a Windows NT computer, but can communicate through a Net8 network connection with Oracle8 databases running on different operating systems (such as Windows NT and Solaris).

MS DTC (part of Microsoft Transaction Server)

Commits and aborts transactions using the two-phase commit protocol.

Keeps track of transactions that require recovery.

Multiple MS DTCs can be involved in a single transaction. When the RM (Oracle8 database) is enlisted in a transaction, a connection is opened between the client MS DTC and the RM MS DTC. When the client MS DTC commits or aborts a transaction, it sends the request through all involved RM MS DTCs. The transaction request is then passed to the Oracle Service for MTS, which sends it to the Oracle8 database.

Oracle8 database

Acts as an RM for Microsoft Transaction Server. This is the database on which the client transaction request is performed.

Component	Major Responsibilities
Client Computer connection	Activates the application components using a Web browser or some other remote connection.
Application logic components	Embed the business logic. If the component is transactional, Microsoft Transaction Server initiates a transaction. Acquire pooled connections to an Oracle8 database through the Oracle resource dispenser and OCI, Oracle's 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.
OO4O, ODBC, OCI, and Oracle Provider for OLE DB	Obtain a service context to the Oracle8 database through the OCI connection pooling component. Provide connection pooling resources, if necessary (ODBC).
OCI Connection pooling	Performs the following for transaction components: Contacts the Oracle8 database for the location of its Oracle Service for MTS. Connects and sends a message to the Oracle Service for MTS requesting to enlist the RM (Oracle8 database) in the transaction. Starts an Oracle global transaction corresponding to the Microsoft Transaction Server transaction of which the component is a part. and also: Acts as a resource dispenser to perform client-side connection pooling.
Oracle Service for MTS	Enlists the Oracle8 database in the transaction. Provides the COM communication interface between Microsoft Transaction Server (and its MS DTC component) and the Oracle8 database. MS DTC commits and aborts transactions through the Oracle Service for MTS. Provides transaction support in the MS DTC. Performs recovery of transactions in case of failure (for example, Microsoft Transaction Server goes down, the Oracle8 database goes down, the client application goes down, etc.). Runs on a Windows NT computer, but can communicate through a Net8 network connection with Oracle8 databases running on different operating systems (such as Windows NT and Solaris).
MS DTC (part of Microsoft Transaction Server)	Commits and aborts transactions using the two-phase commit protocol. Keeps track of transactions that require recovery. Multiple MS DTCs can be involved in a single transaction. When the RM (Oracle8 database) is enlisted in a transaction, a connection is opened between the client MS DTC and the RM MS DTC. When the client MS DTC commits or aborts a transaction, it sends the request through all involved RM MS DTCs. The transaction request is then passed to the Oracle Service for MTS, which sends it to the Oracle8 database.
Oracle8 database	Acts as an RM for Microsoft Transaction Server. This is the database on which the client transaction request is performed.

Programming with Microsoft Transaction Server and an Oracle8 Database

See the 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.

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 on how a transaction is registered and OCI connection pooling is used to coordinate your transaction:

Registering Microsoft Transaction Server Components

Application components that run in the Microsoft Transaction Server environment are created as dynamic link libraries (DLLs) that 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 following:

Type The Component...

Requires a transaction

Must execute in a transaction. If the transaction does not currently exist, Microsoft Transaction Server automatically creates a new transaction for the component.

Requires a new transaction

Must execute within their own transaction. Microsoft Transaction Server automatically creates a new transaction for the component.

Supports transactions

Can execute within the client's transaction. When a new component is created, its context inherits the transaction from the context of the 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. The new component is created without a transaction, regardless of whether the client has a transaction.

Type	The Component...
Requires a transaction	Must execute in a transaction. If the transaction does not currently exist, Microsoft Transaction Server automatically creates a new transaction for the component.
Requires a new transaction	Must execute within their own transaction. Microsoft Transaction Server automatically creates a new transaction for the component.
Supports transactions	Can execute within the client's transaction. When a new component is created, its context inherits the transaction from the context of the 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. The new component is created without a transaction, regardless of whether the client has a transaction.

How you register an application component determines if it runs in a Microsoft Transaction Server-coordinated transaction.

If Your 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 section "Components Running in a Microsoft Transaction Server-Coordinated Transaction".
Does not run in a Microsoft Transaction Server-coordinated transaction	The component runs in a Microsoft Transaction Server environment, but its MS DTC component may or may not coordinate communication between the Oracle Service for MTS and the Oracle8 database. If your transaction is not MS DTC-coordinated, your client application must create, start, manage, and commit the transaction. See section "Components Not Running in a Microsoft Transaction Server-Coordinated Transaction, But Using MS DTC". OCI connection pooling may be used, depending upon how you program your client application and the OCI API.

If Your 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 section "Components Running in a Microsoft Transaction Server-Coordinated Transaction".

Does not run in a Microsoft Transaction Server-coordinated transaction

The component runs in a Microsoft Transaction Server environment, but its MS DTC component may or may not coordinate communication between the Oracle Service for MTS and the Oracle8 database. If your transaction is not MS DTC-coordinated, your client application must create, start, manage, and commit the transaction. See section "Components Not Running in a Microsoft Transaction Server-Coordinated Transaction, But Using MS DTC". OCI connection pooling may be used, depending upon how you program your client application and the OCI API.

Components Running in a Microsoft Transaction Server-Coordinated Transaction

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.

The client API being used (ODBC, OCI, OO4O, or Oracle Provider for OLE DB) calls OCI function OraMTSSvcGet() to obtain a service context from the OCI connection pooling component.
The OCI connection pooling component:
1. Contacts the Oracle8 database for the hostname and pipe name of its Oracle Service for MTS.
2. Opens a pipe to the Oracle Service for MTS.
3. Requests to enlist in 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.
The Oracle Service for MTS enlists the Oracle8 database in the MS DTC-coordinated transaction.
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.
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.
The Oracle Service for MTS is notified and performs the necessary steps to complete phase one (the prepare phase).
The Oracle Service for MTS is notified and performs the necessary steps to complete the commit (or abort) phase.

Components Not Running in a Microsoft Transaction Server-Coordinated Transaction, But Using MS DTC

This section describes how OCI connection pooling, Microsoft Transaction Server, and MS DTC operate with application components in this type of environment.

The client application starts an MS DTC-coordinated transaction and connects to the Oracle8 database. OCI function OraMTSSvcEnlist() enlists either a:
- pooled OCI connection obtained previously through OraMTSSvcGet(...,...,ORAMTS_CFLG_NOIMPLICIT) and not yet released with OraMTSSvcRel()
- nonpooled OCI connection obtained through some other method
The client application then calls OraMTSSvcEnlist() or OraMTSSvcEnlistEx(), passing in the MS DTC-coordinated transaction.
The OCI connection pooling component:
1. Contacts the Oracle8 database for the hostname and pipe name of its Oracle Service for MTS.
2. Opens a pipe to the Oracle Service for MTS.
3. Requests to enlist in the transaction, which is to be coordinated by the MS DTC component of Microsoft Transaction Server.
The Oracle Service for MTS enlists the Oracle8 database in the MS DTC-coordinated transaction.
The client application:
1. Performs database operations.
2. Calls OCI function OraMTSSvcEnlist(NULL) regardless of whether pooled or nonpooled OCI connections were used.
3. Calls OCI function OraMTSSvcRel() to release them, if pooled OCI connections originally obtained from OraMTSSvcGet(...,...,ORAMTS_CFLG_NOIMPLICIT) were used.
4. Calls the commit or Abort method on the transaction object returned by MS DTC (for example, pTransaction->Commit() or pTransaction->Abort()).
MS DTC performs the two-phase commit protocol to commit the transaction.
The Oracle Service for MTS is notified and performs the necessary steps to complete phase one (the prepare phase).
The Oracle Service for MTS is notified and performs the necessary steps to complete the commit (or abort) phase.

Integrating OCI with Microsoft Transaction Server

The following OCI functions enable you to integrate your OCI client application with Microsoft Transaction Server and an Oracle8 database. Review the following sections for information on this integration:

OCI Function Overview

You must use OCI version 8.1. OCI releases earlier than 8.1 are not supported.

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

The only change to make in your code is in obtaining and releasing your 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 your service context handle and environment handle. Using OraMTSSvcGet() enables you to receive connection pooling and implicit transaction support (if you registered your application component to run in a Microsoft Transaction Server transaction). See sections "OraMTSSvcGet()" and "OraMTSSvcRel()" for more information.

If you use OraMTSSvcGet() and OraMTSSvcRel(), and you did not register your component to run in a Microsoft Transaction Server transaction, you can still enlist your database in a Microsoft Transaction Server-coordinated transaction. Use OCI function OraMTSSvcEnlist() (for pooled or nonpooled connections) or, for better performance, OraMTSSvcEnlistEx() (for nonpooled connections obtained through standard OCI methods). In these cases, the MS DTC component of Microsoft Transaction Server coordinates the transaction. See sections "OraMTSSvcEnlist()" and "OraMTSSvcEnlistEx()" for more information.

Ensure that on a per process basis, 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 your 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 relevent 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 via 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 the ACCOUNT.VC files in the ORACLE_BASE\ORACLE_HOME\ORAMTS\ SAMPLES\ACCOUNT.VC\RELEASE directory for code samples.

This illustration provides a high-level overview of how to use the OCI functions OraMTSSvcGet(), OraMTSSvcRel(), and OraMTSSvcEnlist().

Non-COM applications (also known as standalone applications) can also use methods 2, 3, and 4 above. However, non-COM applications cannot use the Microsoft Transaction Server Explorer GUI. Method 3 can also use OraMTSSvcEnlistEx() instead of OraMTSSvcEnlist() for better performance.

OraMTSSvcGet()

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

Data Type Parameter Description

text

*lpUname(IN)

User name for connecting to the Oracle8 database

text

*lpPsswd(IN)

Password for the above user name

text

*lpDbnam(IN)

Net service name for connecting to the database (created with Net8 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 MTS transaction, if one exists. If the component is nontransactional, no enlistment request is issued.

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. This flag must be used if the component wants to manually enlist 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 per 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 INTERNAL.

Data Type	Parameter	Description
text	*lpUname(IN)	User name for connecting to the Oracle8 database
text	*lpPsswd(IN)	Password for the above user name
text	*lpDbnam(IN)	Net service name for connecting to the database (created with Net8 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 MTS transaction, if one exists. If the component is nontransactional, no enlistment request is issued.
		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. This flag must be used if the component wants to manually enlist 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 per 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 INTERNAL.

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 if you want 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() can also be used to simply provide connection pooling without enlisting the Oracle8 database in an MTS 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 you are done.

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

Use the flags ORAMTS_CFLG_SYSDBALOGN, ORAMTS_CFLG_SYSOPRLOGN, and ORAMTS_CFLG_PRELIMAUTH when connecting as SYSDBA, SYSOPER, and INTERNAL, respectively. For instance, to obtain an enlisted connection using the INTERNAL account, call OraMTSSvcGet() as follows:

OraMTSSvcGet("INTERNAL", "oracle", "oracle", &OCISvc, &OCIEnv, ORAMTS_CFLG_
ALLDEFAULT | ORAMTS_CFLG_PRELIMAUTH);

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 user name (lpUname), password (lpPsswd), and net service name syntax (lpDbname) together in the user name argument (for example, "SCOTT/TIGER@PROD_FIN"). Instead, the caller must fill in lpUname, lpPsswd, and lpDbname separately (as shown in the two examples above). Calling OraMTSSvcGet() with the user name and password as NULL strings causes external authentication (operating system authentication) to be used for the connection.

OraMTSSvcRel()

Purpose

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

Syntax

DWORD OraMTSSvcRel(OCISvcCtx *OCISvc);

Parameters

Data Type Parameter Description

OCISvcCtx

*OCISvc(IN)

OCI service context for 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 issue 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()

Purpose

OraMTSSvcEnlist() enlists or de-enlists an OCI connection in a transaction coordinated by MS DTC.

Syntax

DWORD OraMTSSvcEnlist(

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

Parameters

Data Type Parameter Description

OCISvcCtx

*OCISvc(IN)

OCI service context for pooled connections obtained by calling OraMTSSvcGet() or a service context obtained through calls to OCI 8.1. The latter have an internal context object created by the resource dispenser when they enlist through OraMTSSvcEnlist().

OCIError

*OCIErr(IN/OUT)

OCI environment handle for errors (ignored if the OCI service context represents a pooled connection that was obtained using OraMTSSvcGet()).

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)

Enlistment flags, for which there are the following possible values:

ORAMTS_ENFLG_DEFAULT

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.

ORAMTS_ENFLG_RESUMTX

Used to re-enlist a temporarily de-enlisted service context by resuming the global Oracle transaction.

ORAMTS_ENFLG_DETCHTX

Used to temporarily de-enlist from a transaction by just detaching from the global Oracle transaction. Wrapping context information for nonpooled OCI service contexts is retained by the resource dispenser.

Data Type	Parameter	Description
OCISvcCtx	*OCISvc(IN)	OCI service context for pooled connections obtained by calling OraMTSSvcGet() or a service context obtained through calls to OCI 8.1. The latter have an internal context object created by the resource dispenser when they enlist through OraMTSSvcEnlist().
OCIError	*OCIErr(IN/OUT)	OCI environment handle for errors (ignored if the OCI service context represents a pooled connection that was obtained using OraMTSSvcGet()).
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)	Enlistment flags, for which there are the following possible values:
		ORAMTS_ENFLG_DEFAULT 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.
		ORAMTS_ENFLG_RESUMTX Used to re-enlist a temporarily de-enlisted service context by resuming the global Oracle transaction.
		ORAMTS_ENFLG_DETCHTX Used to temporarily de-enlist from a transaction by just detaching from the global Oracle transaction. Wrapping context information for nonpooled OCI service contexts is retained by the resource dispenser.

Returns

Returns ORAMTSERR_NOERROR upon successful acquisition of an OCI connection.

Comments

OraMTSSvcEnlist() enlists two types of OCI connections:

pooled OCI connections obtained previously through OraMTSSvcGet() with the ORAMTS_CFLG_NOIMPLICIT flag and not yet released with OraMTSSvcRel()
OCI connections obtained through some other method (standard OCI programming)

With both connection types, your application must manually begin the MS-DTC-coordinated transaction.

For pooled OCI connections, the underlying object must be explicitly enlistable. When the transaction is complete, you must de-enlist OraMTSSvcEnlist(), passing NULL as the transaction pointer as follows:

OraMTSSvcEnlist(OCISvc, OCIenv, NULL, ORAMTS_ENFLG_DEFAULT)

If OraMTSSvcGet() is also involved in obtaining the connection, you must use OraMTSSvcRel() to release the connection when done.

Callers must:

Allocate a connection.
Enlist the connection.
Perform work.
De-enlist the connection.
Release the connection.
Attempt to commit or abort.

For nonpooled OCI connections, the enlistment creates a context wrapper object within the resource dispenser. This has a transaction handle, error handle, and other information pertaining to the enlistment. The transaction handle must be undisturbed until the service context is finally disposed. Once a nonpooled OCI connection has been enlisted, it can be detached and attached to the underlying Oracle transaction through the same call using the dwFlags parameter. To detach from the Oracle transaction, set lpTrans to NULL and dwFlags to ORAMTS_ENFLG_DETCHTX. To resume the current transaction, lpTrans is not set to NULL and dwFlags is set to ORAMTS_ENFLG_RESUMTX.

OraMTSSvcEnlistEx()

Purpose

OraMTSSvcEnlistEx() enlists an OCI connection or service context in an MS DTC transaction.

Syntax

DWORD OraMTSSvcEnlistEx(

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


                                );

Parameters

Data Type Parameter Description

OCISvcCtx

*OCISvc

OCI service context for a pooled connection

OCIError

*OCIErr

OCI error handle for errors. This is ignored if the service context represents a pooled connection

void

*lpTrans

Pointer to an MS DTC-controlled transaction

unsigned

dwFlags

Enlistment flags

char

*lpDBName

Net service name for connecting to the database (created with Net8 Assistant)

Returns

Returns ORAMTSERR_NOERROR on success.

Comments

This call is identical to OraMTSSvcEnlist() except for the addition of an lpDBName parameter. The lpDBName parameter is only used when enlisting nonpooled connections. The parameter is used to cache information to improve enlistment performance (regarding the Oracle Service for MTS for the Oracle8 database). This parameter is ignored for pooled connections and also for de-enlistment requests.

OraMTSTransTest()

Purpose

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

Syntax

BOOL OraMTSTransTest();

Parameters

None.

Returns

Returns TRUE if running inside a Microsoft Transaction Server transaction. Otherwise, FALSE is returned.

Comments

This can be used by Microsoft Transaction Server transactional components to check if a component is executing within the context of a Microsoft Transaction Server transaction. Note that this call can only test Microsoft Transaction Server-initiated transactions. Transactions started by directly calling the MS DTC are not detected.

OraMTSOCIErrGet()

Purpose

OraMTSOCIErrGet() retrieves the OCI error code and message text (if any) from the last OraMTSSvc operation (typically OraMTSSvcGet(), OraMTSSvcEnlist(), or OraMTSSvcEnlistEx()).

Syntax

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

Parameters

Data Type Parameter Description

DWORD

*dwErr

(OUT) error code

LPCHAR

lpcEMsg

(OUT) buffer for the error message if any

DWORD

*lpdLen

(IN/OUT) size of lpcEmsg in; msg bytes out

Data Type	Parameter	Description
DWORD	*dwErr	(OUT) error code
LPCHAR	lpcEMsg	(OUT) buffer for the error message if any
DWORD	*lpdLen	(IN/OUT) size of lpcEmsg in; msg bytes out

Returns

Returns TRUE if an OCI error was encountered. Otherwise, returns FALSE. If TRUE 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

This function 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);
        }

Integrating ODBC with Microsoft Transaction Server

This section describes how to use ODBC with Microsoft Transaction Server and an Oracle8 database. Specific topics discussed are:

Setting the Connection Attribute
Using Oracle's ODBC Driver (recommended)
Using Microsoft's Oracle ODBC Driver

OCI connection pooling operates as described in section "Programming with Microsoft Transaction Server and an Oracle8 Database", with no changes to OCI code required for ODBC to operate.

Setting the Connection Attribute

To use Microsoft Transaction Server with either Oracle's ODBC Driver 8.1 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 your 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 Oracle's 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's ODBC driver release 8.1 integrates with the ODBC 3.0 Driver Manager by supporting the SQLSetConnectAttr(...,..., SQL_ATTR_ENLIST_IN_DTC) call to enlist/de-enlist the ODBC connection in/from MS DTC-coordinated transactions.

You must use release 8.1 of Oracle's ODBC driver. Previous versions do not work with Microsoft Transaction Server. Use Oracle's ODBC Driver 8.1 with:

Your own applications
The sample banking application that Microsoft provides with Microsoft Transaction Server (see Chapter 4, "Using the Microsoft Application Demo")

To configure Oracle's ODBC Driver 8.1:

Choose Start > Settings > Control Panel.

The Control Panel window appears.
Double-click ODBC.

The ODBC Data Source Administrator dialog box appears.
Choose the File DSN tab.
If you want Oracle's ODBC Driver to work with Microsoft's sample banking application demo, follow substeps a through d. 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.
Click Add to create a new File data source name (DSN).

The Create New Data Source wizard appears.
Select Oracle ODBC Driver 8.1.
Click Advanced.

Add the following information in the keywords and values field:

SERVER=DATABASE_ALIAS
USERNAME=SCOTT 
PASSWORD=TIGER

Where... Is...

SERVER

the database alias used by the Oracle Service for MTS to access the Oracle8 database

USERNAME

SCOTT (Oracle8 database user name for this application)

PASSWORD

TIGER (Oracle8 database password for user name SCOTT)

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

Where...	Is...
SERVER	the database alias used by the Oracle Service for MTS to access the Oracle8 database
USERNAME	SCOTT (Oracle8 database user name for this application)
PASSWORD	TIGER (Oracle8 database password for user name SCOTT)

Enter the name of the file DSN to which you want 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 4 b.

Your own applications

Any appropriate name.

Complete the remaining Create New Data Source wizard pages.
Click OK to exit the ODBC Data Source Administrator dialog box.
Exit the Control Panel window.

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 4 b.
Your own applications	Any appropriate name.

Using Microsoft's Oracle ODBC Driver

If the Oracle8 database version is 8.0.5 or earlier, you cannot use the integration 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 an Oracle8 database. If you use this driver, the rest of the information in this guide does not apply and you do not receive the performance benefits, other API support of Oracle integration, or Oracle 8.1 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:

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.
Run the ORACLE_BASE\ORACLE_HOME\ORAMTS\SAMPLES\ ACCOUNT.VC\OMTSSAMP.SQL script.
Use SQL*Net Easy Config to set up a database alias connection. This is the alias that the MTSSAMPLES.DSN file uses.
If you installed the release 7.3.4 RSFs in a home that has Net8 installed, be sure to set the following registry parameter at HKEY_LOCAL_MACHINE\ SOFTWARE\ORACLE:
```
ORAOCI = ORA73.DLL
```

Integrating Oracle Objects for OLE with Microsoft Transaction Server

There are no special requirements for using Oracle Objects for OLE. You must use version 8.1 of Oracle Objects for OLE. See the Oracle Objects for OLE online Help file for additional information on using Oracle Objects for OLE with Microsoft Transaction Server.

Connection pooling operates as described in section "Programming with Microsoft Transaction Server and an Oracle8 Database", with no changes required to the Oracle Objects for OLE code.

Integrating Oracle Provider for OLE DB with Microsoft Transaction Server

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

Integrating Other APIs with Microsoft Transaction Server

Currently, other APIs are not supported, unless they use Oracle's ODBC Driver 8.1, such as ADO.

5 Programming with Microsoft Transaction Server and an Oracle8 Database

Component Integration In a Transaction

Programming with Microsoft Transaction Server and an Oracle8 Database

Registering Microsoft Transaction Server Components

Components Running in a Microsoft Transaction Server-Coordinated Transaction

Components Not Running in a Microsoft Transaction Server-Coordinated Transaction, But Using MS DTC

Integrating OCI with Microsoft Transaction Server

OCI Function Overview

OraMTSSvcGet()

Purpose

Syntax

Parameters

Returns

Comments

OraMTSSvcRel()

Purpose

Syntax

Parameters

Returns

Comments

OraMTSSvcEnlist()

Purpose

Syntax

Parameters

Returns

Comments

OraMTSSvcEnlistEx()

Purpose

Syntax

Parameters

Returns

Comments

OraMTSTransTest()

Purpose

Syntax

Parameters

Returns

Comments

OraMTSOCIErrGet()

Purpose

Syntax

Parameters

Returns

Comments

Integrating ODBC with Microsoft Transaction Server

Setting the Connection Attribute

Using Oracle's ODBC Driver

Using Microsoft's Oracle ODBC Driver

Integrating Oracle Objects for OLE with Microsoft Transaction Server

Integrating Oracle Provider for OLE DB with Microsoft Transaction Server

Integrating Other APIs with Microsoft Transaction Server

5
Programming with Microsoft Transaction Server and an Oracle8 Database