CODEWITHSUNDEEP
mysqldelphi
sasa
sasa

Reputation: 31

Transfer data between two different databases with Delphi

I want to do a data transfer between two different databases in Delphi. I can not work with TBachmove because the columns that are going to be transferred are selected by the user.

I try to integrate this code but I did not know how to integer it with different databases.

INSERT INTO table_destination ( colonne1, colonne2, colonne3, ..., colonneN )
SELECT
    colonne3,
    colonne8,
    NULL,
    ...,
    colonne137,
    ...,
    colonneN
FROM table_source;

Upvotes: 0

Views: 1593

Answers (1)

MartynA
MartynA

Reputation: 30735

I can not work with TBachmove because the columns that are going to be transferred are selected by the user.

Actually, even in D7, you should be able to do this quite easily as long as Blob columns are not involved. Steps:

  1. Create an ODBC System DSN pointing to your MySQL destination database.

  2. Create a Delphi project with a TTable, Table1, which opens your Paradox table, a TQuery, Query1, which uses the same BDE Alias as your Paradox Table1, a TTable Table2 which uses the ODBC Alias from step 1, and a BatchMove component. Give Table2 a TableName which is what you want the MySQL table to be called.

  3. Provide a gui method for the user to select which columns to copy.

  4. When the user has selected the columns to copy, create a SQL statement to select those columns from your paradox table, load it into Query1 and call Open on it.

  5. Set Query1 as the Source of BatchMove1, Table2 as the Destination and the BatchMove mode as batCopy.

  6. Call BatchMove1.Execute.

That should do it.

My first attempt to test this, using the Biolife.Db table from the "FishFacts" demo failed because I got an exception complaining about an invalid blob size. My second attempt, using the Customer.Db table worked fine though.

As far as the blob-size problem is concerned, although it has been 15+year since I used the BDE, ISTR that there is a maximum blob-size configuration parameter that can be adjusted via the BDE Adminstrator and that there was a way to set it to "No maximum", possibly by setting it to -1.

Current versions of Delphi include the FireDAC data-access components, which include a "modern" BatchMove component which does not depend on the BDE, but I think FireDAC was only included with Delphi in XE8.

Upvotes: 2

Related Questions