Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Enlisting Resources

The databases that the object accesses must be enlisted to be included within the global transaction.

Note: At this time, the Oracle JTA implementation does not support including non-Oracle databases in a global transaction.

Before the database can be enlisted, you must do the following:

1. If using two-phase commit, create a database link from the two-phase commit engine to the intended databases. See "Configuring Two-Phase Commit Engine" for more information.

2. Bind the `UserTransaction` object within the namespace.

3. Bind a JTA `DataSource` object within the namespace for each desired database.

Once this is completed, Oracle8i JTA will automatically enlist your database resource when you retrieve the JDBC connection to the database. Retrieve the JTA `DataSource` object that you bound in the namespace. Create the JDBC connection through the `DataSource.getConnection` method.

Note: All database resources are automatically unenlisted when the transaction is committed. However, the connection must still be closed at the end of the transaction.

These subjects are discussed in the following sections:

- Bind DataSource Object in Namespace

- Bind UserTransaction Object in Namespace

- Server Enlists the Local Database in Transaction

- JTA Client-Side Database Enlistment

- JTA Server-Side Database Enlistment

### Bind DataSource Object in Namespace

Use the `bindds` command of the `sess_sh` tool to bind an `DataSource` object in the namespace. For the database to be included in the transaction, you must bind the `DataSource` object with the `-jta` option. The full command is detailed in the Oracle8i Java Tools Reference.

To bind a `DataSource` object for a single-phase commit transaction with the `empHost` database to the name "`/test/empDatabase`" in the namespace located on `nsHost`, execute the following:

    bindds /test/empDatabase -url jdbc:oracle:thin:@empHost:1521:ORCL

    	-dstype jta

After binding the `DataSource` object in the namespace, the server can enlist the database within a global transaction.

To bind the same object for a two-phase commit transaction, you must supply the database link created from the two-phase commit engine to the specified database, as follows:

    bindds /test/empDatabase -url jdbc:oracle:thin:@empHost:1521:ORCL

    	-dstype jta -dblink 2pcToEmp.oracle.com

### Bind UserTransaction Object in Namespace

You bind the `UserTransaction` object in the namespace through the `bindut` command of the `sess_sh` tool. See the Oracle8i Java Tools Reference for a full description of this tool. To bind a `UserTransaction` object to the name "`/test/myUT`" in the namespace located on `nsHost`, execute the following:

    bindut /test/myUT -expprop

Note: The -`expprop` option is used to denote that this is a JDBC-only JTA transaction.

Verify that the user bound with the `UserTransaction` has FORCE ANY TRANSACTION granted to the user that bound this object. This privilege enables the user to commit this transaction. In this example, you would execute the following:

    GRANT FORCE ANY TRANSACTION TO SCOTT

Note: The client needs the same information to retrieve the `UserTransaction` as you give within the `bindut` command.

The client needs the same information to retrieve the `UserTransaction` as you give within the `bindut` command.

To bind the same object for a two-phase commit transaction, you must supply the URL location for the two-phase commit engine, username, and password, as follows:

    bindut /test/myUT -expprop -url jdbc:oracle:thin:@2pcHost:1521:ORC	L

    	-user SCOTT -password TIGER

### JTA Client-Side Database Enlistment

You do the following for a client to demarcate the transaction and enlist a database within the transaction:

1. The system administrator binds the required JTA objects in the namespace.

   1. Bind the `UserTransaction` object within the namespace.

   2. Bind the `DataSource` object within the namespace.

      Note: If using more than one database, you will need to setup for a two-phase commit. See "Configuring Two-Phase Commit Engine" for more information.

2. Initialize a Hashtable environment with the namespace address and authentication information.

3. Register the `OracleDriver`.

4. Retrieve the `UserTransaction` object from the namespace within the client logic. When you retrieve the `UserTransaction` object remotely, as you are from any client, the URL must consist of "`jdbc_access://`" prefix before the JNDI name.

5. Start the global transaction within the client using `UserTransaction.begin()`.

6. Enlist any database resources to be included in the transaction by opening a JDBC connection to the specified database, as follows:

   1. Retrieve the `DataSource` object from the namespace within the client logic. When you retrieve the `DataSource` object remotely, as you are from any client, the URL must consist of "`jdbc_access://`" prefix before the JNDI name.

   2. Open a connection to the database through `DataSource.getConnection`. If the username and password given within the environment is the correct authentication information, provide no arguments. However, if the authentication requires a different username and password, provide these as input parameters to the `getConnection` method call.

7. Invoke SQL DML statements, such as `insert` or `update`, against any enlisted databases.

8. Close any open database connections.

9. End the transaction through `UserTransaction.commit()` or `UserTransaction.rollback()`.

#### Example 17-1 Client Enlists a Remote Database in Transaction

