This was part of a project I did a couple of years back. The project was to rewrite a intraweb application that was written in OdbicScript and used Foxpro tables for data storage. The goal was to make it more industry standard by moving it to a “Lamp” platform. The portion of this project that this article discusses was the method to get the existing data from the Foxpro tables into a MySql database.
There are commercial programs to do this, but this was a well defined one time use, so this solution actually worked well and only took a day to put together.
The program takes advantage of the way MySql does a back and restore. The backup file from MySql is in clear text, so by getting the attributes of the Foxpro tables it was pretty easy to emulate the backup file, and then use that to move the data into a MySql database.
To describe the MySql backup file, the beginning will drop a table if it exists, then create the columns in the table, and then finally insert each line of data into the MySql Table.
To actually restore that file I used the command line form, on the target box (probably a linux/unix variant but hey I’m not judging) as a MySql administrator run the command “mysql -u username -p database < file.sql”. Substitute username with a valid username, the p will cause it to ask for a password, substitute database with the target database, and redirect the backup file as standard input (which is the < filename at the end of the command).
I know this is sketchy, I will flesh out the steps and elaborate more on what the programs do, but I wanted to get this out, to get past the deer in the headlight syndrome, which is exacerbated by a almost blank website.