Hi all,

I have a following problem.

Brief description:
I need to perform two phase commit to two different Firebird databases.
The commit should be performed from desktop application and no
application server or transaction manager should be used.
The two phase commit should be performed directly from the application
using only the Jaybird library.

Do you know how to know how to do this?
Can you send me your code (the skeleton of two phase commit)?
You can see what I need when you read my code snipplet in the verbose
description bellow.



Verbose description:
We develop a desktop application based on Netbeans platform which serves
as thin client to a database.
Now, we work on a part of the application, in which we should save quite
a lot of images, technical drawings and other files into the database.

Basically, there are two ways how to do this.
1) We can save the images and tech. drawings on the filesystem and save
only the paths to them in database.
2) We can save the files directly into the database as blob fields.
We consider to use the 2nd way from various reasons.

However, the size of the images which will be saved to the database will
dramatically change the size of our database.
This increases the time of backup/restore or validation of the database.
Therefore, we want to save the images and other files into a separate
database.

Finally, we need to use the two phase commit in order to not corrupt the
integrity between two separate databases.
Further we do not want to use some application server. We want to
perform the two phase commit directly from the client via JDBC.

I follow this two resources in order to find out how to do it:
TestXADataSource.java from jaybird sources
http://archive.devx.com/java/free/articles/dd_jta/jta-2.asp

and as a result I get this code:
//Initialization
AbstractFBConnectionPoolDataSource mainConnPool =
FBPooledDataSourceFactory.createFBConnectionPoolDataSource();
AbstractFBConnectionPoolDataSource docConnPool =
FBPooledDataSourceFactory.createFBConnectionPoolDataSource();
mainConnPool.set ....
docConnPool.set ....

XADataSource mainXAds = (XADataSource) mainConnPool;
XADataSource docXAds = (XADataSource) docConnPool;

try {
XAConnection mainXAConn = mainXAds.getXAConnection();
XAConnection docXAConn = docXAds.getXAConnection();

XAResource mainXARes = mainXAConn.getXAResource();
XAResource docXARes = docXAConn.getXAResource();

Xid xidA = new FBTestXid();
Xid xidB = new FBTestXid(xidA.getGlobalTransactionId());
boolean chyba = false;

//two phase commit start
mainXARes.start(xidA, XAResource.TMNOFLAGS);
docXARes.start(xidB, XAResource.TMNOFLAGS);

Connection mainConn = mainXAConn.getConnection();
mainConn.setAutoCommit(false);

// insert to first database
boolean error = false;
try {
Statement stmtA = mainConn.createStatement();
try {
stmtA.execute("INSERT INTO TEST_LOG (LOG)
VALUES('dadadadada')");
} finally {
stmtA.close();
}
} catch (SQLException ex) {
error = true;
mainXARes.end(xidA, XAResource.TMFAIL);
} finally {
mainConn.close();
}

if (!error) {
mainXARes.end(xidA, XAResource.TMSUCCESS);
}


//insert to second database
error = false;
Connection docConn = docXAConn.getConnection();
System.out.println("Doc connection autocommit: " +
docConn.getAutoCommit());
docConn.setAutoCommit(false);
try {
PreparedStatement stmtB =
docConn.prepareStatement("INSERT INTO DOC_FILE (SOUBOR,ID) VALUES (?,1)");
byte[] b = ("BLABLABLA").getBytes();
stmtB.setBytes(1, b);
try {
stmtB.execute();
} finally {
stmtB.close();
}
} catch (SQLException ex) {
chyba = true;
docXARes.end(xidB, XAResource.TMFAIL);
InfosysUtils.showExceptionDlg(ex);
ex.printStackTrace();
} finally {
docConn.close();
}

if (!chyba) {
docXARes.end(xidB, XAResource.TMSUCCESS);
}

try {
mainXARes.prepare(xidA);
docXARes.prepare(xidB);
mainXARes.commit(xidA, false);
docXARes.commit(xidB, false);
} catch (XAException ex){
mainXARes.rollback(xidB);
mainXARes.rollback(xidA);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
main ConnPool.shutdown();
docConnPool.shutdown();
}

The problem with this code is that it does not work as I want. Namely,
when the second insert to the document database fail (due to the primary
key violation), it does not rollback both transactions. It commits the
first thransaction as well as the second transaction.

Could you please give me advice how to implement the two phase commit
correctly?

Thank you,

Honza Hubeny