Moving Data between paradox & MySQL Tables

Hello,

   I Have 55 Paradox Tables, I want to imigrate my project to MySQL. I designed the tables  in my SQL. Now I want to move the Data, I made the below code, but it gave me an error message at runtime.

PS: I am using myDAC components ro access MySQL.
       Error Message Attached as jpg Image.
procedure TForm1.Button1Click(Sender: TObject);
var
 I, J : Integer;
 hs: string;
begin
  BDE_Database.GetTableNames(ListBox1.Items);
  I := 0;
  While I < ListBox1.Count do
  begin
    ParadoxTable.TableName := ListBox1.Items.Strings[I] + '.DB';
    ParadoxTable.Open;
    MySQLTable.TableName := ListBox1.Items.Strings[I];
    MySQLTable.Open;
    MySQLTable.First;
    while not ParadoxTable.EOF do
    begin
      MySQLTable.Append;
      for J := 0 to ParadoxTable.FieldCount - 1 do
      begin
        hs := ParadoxTable.Fields[J].FieldName;
        MySQLTable.Edit;
        MySQLTable.FieldByName(hs).Value := ParadoxTable.FieldByName(hs).Value;
        MySQLTable.Post;
      end;
      ParadoxTable.Next;
    end;
    ParadoxTable.Close;
    MySQLTable.Close;
    I := I + 1;
  end;
  ShowMessage('Done!');
end;

Open in new window

Error.jpg
i7madAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ziolkoCommented:
general suggestion is that you should use try/finally blocks in your code other issue is performance of this code, but those are not-relevnat with your problem.

error message that you showed us is not really helpfull so you need to do few things:

check that all tablenames and field names are appropriate
check if types of corresponding fields are ok
tell us what keys you have defined in your tables
and most important of all try to debug your code and find out on which table/field it fails and what value has field of a record that you processing while this error is raised

ziolko.
0
LearnDelphiCommented:
The error message is coming from your SQL statement. This means that your SQL query has a syntax error in the way you wrote it. As ziolko suggested, you can debug your code to get you to the offending line which will point you to the offending query. Submit that SQL statement if you need more help with it.
0
i7madAuthor Commented:
No Query

I just have one procedure and it is attached in my question, so from where this SQL Query error message came from?
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

ziolkoCommented:
it came from db driver I suspect that it's because of values, most likely missing quotes somewhere or key incorrect key-fields values. did you try to find on which table/field/value it crashes?

ziolko.
0
i7madAuthor Commented:
yes ziolko

it is the second table

first table done without any problems, and data moved. but first table it just moved one record and gave me the error on the second record

i checkd the second table, and its second record, everything is normal

0
i7madAuthor Commented:
MY LAST POST EDITED-->

yes ziolko

it is the second table

first table done without any problems, and data moved. but SECOND table it just moved one record and gave me the error on the second record

i checkd the second table, and its second record, everything is normal
0
ziolkoCommented:
i7mad

what are filed types in second tables (paradox and mysql respectivly) and what values does second record have?

ziolko.
0
i7madAuthor Commented:
Here we go


Paradox.jpg
MySQL.jpg
0
ziolkoCommented:
check values for column "Limit" if there is something suspicious.

you can also try to create/destroy MySQLTable in rutime for each transferred table or instead copying values generate INSERT statement and execute it with query instead table

pseudo-code:
procedure TForm1.Button1Click(Sender: TObject);
var
 I, J : Integer;
 hs: string;
 sqltable: TMySQLTable
begin
  BDE_Database.GetTableNames(ListBox1.Items);
  I := 0;
  While I < ListBox1.Count do
  begin
    ParadoxTable.TableName := ListBox1.Items.Strings[I] + '.DB';
    ParadoxTable.Open;
    sqltable := TMySQLTable.Create;
  try
    MySQLTable.TableName := ListBox1.Items.Strings[I];
    MySQLTable.Open;
    MySQLTable.First;
    while not ParadoxTable.EOF do
    begin
      MySQLTable.Append;
      for J := 0 to ParadoxTable.FieldCount - 1 do
      begin
        hs := ParadoxTable.Fields[J].FieldName;
        MySQLTable.Edit;
        MySQLTable.FieldByName(hs).Value := ParadoxTable.FieldByName(hs).Value;
        MySQLTable.Post;
      end;
      ParadoxTable.Next;
    end;
    ParadoxTable.Close;
  finally
    sqltable.Free;
  end;
    I := I + 1;
  end;
  ShowMessage('Done!');
end;

ziolko.
0
i7madAuthor Commented:
ziolko I will try it

but unfortaunetly I Updated my Delphi 2009, Updates 3 & 4, and it gives me access violation when I try to run the IDE :/ what a bad luck

I will try reinstalling it and test your code, and then I will back to you with the result.
0
LearnDelphiCommented:
Your problem is coming from the field "Price Level". Since it is two separate words, you must enclose it in quotes. My recommendation is that you modify your procedure to add quotes to the beginning and end of every field. So:

hs := '"' + ParadoxTable.Fields[J].FieldName + '"';
0
ziolkoCommented:
Delphi 2009? and the components you use for MySQL are standard componeents or some 3rd party?
it may problem related to unicodestrings.

ziolko.
0
i7madAuthor Commented:

ziolko

  yes Delphi 2009

The components are myDAC latest version, and it is designd for Delphi 2009, so there is no problem with them I guess
0
LearnDelphiCommented:
i7mad: Did you try my suggestion above with the quotes?
0
Geert GOracle dbaCommented:
Limit 0

mysql query select reference: http://dev.mysql.com/doc/refman/6.0/en/select.html
select * from table limit 0

this means limiting the dataset to 0 rows (mysql reference says to use some large number to retreive all rows)
this query is probably getting built by the Mydac component
there may also be a smartquery component in which you can specify a tablename
and optionally alter the query to change this number in the limit
like
select * from table limit 0,999999999
0
i7madAuthor Commented:

LearnDelphi:

  Yes I changed both tables design (paradox and mysql) and renamed the "Price Level" field to "PriceLimit" , and got same problem, same error message exactly.
0
i7madAuthor Commented:

Geert_Gruwez: so, u mean that error done by myDAC component? no need to change my code?
0
ziolkoCommented:
Geert note that there is column called "Limit"

ziolko.
0
Geert GOracle dbaCommented:
lol ziolko, you'r still top notch ...

i wonder if it's allowed in mysql and not a reserverd word ...
0
Geert GOracle dbaCommented:
yup:
http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html

guess you'll have to quote that column, i am assuming the mydac would do this though
devart would have foreseen this, i haven't often had bugs with their SDAC and ODAC.
Haven't used MyDac yet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
you really mean this limit column was the cause ?
if so devart has gone a tad down, or it's a bug ...
0
i7madAuthor Commented:
yes it was the whole problem, after changed its name, everything worked fine , thanks ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.