Monday, August 11, 2003 05:02 AM
The response is not simple at all. You could solve it easiliy (more or less), if the two database engines where Oracle 9i (using Advanced Replication, MASTER-MASTER or MASTER-SNAPSHOT, depending if the two databases or only one of them is updated). (I think that there are also solutions for a mySQL only environment, see http://www.databasejournal.com/features/mysql/article.php/1584401
If you need to use mySQL and Oracle I think you have to program your own solution (I don't know if there is any commercial or free tool in order to achieve this...). My recomendation is:
- Have in all your tables two columns (dateCreation & lastChange), and program your update code in order to modifiy properly this information.
- If you cannot modify the tables, create a table that saves this information for the valuable table of your database model.
- If you cannot modify the source code of the application, create triggers in order to modify the proper colums
- Your java replication code must have idea of the last replication date (perhaps saved in a file, or in the mySQL or Oracle Database)
- Use this replication date in order to make the proper queries in the mySQL database. Before inserting any data into Oracle database, take into an account the integrity constraints