Oracle9i, Java and XAResource

[Thomas Lang]

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;
}

}