Solved

VFP9 & Remote MySQL Backup to Local

Posted on 2010-11-27
4
495 Views
Last Modified: 2012-05-10
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
Comment
Question by:formadmirer
4 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 350 total points
ID: 34222278
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
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 150 total points
ID: 34222285
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
 

Author Comment

by:formadmirer
ID: 34222504
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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 34223102
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now