• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Connecting to multiple databases using different jdbc drivers

 
Ranch Hand
Posts: 430
Android VI Editor Debian
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a requirement to write a daemon based process (Not web based) that will connect to an Oracle 10G database, read some data from it and then connect to a SQL Server database and write the data to a table.

Sounds simple enough but i have a couple of queries about this.

* i will need to have two jdbc drivers, i.e. one for connecting to the Oracle database and the other for connecting to the sql server database. The sql server jdbc driver is the jtds jdbc driver (http://jtds.sourceforge.net/) and for Oracle i will be using the standard oracle jdbc driver. Am i likely to come across any problems with both drivers available in the classpath together? Is there a way to achieve this with only one driver? I had a look at hibernate and decided that it would be overkill as all i want to do is extract data and insert it into another database so hibernate wont provide any advantage in this scenario.

* My guess is that all i need is a ConnectionManager class to manage the connections and a client DAO class that would call the relevant method to get the connection it needs depending on whether it is reading from Oracle or writing to SQL Server. Is this a reasonable approach or is there a better design/pattern for this?

Thanks
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Am i likely to come across any problems with both drivers available in the classpath together?


No

Is there a way to achieve this with only one driver?


No

I had a look at hibernate and decided that it would be overkill as all i want to do is extract data and insert it into another database


Most likely true.

My guess is that all i need is a ConnectionManager class to manage the connections and a client DAO class that would call the relevant method to get the connection it needs depending on whether it is reading from Oracle or writing to SQL Server. Is this a reasonable approach or is there a better design/pattern for this?


If this task is small enough that you think using ORM would be overkill, then anything fancier than this is probably overkill as well.
 
O. Ziggy
Ranch Hand
Posts: 430
Android VI Editor Debian
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok i have tried to put together a quick design solution. See image below



The problem i think i am having is how to commit. Here is the flow of processing

- InvoiceBD gets an Oracle connection from the factory class and calls InvoiceUploadDAO.readData passing it the Oracle connection object.
- InvoiceBD get a SQL Server connection from the factory class and calls InvoiceUploadDAO.writeData passing it the SQL Server connection object.
- InvoiceBD reuses the Oracle connection to call InvoiceUploadDAO.update status to 'Complete' set status on the Oracle database.

InvoiceBD commits the Oracle connection.
InvoiceBD commits the SQL Server connection.

Or if something goes wrong both connection objects are rolled back.

Does that sound like a possible approach? I cant seem to workout how to resolve a situation where the first commit succeeds but the second fails.

Thanks
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Transactions involving multiple databases can't be handled with regular JDBC transactions; you need to use distributed transactions (so-called "XA" transactions). You need to check whether the databases drivers you're using support distributed transactions.
 
O. Ziggy
Ranch Hand
Posts: 430
Android VI Editor Debian
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:Transactions involving multiple databases can't be handled with regular JDBC transactions; you need to use distributed transactions (so-called "XA" transactions). You need to check whether the databases drivers you're using support distributed transactions.



Hi yes i read up about XA transactions and thought that it was quite complicated and overkill for what i am trying to do. The process i am writing will be written so that if SQL Server commit fails the Oracle commit will not take place. This means the process can be re-run at any time because it did not complete on the previous attempt. The Oracle commit will be done last and if this does not happen the whole transaction will be treated as a failed transaction.

if the SQL server commit succeds but the oracle commit fails, the transaction will still be marked as failed so the process can be rerun again. When it is rerun, the data on the sql server is deleted and rewritten.

 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If it's OK to commit something on SQLServer even if the commit fails on Oracle, then I guess regular TX will work.
 
O. Ziggy
Ranch Hand
Posts: 430
Android VI Editor Debian
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:If it's OK to commit something on SQLServer even if the commit fails on Oracle, then I guess regular TX will work.



Yes the reason being that the commit on Oracle signifies success. If the Oracle commit fails the process will not be successfull. When it is rerun again the first thing it does is truncate the data on the SQL server tables and populate it with fresh data.

Thanks


 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic