5 Programming with Microsoft Transaction Server and an Oracle Database

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.

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

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, ODP.NET 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 terminate 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 terminate) 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 Oracle database. 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 and Microsoft Transaction Server Function Overview

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).

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().

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 Internet Information Server (IIS) and the components are being called as in-process libraries, then OCIInitialize is already called for you. The registry key ORAMTS_OCI_OBJ_MODE has been added. Set the value to 1 to initialize OCI in Object mode; otherwise OCI will initialize in the threaded mode.

#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( 
"hr","hr_password","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); 
}

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

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).

OraMTSSvcGet() Function

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.

DWORD  OraMTSSvcGet( 

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

);

OraMTSSvcGet(...,ORAMTS_CFLG_NOIMPLICIT)

OraMTSSvcGet("hr", "hr_password", "oracle", &OCISvc, &OCIEnv, ORAMTS_CFLG_ALLDEFAULT | ORAMTS_CFLG_NOIMPLICIT);

OraMTSSvcRel() Function

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

DWORD OraMTSSvcRel(OCISvcCtx* OCISvc);

OraMTSSvcEnlist() Function

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().

DWORD OraMTSSvcEnlist(

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

OraMTSSvcEnlist(OCISvc,OCIErr,NULL,ORAMTS_ENFLG_DEFAULT)

OraMTSSvcEnlistEx() Function

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().

DWORD OraMTSSvcEnlistEx(
                     OCISvcCtx* OCISvc, 

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

OraMTSEnlCtxGet() Function

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

DWORD OraMTSEnlCtxGet(

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

OraMTSEnlCtxRel() Function

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

DWORD OraMTSEnlCtxRel(void* pCtxt); 

OraMTSJoinTxn() Function

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

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

OraMTSTransTest() Function

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

BOOL OraMTSTransTest();

OraMTSOCIErrGet() Function

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

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

DWORD dwStat = OraMTSSvcGet("hr", "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);
        }

Setting the Connection Attribute

To use Microsoft Transaction Server with either Oracle ODBC Driver 10.1 or Microsoft 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.

Using Oracle ODBC Driver

The ODBC Driver Manager distributed with ODBC 3.0 is a Resource Dispenser that supports connection pooling. Oracle ODBC Driver release 10.1 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 10.1 with:

• Applications you develop

• The sample banking application that Microsoft provides with Microsoft Transaction Server.

To configure Oracle ODBC Driver:

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 ODBC Driver work with Microsoft sample banking application demo, follow Substeps 4` through 4d. Otherwise, go to Step 5.

   1. Back up Microsoft 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 the Microsoft 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 in HOME_NAME.

7. Click Advanced.

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

   SERVER=database_alias
   USERNAME=hr 
   PASSWORD=hr_password
   
   

   The following table describes the keywords and values.

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

   
   
   
   

   Note: Verify that the hr schema contains the account and receipt tables.

   
   

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 sample application	mtssamples.dsn (Microsoft 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 Oracle ODBC Driver

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

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

• Performance benefits

• Other API support of Oracle integration

• Oracle client support

After enabling Microsoft's Oracle ODBC Driver perform these additional steps:

To configure Microsoft's Oracle ODBC Driver:

1. Install Oracle Required Support Files (RSF) release 10.1 and SQL*Net 2.3 or later 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 10.1 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