Best approach to update mySQL table from FoxPro

Vladimir Buzalka
Vladimir Buzalka used Ask the Experts™
on
Hi all,

I would like to have your idea what is the best way to update mySQL table  with data from FoxPro table.

I.e. I have 600 000 records in my foxpro table, 2 columns (id, md5) and I have 600 000 records in mySQL with 2 columns (id, md5). MySQL column ID contains same IDs as foxpro table and I need the quickest appoach to assign MD5 from foxpro table to mySQL table.

Any ideas are welcome.

Vladimir
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Olaf DoschkeSoftware Developer

Commented:
If you have any additional fields about a change date of the records and a last import/update date, then you could limit yourself to only update changes since the last update.

The easiest is to truncate the mysql table and insert all data into it from the dbf afterwards, as that will be the same result as when updating the existing records from the dbf.

Bye, Olaf.

Author

Commented:
Hi Olaf, I understand,. the best way is to import directly in the single step all foxpro table to mySQL. However I am just curious how to UPDATE mySQL  in my scenario?
It is impossible to tell what is the quickest approach because you don't disclose existing indexes. You are also not disclosing your infrastructure, number of concurrently working users, and network speed which is also very important for the decision.

If no indexes exist then the really quickest way is to truncate the MySQL table and create a new one from VFP data. You should import as many rows in one command as possible.
Software Developer
Commented:
As you can't determine which of the 600.000 records are changed if really doing this from vfp as an UPDATE SQL you'd neeed to load all 600.000 records from MySQL into a remote cursor, eg via remote view, then do an update from the dbf and store that via Tableupdate.

That's not advisable.

Another way would be to first store all the 600.000 records of the dbf into a second MySQL table and then do the update of the first MySQL table from the second table.

It's not possible to update a mysql table from a dbf directly, you either pull all data into dbfs or into mysql table and then can update one from the other table, that's the problem and that's why truncating ans full insert is always faster.

In each situation you load the full data of either the dbf or the mysql table before even starting the update. As you need to do that you can also simply empty the mysql table and refill it in the first place.

The one thing that SQL Server can do is adding a DBC or some DBFs as a linked server. I'm not sure but would guess MySQL doesn't offer this option, so you can't access a dbf from within mysql without first loading it into a mysql table. And vfp can access a mysql table only via soime remote data access technique, which will creata a cursor. That also means a full load of the data before working on it.

See?

Bye, Olaf.
Olaf DoschkeSoftware Developer

Commented:
...and because of that you better know in advance which records have changed in the dbf since the last time you updated the mysql table from the dbf.

Another way is to couple these systems, eg instead oif using the dbf let the vfp applicaion work on the mysql database in the first place or at least have triggers in a dbc that do the same changes towards mysql data, as is done on the dbf.

Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial