All geek questions in one place

Data transfer between different DBMS

Since I could not get any satisfactory answer to my Question , it seems to us that we need to write our own program for this, we are at the design stage, and we are thinking about which format we will use for data backup.

The program will be recorded in Delphi.

It is necessary to export / import data between Oracle / Informix / Msserver, it is very important , this is a performance problem, since this program will work in 1-2 GB databases. In addition to normal data in the database, there are Blobs that need to be backed up.

We thought about Xml-Data or comma-separated data, since both of them are transparent (which is nice to have), but Blobs should be considered here. In this case, the Paradox format is not optical.

Can some performance formats be recommended?

Any other ideas to achieve the same goal are welcome.

Thanx in Advance.

+4
source share
7 answers

I use an excellent program called OmegaSync for my backups, but it will only process Informix through ODBC, not directly. If you find that you can use OmegaSync, you will find that its performance is excellent because it compares the databases first and then only the differences are synchronized. You might want to use this idea if you decide to program yourself, if efficiency is your number one goal.

But transforming a programming database is very complicated, as other answers to your question said. So, why don't you just develop the SQL you need, and do it like this. For example, see Converting an Informix schema to an Oracle schema or any other RDBMS . To move data, check sources such as: Moving non-information data between computers and dbspaces

You can optimize SQL so that I’m sure it will be fast enough if you quickly and quickly load your data.

+4
source

DbUnit is a popular tool that can retrieve and load XML data, see

http://www.dbunit.org/faq.html#extract

// partial database export QueryDataSet partialDataSet = new QueryDataSet(connection); partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'"); partialDataSet.addTable("BAR"); FlatXmlDataSet.write(partialDataSet, new FileOutputStream("partial.xml")); // full database export IDataSet fullDataSet = connection.createDataSet(); FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));
+3
source

You checked ODI (Oracle Data Integrator). It supports many source databases. It is able to capture changes from source databases and integrate them into the target database. It is completed, but has a price.

Ronald.

+1
source

The new DBExpress structure makes it possible to export / import data between many databases. you can check out this CodeRage Deep Dive session in dbExpress by John Custer

+1
source

You should use your own binary integrated format (xml for text / streams for Blobs).

+1
source

If you need to export metadata, not just data, it can be very difficult. There are many subtle (and not so subtle differences) between the databases that you intend to use, that such a format should be fairly general, and the export / import code should be able to translate and map metadata through the databases, and also because the external application cannot write directly to internal database structures, it will need to generate db of proper DDL to create data structures. While this is a proprietary format, IMHO its design is the least of your problems, if size and performance are important, and the file is read sequentially, it is not difficult to design a binary format. In any case, import / export and backup are two different tasks. If you need to backup the database, use its capabilities. Usually they provide much more control, i.e. Time recovery. If you need to move data in databases, which is another problem - I would write only code to move data, not metadata, having previously created the required structure in the target database.

0
source

You can give Toad (Quest Software) a try.

It supports all of your mentioned platforms and can do things like “Export table data to INSERT statements” on the source platform, which can then be run on the target platform. IIRC there is even some internal backup Toad format that can be cross-platform.

Toad communities:

Some videos about export, import:

0
source

Source: https://habr.com/ru/post/886639/

More articles:


All Articles