How to copy a database table to another database matching with TSQLConnection / dbExpress?

Question

I am using C ++ Builder (or Delphi 2007 and XE2) using DBExpress. I connected two databases for Firebird (my local hard drive) for MySQL (on the Internet) using TSQLConnection. Then, my question is ... how can I use (make) a SQL statement (or something else) to copy one table in Firebird to another table in MySQL? Any comment would be very valuable .. thanks a lot in advance.

Answer 1

You cannot copy content from a Firebird table to a MySQL table using an SQL statement. Another RDBMS has this functionality, for example, Sql Server has Linked Servers , and the MySQL FEDERATED engine (works only for MySQL databases).

So, I have two options for solving this problem:

• Iterate over datasets using SqlExpr.TSQLQuery or SqlExpr.TSQLDataSet , and then manually insert each record (row) into a MySQL table.

• Use TClientDataSet to dump Firebird table data using SaveToFile , and then with another TClientDataSet load the data using the LoadFromFile method, finally you can combine the data of the second TClientDataSet into a MySQL table.