Oracle9i, Java and XAResource

From: Thomas Lang <thlang_at_gmx.at>
Date: Wed, 16 Apr 2003 14:32:18 +0200
Message-ID: <3E9D4D52.4080802@gmx.at>

I'm useing Oracke 9i and JAVA 1.4.0_01.

For my diplomathesis I need to create a system with a distributed database. To serve this problem I'm using the XAResources to control the consistence at all sites with the twophasecommit-protocoll.

Now my question:
The code below is the same like the tutorialprogramm from oracle, i've only modified the adresses. - I'm using the same database with the same user but with different tables.

When I'm inserting new values in different tables ////
start
insert into my_table_a values (4321)
insert into my_tab_b values ('test')
end
int prp1 = oxar1.prepare (xid1);
int prp2 = oxar2.prepare (xid2);
////

prp1 is READONLY and
prp2 is OK.

Why?
I have no idea why this problem occured. in my oppinion prp1 should be also OK. I'm right, or is there a mistake?

The second problem is, when oxar2.commit is performed both tables are commited.
Is it possible only at one specific table to commit not on the whole database?

Can someone help me to resolve my problems.

Thomas

// You need to import the java.sql package to use JDBC import java.sql.*;
import javax.sql.*;

import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;

import javax.transaction.xa.*;

class db3
{

   public static void main (String args [])

