• 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

best approach to move data between 2 databases

 
Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

I have a question on which approach to follow for Migration of data from one database to another.
The scenario is something like this.

The source & target databases are from differnt vendors.

We have created an exact replica of the table & column structure in the Target database similar to the source database.
I am thinkin of writing a java utility to pull data (resultset) from the source database & then dump it into the target database. I will be using plain JDBC approach for the same.

I will have to load the drivers for each of the databases , create separate connections & all the usual JDBC stuff for both databases.Since this is just one utility when I retrieve the resultset from the source database then i will close the connection to the source & connect to the target database.

So my resultset object will not be available.
Can some one throw some light on what would be an elegant solution to the above problem

Regards
 
manish ahuja
Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All

To ellaborate on the scenario.
I will be having a connection , statement objects directing to the source database which will fetch the resultset.
Then I need to create a new connection , statement objects pointing to the target database & insert the resultset.
The problem is when I fetch the resultset from connection1 it wont be available when i create connection2 & try to insert into database2.

Can someone throw some light on the same

Regards
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

We have created an exact replica of the table & column structure



You should consider using the bulk load/unload tools that come with your databases.

Using Java to select and insert the data will not perform as well as bulk unloading/loading, will take extra time to develop and provides no advantages.

I would write a program to copy the data only if you need to manipulate the data before inserting it.
 
manish ahuja
Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Scott

Thanks for your response.
You are absolutely right. I should have mentioned the same in my previous posts.
Yes I would have to slight changes to the data before inserting into the target databse & hence the java program but that is not always. In few cases the data will pass through as it is. We dont have huge volumes of data per table but number of tables are high.

At the moment the design is to migrate using this java utility. Do post your suggestions of any approach that may help

Thanks
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Yes I would have to slight changes to the data before inserting into the target databse & hence the java program but that is not always


Most bulk load tools will let you manipulate the data you are loading as you load it. I'd take another look at what your database provides, make sure your requirement is not already met.
 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why do you must close the database connection from source before you get the database connection from target?
You can hold two or more connections if you want to.
You can close them at the end.
[ October 25, 2006: Message edited by: Lucas Lee ]
 
manish ahuja
Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Lucas

Thanks for the response.
What I meant to ask was how do i take the resultset from the first connection object & fire an insert into the second database using the second connection.

Say when I m doing while (rs.next()) & iterate over each row & then want to insert the results not using the connection1 (statement1) but connection2(statement2).

This is what i want to achieve. Like having 2 valid Statement or Prepared Statements using 2 different connections each mapping to different databases live at the same time simultaneously in a way that i fetch results from statement1 over connection1 from database1 & dump into database2 using preparedstatement2 over connection2.

This is finalized design. So I have to move in this direction & not allowed to use any database specific utilities

Regards
 
Lucas Lee
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know what you mean really.
If you have known you can hold tow different connections simultaneously,then what do you want to do? Insert to the source result set directly?
What I think is:
Get ResultSet from source database,then read data from it and insert data to the target database by executing insert sql.
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

What I meant to ask was how do i take the resultset from the first connection object & fire an insert into the second database using the second connection.



There's no magic here. You'll need to take the values from each row of the result set and set them as parameters on an insert prepared statement.

You could use an ORM tool such as Hibernate. You'd retrieve an object from one Session and persist it to the other. This would eliminate the need to copy the values from the result set to the insert prepared statement. Hibernate would take care of generating and executing the necessary sqls.

This won't perform nearly as well as using a bulk load utility though.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


You could use an ORM tool such as Hibernate. You'd retrieve an object from one Session and persist it to the other. This would eliminate the need to copy the values from the result set to the insert prepared statement. Hibernate would take care of generating and executing the necessary sqls.


ORMs tend to have too high an overhead for bulk loading. Better to just use straight JDBC. Or best, as you note, to use the specialized tools the database provides.
reply
    Bookmark Topic Watch Topic
  • New Topic