JDBC Data Transformation

I'm sure there are utiltiies to convert data from one database (say MySql) to another database (say, Oracle) that a database administrator is familiar with. However, it will proably take you longer to find these tools and learn how to use them than the method your using now. Especially if you only have a few tables to transfer and they dont have complex associations (constraints, etc). Also, I seriously doubt there is any function in JDBC that will do it for you automatically (I'm quite sure of it).

Since copying the data from one database to another is only going to be done once it doesnt matter how inefficient the process is.

Here are some things you might want to research when moving the database to another database:

* Do you have the same table (names) on the new database as the old?

* Does each table have the same field names?

* Is the type, size, and name of each field the same?

* Is each field nullable or non-nullable as on the old database?

* Is the primary key columns indexed as on thd old database?

* Are triggers set up the same on the new database as on the old? For example: some primary key columns may be set up to automatically create a new primary key when you insert a new record (you will have to keep this feature off until all the data is transfered since you dont want the new database creating new primary keys on the transfer, but do want it to when the transfer is done and some other program is using the database and needs to insert a record.

* What about all constraints set up on the database? You cant insert a child record before a parent record or you'll violate a constaint.

* What stored proceedures and stored functions exist that you need to duplicate?

* public synonyms set up correctly?

* did you grant access rights to each table (read, write, delete, insert)?

A utility tool that you can use to evaluate what contraints, etc a database has is 'Toad'. You may be able to obtain a 30 day evaluation of the tool to use to analyize your database.

George123a at 2007-7-12 22:14:25 >