JDBC Transactions involving more than one database?

Hi,

I'm building a data synchronization process that must be contained
within a transaction.  Tables from two different databases (Oracle and
Informix) will be updated during the process.  I'm thinking I can't do
this with JDBC type transactions since they are based on the Connect
type object and it can only handle transactions from one database
(unless they could be nested somehow?).

Would EJB transactions be the best way to go?  If so, please explain
why and how.....thnx!

Environment:
WebLogic 8.1 SP2
JRE 1.4.1
Oracle 9.2
Informix 9.3


Larry

Hi!
Don't worry, you can use two or more transactional resources (two
database conenctions in your case) and roll back them all. Don´t nest
them, Weblogic will say commit or rollback to every TX resouce included
in your transaction.
I have not had any experience on threating two database connections but
I have had this managing one database and one JMS connection committing
or rolling back both.

Larry wrote:

> Hi,
>
> I'm building a data synchronization process that must be contained
> within a transaction.  Tables from two different databases (Oracle and
> Informix) will be updated during the process.  I'm thinking I can't do
> this with JDBC type transactions since they are based on the Connect
> type object and it can only handle transactions from one database
> (unless they could be nested somehow?).
>
> Would EJB transactions be the best way to go?  If so, please explain
> why and how.....thnx!
>
> Environment:
> WebLogic 8.1 SP2
> JRE 1.4.1
> Oracle 9.2
> Informix 9.3
>
>
> Larry

"Larry" <larry.brown@bankofamerica.com> wrote in message
news:2686e6da.0403040704.4e1a96ea@posting.google.com...
> Hi,
>
> I'm building a data synchronization process that must be contained
> within a transaction.  Tables from two different databases (Oracle and
> Informix) will be updated during the process.  I'm thinking I can't do
> this with JDBC type transactions since they are based on the Connect
> type object and it can only handle transactions from one database
> (unless they could be nested somehow?).
>
> Would EJB transactions be the best way to go?  If so, please explain
> why and how.....thnx!
>
> Environment:
> WebLogic 8.1 SP2
> JRE 1.4.1
> Oracle 9.2
> Informix 9.3
>
>
> Larry

I've had experience with this--ported 40,000 records from one database (and
format) into another.

All you have to do is get two connections (conn1 and conn2, one for each
database), and simply do the reading/writing in-between.

Something along these lines:

ResultSet rs1 = conn1.executeQuery(sqlString1);
PreparedStatement stmt =  conn2.prepareStatement(sqlString2);
while (rs.next()) {

stmt.setInt(1, rs.getInt(1));
//repeat as necessary, getting integers, doubles, Strings, booleans, etc

stmt.addBatch(); //suggest using batch updates for larger transactions
}
int i = stmt.executeUpdate();


Just don't forget to close your connections, catch Exceptions, etc...typical
cleaning up.

Hope this helps--worked fine for me.

As an aside, I created a couple of beans to handle "translating" the data
from one format into another...worked pretty good.

Let us know how it turns out.

Happyslayer

Thnx for the feedback....I do have a little experience with EJB's, but it
doesn't look like I have to go there.

"Larry" <larry.brown@bankofamerica.com> wrote in message
news:2686e6da.0403040704.4e1a96ea@posting.google.com...
> Hi,
>
> I'm building a data synchronization process that must be contained
> within a transaction.  Tables from two different databases (Oracle and
> Informix) will be updated during the process.  I'm thinking I can't do
> this with JDBC type transactions since they are based on the Connect
> type object and it can only handle transactions from one database
> (unless they could be nested somehow?).
>
> Would EJB transactions be the best way to go?  If so, please explain
> why and how.....thnx!
>
> Environment:
> WebLogic 8.1 SP2
> JRE 1.4.1
> Oracle 9.2
> Informix 9.3
>
>
> Larry