VFP9 & Remote MySQL Backup to Local

Hi all. I have an FP application that makes use of a MySQL db that resides on a remote server.

Locally, I have MySQL installed on Windows and from time to time I use a third-party program to copy all the MySQL tables from the remote server to my local windows installation. This works well but has several manual steps and cannot be run unattended.

What I would like to do is accomplish the same thing, but within my FP app. But I'm not sure where to begin.

Since I can run queries on the remote MySQL db and the local MySQL db, I'm assuming there must be a way to determine what tables exist along with their structure, and recreate those tables locally. And I guess the data as well?

Like I said I'm unsure where to begin, so your help would be greatly appreciated.




formadmirerAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
If you are able to connect to the MySQL server via SQLCONNECT() or SQLSTRINGCONNECT() then you may simply obtain a list of all MySQL tables:

SQLTABLES(lnSqlHandle, "TABLE", "cTables")

Columns for certain table are also easy to populate:

SQLCOLUMNS(lnSqlHandle, "TableName", "NATIVE", "cColumns")

Some code examples are e.g. here:
http://msdn.microsoft.com/en-us/library/f3c5e4wf(VS.80).aspx
http://msdn.microsoft.com/en-us/library/wfktfyx0(VS.80).aspx


More ways of MySQL data handling from VFP are described in the following book which I highly recommend:
http://www.hentzenwerke.com/catalog/mysqlvfp.htm
0
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
How about using a tool like MySQL dump?

http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

Otherwise, your plan could be done, there is SHOW TABLES to list all tables and SHOW CREATE TABLE will give a create a CREATE TABLE statement you'd need to create a table locally as it's defined remotely.

Nevertheless perhaps first have a read of mysqldump before reinventing the wheel.

Also, if you actually need database replication to have your local database up to date all the time instead of copying the database from time to time, then have a read on replication here: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Bye, Olaf.
0
 
formadmirerAuthor Commented:
Thx for the info. I plan to work some with the suggestions from both later on today and see how it goes.

And thanks for the book link. I had no idea a book existed specifically for using MySQL with VFP. I'm currently looking for the best price online and definitely plan on getting this.
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Virtually anything can be automated unless it does not involved a human decision. If a user is clicking or typing it can be automated. If he is thinking, it might be automated. :-)
0
All Courses

From novice to tech pro — start learning today.