The following example shows a client that starts a transaction, enlists a remote database, performs SQL against the database, closes the database JDBC connection, and commits the transaction. Since the client is retrieving both the `UserTransaction` and `DataSource` objects from a remote site, the lookup requires authentication information, location of the namespace, the Oracle JDBC driver registration, and the "`jdbc_access://`" prefix.

    import employee.*;
    import java.sql.DriverManager;
    import java.util.Hashtable;
    import javax.naming.Context;
    import javax.naming.InitialContext;
    import javax.transaction.UserTransaction;
    import java.sql.SQLException;
    import javax.naming.NamingException;
    import javax.sql.DataSource;
    import java.sql.Connection;
    import oracle.aurora.jndi.jdbc_access.jdbc_accessURLContextFactory;

    public class Client
    {
      public static void main (String[] args) throws Exception
       //Set up the service URL to where the UserTransaction object
       //is bound. Since from the client, the connection to the database
       //where the namespace is located can be communicated with over either
       //a Thin or OCI8 JDBC driver. This example uses a Thin JDBC driver.
       String namespaceURL = "jdbc:oracle:thin:@nsHost:1521:ORCL";

       // lookup usertransaction object in the namespace
       try {
        //1. Authenticate to the database.
        // create InitialContext and initialize for authenticating client
        Hashtable env = new Hashtable ();
        env.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi");
        env.put (Context.SECURITY_PRINCIPAL, "SCOTT");
        env.put (Context.SECURITY_CREDENTIALS, "TIGER");
        //2. Specify the location of the namespace where the transaction objects
        //   are bound.
        env.put(jdbc_accessURLContextFactory.CONNECTION_URL_PROP, namespaceURL);
        Context ic = new InitialContext (env);

        //3. Register a JDBC OracleDriver. This is a requirement for using
        //   an Oracle JDBC driver.
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

        //4. Retrieve the UserTransaction object from JNDI namespace
        ut = (UserTransaction)ic.lookup ("jdbc_access://test/myUT");

        //5. Start the transaction
        ut.begin();

        //6. Retrieve the DataSource (that was previously bound with bindds in
        // the namespace. After retrieving the DataSource...
        // get a connection to a database.
        DataSource ds = (DataSource)ic.lookup ("jdbc_access://test/empDB");

        // get connection to the database through DataSource.getConnection
        // in this case, the database requires the same username and password as
        // set in the environment.
        Connection conn = ds.getConnection ();

        //7. Execute SQL statements against the enlisted database.
        Statement stmt = conn.createStatement ();
        int cnt = stmt.executeUpdate ("insert into my_tab values (39304)");

        //8. Close the database connection.
        conn.close ();

        //9. End the transaction
        //Commit the updated value
        ut.commit ();
    }

#### Example 17-2 Providing Username and Password to DataSource.getConnection

If the database that you are retrieving the connection for requires a different password than what was setup in the environment, execute the `getConnection` method with the require username and password, as follows:

    Connection conn = ds.getConnection (username, password);

### JTA Server-Side Database Enlistment

If the database where the server is located also contains the namespace, you can do an in-session lookup. That is, you do not need to provide any environment setup. However, if the server is located on a separate machine than the namespace, you must initialize the environment exactly as specified in "JTA Client-Side Database Enlistment". This section assumes that the namespace and the server object exist on the same machine and demonstrates an in-session lookup of the `UserTransaction` and `DataSource` objects.

You do the following for a server to demarcate the transaction and enlist a database within the transaction:

1. The system administrator binds the required JTA objects in the namespace.

   1. Bind the `UserTransaction` object within the namespace.

   2. Bind the `DataSource` object within the namespace.

      Note: If using more than one database, you will need to setup for a two-phase commit. See "Configuring Two-Phase Commit Engine" for more information.

2. Retrieve the `UserTransaction` object from the namespace using in-session activate. No environment is required and no URL prefix is required. The lookup requires only the JNDI name of the bound object.

3. Start the global transaction within the client using `UserTransaction.begin()`.

4. Enlist any database resources to be included in the transaction by opening a JDBC connection to the specified database, as follows:

   1. Retrieve the `DataSource` object from the namespace using in-session activate. No URL prefix is required. The lookup requires only the JNDI name of the bound object.

   2. Open a connection to the database through `DataSource.getConnection`. If the username and password that was used to authenticate to this server is the correct authentication information, provide no arguments. However, if the authentication requires a different username and password, provide these as input parameters to the `getConnection` method call.

5. Invoke SQL DML statements against any enlisted databases.

6. Close any open database connections.

7. End the transaction through `UserTransaction.commit()` or `UserTransaction.rollback()`.

#### Example 17-3 Server Enlists the Local Database in Transaction

The following example shows how a server object can enlist the local database in the transaction. The global transaction is initialized, the connection is retrieved through the `DataSource.getConnection` method, and statements are executed against the local database. These statements are committed when the global transaction is committed.

Since the `UserTransaction` and `DataSource` objects are bound in the namespace local to this host, you can perform an in-session activation. This means that no environment information is necessary (unless performing a two-phase commit scenario) and the lookup uses only the JNDI name, without the "`jdbc_access://`" prefix.

    public EmpRecord query (int empNumber) throws SQLException, RemoteException
      {
       //Retrieving the UserTransaction and DataSource using in-session activation.
       //no authentication info, no namespace URL, and no OracleDriver registration
       //needed.
        Context ic = new InitialContext ( );

        // Retrieve the UserTransaction object from JNDI namespace using JNDI name
        // only
        UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT");

        // Start the transaction
        ut.begin();

        //Retrieve the DataSource using in-session activation using JNDI name only
        DataSource ds = (DataSource) ic.lookup("/test/myDB");

        //Retrieve the default connection object to the local database. Since
        // username and password used to authenticate to this server are correct,
        // no parameters are required.
        Connection conn = ds.getConnection ();

        //prepare and execute a sql statement against the local database.
        PreparedStatement ps =
          conn.prepareStatement ("insert into empNumber values (39304)");
        try {
          ps.setInt (1, empNumber);
          ResultSet rset = ps.executeQuery ();
          if (!rset.next ())
            throw new RemoteException ("no employee with ID " + empNumber);
          return new EmpRecord (rset.getString (1), empNumber, rset.getFloat (2));
        } finally {
          ps.close();
        }
        //close the connection and commit the transaction
        conn.close();
        ut.commit();
      }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index