The procedure of moving FoxPro databases to MySQL server is quite easy when compared to migration between another DBMS. The cause of this relative simplicity is the fact that FoxPro doesn’t have such advanced database objects as stored procedures, triggers, and views. This means that FoxPro databases are only utilized as storages while all data handling logic is confined in the matching application(s). As a result, it is only needed in transferring the data from FoxPro to MySQL database.
Even so, even migration of FoxPro data to MySQL server can be a complicated job. The primary issues you can encounter comprise of:
- Unmatched data types. FoxPro has type Logical having two possible values: True (stored as symbol ‘T’) or False (stored as symbol ‘F’). MySQL has corresponding type BOOLEAN or BOOL that is synonyms for TINYINT(1) that also accept two possible values: 1 for True and 0 for False. Based on semantic equivalent, ‘T’ should be mapped into 1 and ‘F’ must be mapped into 0. But, particular cases may need to preserve the initial data “as is”. The most suitable type mapping for such situations is ENUM(‘T’,’F’).
- Different character sets. Although DBF files store details about encoding in the header, occasionally it could be empty or incorrect. So, to get correct conversion it’s important to analyze results after converting data with code page described in the DBF file. If certain parts of the text are incorrect, the database migration expert needs to run conversion again using another codepage.
How different recommendations for FoxPro to MySQL migration address the problems above? The most transparent approach is to export DBF files (FoxPro tables) to Comma Separate Values format and thereafter import it to MySQL. DBF files can be converted into CSV format using free tool dbf2csv available at SourceForge.net. The next part of the process could be done via MySQL “LOAD DATA INFILE” statement as follows:
- Copy the CSV file(s) into data folder of the destination MySQL database, because MySQL will only allow you load data from a CSV file that is in the data folder (for security reasons).
- Run the following statement
LOAD DATA INFILE ‘student.csv’ INTO TABLE mydatabase.student
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;
Apparently, none of two described difficulties are fixed by this approach, it is essential to consider certain post-processing process in to solve them manually.
Script dbf2sql.php enables you to convert DBF files into SQL script that builds table and fill it with data preventing intermediate steps like CSV file. But, it doesn’t give you the opportunity to set up the mapping of FoxPro logic type and also to specify the user-defined encoding, and so it doesn’t sort out potential problems with FoxPro to MySQL migration in an intelligent manner.
Commercially produced tools like FoxPro to MySQL available through Intelligent Converters gives you the opportunity to personalize every possible parameter of the conversion process: how one can process logical values, what encoding ought to be used among others. These features aid in avoiding intermediate steps and manual efforts throughout the migration process. Furthermore, the software can migrate FoxPro database to MySQL server immediately or export the data into local MySQL script file containing SQL statements to generate tables and fill all of them with data. The next option needs to be used if the target MySQL server doesn’t accept remote connections.