Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

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.




0
formadmirer
Asked:
formadmirer
2 Solutions
 
pcelbaCommented:
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 DoschkeSoftware 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now