        throws SQLException
   {

     try
     {
         String URL1 ="jdbc:oracle:thin:@database1";
         String URL2 ="jdbc:oracle:thin:@database1";

         DriverManager.registerDriver(new OracleDriver());



         // You can put a database name after the @ sign in the 

connection URL.
         Connection conna =
           DriverManager.getConnection (URL1, "USER1", "PW1");



         // Prepare a statement to create the table

         Statement stmta = conna.createStatement ();

         Connection connb =
           DriverManager.getConnection (URL2, "USER1", "PW1");



         // Prepare a statement to create the table

         Statement stmtb = connb.createStatement ();

         try
         {
           // Drop the test table
           stmta.execute ("drop table my_table_a");
         }
         catch (SQLException e)
         {
           // Ignore an error here
         }

         try
         {
           // Create a test table
           stmta.execute ("create table my_table_a (col1 int)");
         }
         catch (SQLException e)
         {
           // Ignore an error here too
         }

         try
         {
           // Drop the test table
           stmtb.execute ("drop table my_tab_b");
         }
         catch (SQLException e)
         {
           // Ignore an error here
         }

         try
         {
           // Create a test table
           stmtb.execute ("create table my_tab_b (col1 char(30))");
         }
         catch (SQLException e)
         {
           // Ignore an error here too
         }



         // Create a XADataSource instance

         OracleXADataSource oxds1 = new OracleXADataSource();
         oxds1.setURL("jdbc:oracle:thin:@database1");
         oxds1.setUser("USER1");
         oxds1.setPassword("PW1");

         OracleXADataSource oxds2 = new OracleXADataSource();

         oxds2.setURL("jdbc:oracle:thin:@database1");
         oxds2.setUser("USER1");
         oxds2.setPassword("PW1");



         // Get a XA connection to the underlying data source

         XAConnection pc1  = oxds1.getXAConnection();



         // We can use the same data source

         XAConnection pc2  = oxds2.getXAConnection();



         // Get the Physical Connections

         Connection conn1 = pc1.getConnection();
         Connection conn2 = pc2.getConnection();



         // Get the XA Resources

         XAResource oxar1 = pc1.getXAResource();
         XAResource oxar2 = pc2.getXAResource();



         // Create the Xids With the Same Global Ids

         Xid xid1 = createXid(1);
         Xid xid2 = createXid(2);



         // Start the Resources

         oxar1.start (xid1, XAResource.TMNOFLAGS);
         oxar2.start (xid2, XAResource.TMNOFLAGS);



         // Do  something with conn1 and conn2

         doSomeWork1 (conn1);
         doSomeWork2 (conn2);



         // END both the branches -- THIS IS MUST

         oxar1.end(xid1, XAResource.TMSUCCESS);
         oxar2.end(xid2, XAResource.TMSUCCESS);



         // Prepare the RMs

         int prp1 =  oxar1.prepare (xid1);
         int prp2 =  oxar2.prepare (xid2);

         System.out.println("Return value of prepare 1 is " + prp1);
         System.out.println("Return value of prepare 2 is " + prp2);

         boolean do_commit = true;

         if (!((prp1 == XAResource.XA_OK) || (prp1 == 
XAResource.XA_RDONLY)))
            do_commit = false;

         if (!((prp2 == XAResource.XA_OK) || (prp2 == 
XAResource.XA_RDONLY)))
            do_commit = false;

        System.out.println("do_commit is " + do_commit);
         System.out.println("Is oxar1 same as oxar2 ? " + 

oxar1.isSameRM(oxar2));

         if (prp1 == XAResource.XA_OK)
           if (do_commit)
             {
              System.out.println("1 commit");
              oxar1.commit (xid1, false);
             }
           else
             {
              System.out.println("1 rollback");
              oxar1.rollback (xid1);
             }

         if (prp2 == XAResource.XA_OK)
           if (do_commit)
             {
              System.out.println("2 commit");
              oxar2.commit (xid2, false);
             }
           else
             {
              System.out.println("2 rollback");
              oxar2.rollback (xid2);
             }

          // Close connections
         conn1.close();
         conn1 = null;
         conn2.close();
         conn2 = null;

         pc1.close();
         pc1 = null;
         pc2.close();
         pc2 = null;

         ResultSet rset = stmta.executeQuery ("select col1 from 
my_table_a");
         while (rset.next())
           System.out.println("Col1 Table A is " + rset.getInt(1));

         rset.close();
         rset = null;

         rset = stmtb.executeQuery ("select col1 from my_tab_b");
         while (rset.next())
           System.out.println("Col1 Table B is " + rset.getString(1));

         rset.close();
         rset = null;

         stmta.close();
         stmta = null;
         stmtb.close();
         stmtb = null;

         conna.close();
         conna = null;
         connb.close();
         connb = null;

     } catch (SQLException sqe)
     {
       sqe.printStackTrace();
     } catch (XAException xae)
     {
       if (xae instanceof OracleXAException) {
         System.out.println("XA Error is " +
                       ((OracleXAException)xae).getXAError());
         System.out.println("SQL Error is " +
                       ((OracleXAException)xae).getOracleError());
       }
     }

   }
   static Xid createXid(int bids)
     throws XAException
   {

     byte[] gid = new byte[1]; gid[0]= (byte) 9;
     byte[] bid = new byte[1]; bid[0]= (byte) bids;
     byte[] gtrid = new byte[64];
     byte[] bqual = new byte[64];
     System.arraycopy (gid, 0, gtrid, 0, 1);
     System.arraycopy (bid, 0, bqual, 0, 1);
     Xid xid = new OracleXid(0x1234, gtrid, bqual);
     return xid;

   }

   private static void doSomeWork1 (Connection conn)     throws SQLException
   {

     // Create a Statement
     Statement stmt = conn.createStatement ();

     int cnt = stmt.executeUpdate ("insert into my_table_a values (4321)");

// int cnt = stmt.executeUpdate ("select * from my_table_a");

     System.out.println("No of rows Affected " + cnt);

     stmt.close();
     stmt = null;

   }

   private static void doSomeWork2 (Connection conn)      throws SQLException
   {

     // Create a Statement
     Statement stmt = conn.createStatement ();

     int cnt = stmt.executeUpdate ("insert into my_tab_b values ('test')");

// int cnt = stmt.executeUpdate ("select * from my_tab_b");

     System.out.println("No of rows Affected " + cnt);

     stmt.close();
     stmt = null;

   }

} Received on Wed Apr 16 2003 - 07:32:18 CDT