Home > Archive > Java Beans > March 2004 > JDBC Transactions involving more than one database?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Víctor 2004-03-19, 8:46 pm |
|
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
| |
| Happyslayer 2004-03-19, 8:46 pm |
|
"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
|
|
|
|
|