copy from MsSQL to MySQL database

[ac]

Hi there,

Nice forum ! I'm in trouble and perhaps you can help me.

I'm using Zeos 6.51 with Lazarus 0.9.21 on a Win2k machine.

I want to copy a table from a MsSQL-database to a MySQL-Database.

Do I need two TZConnection ans TZQuery for this? How can I build some kind of "bridge" between the MsSQL and the MySQL connection?

Can I store the contents of a table-cell in a variable (what data-type?)

greetings, ac

[mdaems]

Do you want to do this once or on a regular basis? How much records?

One shot : export to a text file. Import into mysql with the mysqlimport tool.

Using Zeos : 2 connections, 2 queries .
Loop on the read query. For each record move all field values to a new record and post.

Maybe you can send a private message to zippo. I think I remember he did something similar.

Mark

[ac]

thanks

yes, on a regular basis with a few tables. some of them are really huge.

how can I write the content of a table cell into a record (or something else if possible)? I found no option to get the content from a cell in a DBGrid.

[zippo]

I would try to do it by 4 methods

1. Via MySQL GUI tool for data migration - I tried it once and it worked quite well (few, nonrelevant errors). Definitely worth a try.

2. Via SQL export. I'm not an MSSQL expert, but if there's a SQL export option, use it. And if (well, chances are quite low here) that there's a "Export standard SQL" option, use it too. Next is harder use, because is a try-fix-and-retry method: Execte the SQL, where stops fix it... Can take ages..

3. Via Delphi app: Two databases (MSSQL and MySQL), two tables. I usually use KBM MemTable for an intermediate, because it can copy from and into an open dataset. But I never used it for cross-DB copying.

4. I can give you some delphi code that I used to copy a database, but I'm not sure i works.

[ac]

thanks zippo,

your hint with the MemTable was very good

[zippo]

Anytime.. Hope it will work OK (never tried between different database types).

[ac]

yes it works, but not perfekt yet...

I'm sending DBMemo.text as value with SQL INSERT INTO command. unfortunately DBMemo.text is a string.

what if I need to send an integer or a date? somebody knows how to send the record that is written in MsSQL-table.

[zippo]

Sorry, don't understand... SQL as itself is a string that descripts the values to be inserted. Example, please.

[ac]

sorry, that I didn't answer but I was busy with other things. I found a solution for my problem in the meantime, but thanks for your efort !

but I have another question.

when I try to send a SQL SELECT command (e.g. SELECT testfield FROM testtable WHERE id=1;) to the MsSQL server I got an error if the data type of the field is ntext.

the error:

Code: Select all

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier

I changed the datatype in the MsSQL table from ntext to text and it works pretty fine. unfortunately this isn't a solution because it need to be ntext.

[zippo]

Try to access it via ODBC (latest of course) and "regular" datasets (like old,slow, ugly BDE)

[ac]

hmm.... what do you mean by "regular" dataset?

I tried to execute my programm on another machine. it crashs when I try to establish the connection to the MsSQL-server, MySQL works.

there is no error message, programm stops working and I got only the possibility to kill it with task manager.

I copied libmysql50.dll and ntwdblib.dll to the programm folder. is there any other file that is required to set up the MsSQL connection?

thanks for your help so far !!!!!!!

[zippo]

regular = TTable + BDE + ODBC As said: Ugly, slow, but usually worx for one-tme projects ..