aspose file tools*
The moose likes JDBC and the fly likes Copy data from Oracle Database to Access Database in a servlet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Copy data from Oracle Database to Access Database in a servlet" Watch "Copy data from Oracle Database to Access Database in a servlet" New topic
Author

Copy data from Oracle Database to Access Database in a servlet

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Hi i have got a scenario where i need to transfer the updated data from access to oracle database and also from oracle to access on click of a button.
My oracle and access database has got the same table structure , when application runs new data gets recorded first in access database and sets a reference flag to 'U' in all the tables now when i click the button 'upload to Oracle DB' i want a script to run such that the updated rows replaces the old rows present in oracle database.
I am not sure how to start with this i dont have much knowledge of oracle, can anybody suggets me how do i do this also is it something related to Sync the tables in both the databases, and how do i sync the tables present in two different databases. ELse is it something related to writing script which contains commands to copy tables to and fro and this script runs in the servlet where this servlet is called on clickof a button. Please help me out in this issue..
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I have to ask, why is Access involved at all if you are ultimately persisting stuff in Oracle? What is the intermediate step for?

If you need an Access front end to an Oracle database (if for example you have a lot of forms developed in the Access database) you can use linking rather than replication. Access will allow you to create a table that is just a view of the Oralce data.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Here Why access database is required is... my application is used by survey people who works offline so when they are onsite on a survey my application stores the data into access database now when they are back from survey the data from access database is to be updated back into oracle database.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

This is very much an Access model of working and very difficult to do with data sets of any complexity (you can't even do it successfully in Access, despite MS claims to the contrary).

What you have is a very long local cache that will be out of data as soon as it is used offline, and as a result a lot of lost data or conflicts when you try to merge the two. Synchronising databases in this way is very difficult and there is nothing in Oracle out the box to do this. To do this safely you'll really need to write something to interpret the Access transaction logs and tried to perform the same transactions on the Oracle database. No idea how you would do that.
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Ok if i create views in access database for all the tables present in oracle database and update them offline (not connected to oracle database), now to update this data in oracle database can this be encorporated in some batch job? and how can this be done do i need a third party tool?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15665
    
  15

Take a look at the Pentaho Kettle utility. It's one of a number of ETL tools written in Java and it's very powerful. It can be launched as a stand-alone application or invoked on a scheule from the Pentaho BI server, and the Community Edition of Pentaho is free and open source..


Customer surveys are for companies who didn't pay proper attention to begin with.
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Will SQLLoader solve this problem? just wanted to clarify because i started to learn SQLLoader now.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

No. It is designed to load large quantities of data to a database that is (typically) offline. You need something that can manage online updates, inserts and deletes of data, and handle the failure behaviour when you can't insert, update or delete the data because its not valid anymore.
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

what i planned is the offline updates will go and sit in the access database , and when the user is online they run batch of sql loader from java and upload the rows with status as 'c' to oracle database. once the upload is done i will delete those rows that have status 'c' from access database, and again another batch to copy from oracle database to access database all rows that have status 'p'(pending). Is it approach efficient enough? also Is it possible to copy data from oracle database to access database via SQL Loader?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


also Is it possible to copy data from oracle database to access database via SQL Loader?

No.
Jhakda Velu
Ranch Hand

Joined: Feb 26, 2008
Posts: 166
Hii
We had a similar problem. Used a job to synchronize the data between 2 databases, it read data from one, uploaded to the other. Make sure you address these issues that we faced
1. If 2 users updated the same data, how the conflict is resolved.
2. How do you manage sequence id of data inserted, as you say that the tables in both the DBs will be same.
3. Access is a bit unstable.
4. Perfect handshake is not possible here, say you select one record marked as "U" in access and try to upload to Oracle,if it succeeds, you have to mark it done in Access also. Say its updated in Oracle, but update fails in Access. Unless you follow"Check if exists in Oracle, if yes, update,else insert". But this will slow down your app.
5. How do you handle a situation where a few records got inserted, but others failed, do you roll back completely etc.

These are few issues i could remember.

Jhakda


If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Third party apps like SQL Anywhere is supposed to handle much (though obviously not ALL) of the replication issues that you'll face, though I've never actually used it so I can't tell you how seamlessly it works.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Copy data from Oracle Database to Access Database in a servlet
 
Similar Threads
Database Question
FileChooser Window
Transfer data from Access DB to Oracle DB and Vice Versa in a java program
Database Synchronization
Jr. & Sr. Java Developers needed in FL!!!!!!