Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Oracle9i, Java and XAResource

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US