copy mysql data to firebird

[OstCode]

actually my problem is:
i want to "copy" some columns of an mysql (5.x) server to an firebird database.
for that i have the following little script:

while not mysqlquery.eof do
begin
iboqry.params.clear;
iboqry.sql.clear;
sql:="insert into tmp(id,date) values (:id,:date);";
iboqry.sql.add(sql);
iboqry.parambyname('id').asinteger:=mysqlquery.fieldbyname('id').asinteger;
(some more params following after that-doesnt matter here)
mysqlquery.next;
iboqry.execsql;


end;


well everything works fine to the first dataset, but afterwards the 2nd dataset, an error occurs (strange things like "'2' is not an valid format" or something...)
seems like the NEXT method doesnt working correct, as the resultdataset is veeery confusing (from 5 sets to over 30!)
issnt it possible to execute an seperatet sqlstatement with depending params in this loop?
does anyone have an idea?for my part an other solution for copy from mysql to firebird?

big thanks in advance!
regards!

[mdaems]

Why using parameters if you just create a new sql every time? Just fill the values clause immediately.
Or you could put the

Code: Select all

iboqry.sql.clear;
sql:="insert into tmp(id,date) values (:id,:date);";
iboqry.sql.add(sql); 

part before the while clause. I think it would reduce the sql/parameter parsing work.
To me it seems more logical to put the 'execsql' before the 'next', but that shouldn't make any difference.

Mark

[btrewern]

Put a ZUpdateSQL on your datamodule and log the queries you are sending. Sounds like a good start to debugging your app.

Regards,

Ben

[OstCode]

hi!

@ mdaems:
i already tryed to generate the sql statement bycode. its the same farce
tryed diff ways:
a. generate a single insert command and execute every loopstep
b. generate a big insert command (>1 valuelines) execute after loop.

nothing different.
aaah yes its different..5 datasets become 52 not even 34 thats all


@btrewern

i already tryed an updatestatement, i have to admit, i never really worked with these ZUpdateSQL´s...how can i log query with it? i only know them for editing and updating an normal ZQuery...
i can say for sure that its not an mysql problem: when debugging with singlestep correct values are importet from ZQuery to my ibo query, even the loopsteps are correct (# of the datasets)...

blimey!


EDIT: Thread can be closed! problem solved.

[zippo]

Here are a few ideas:
- What about an SQL dump and insert into new DB?
- What about using ODBC and generic tool?

[pol]

Just being curious: how did you solve it?

[btrewern]

Sorry meant ZSQLMonitor.

Regards,

Ben