Migration between databases is generally considered to be a complex procedure but FoxPro to MySQL migrations are easy compared to other DBMS. The absence of some advanced database objects such as views, triggers, and stored procedures are the reasons for the sheer simplicity. Technically, the purpose of FoxPro database is to store data. To that effect, matching applications act as storage for data handling logic. This is why they only handle data between FoxPro and MySQL database.
However, this somewhat simple process can be complex when dealt with the wrong or unprofessional hands. Some common problems are:
- Data types that do not match
There are two possible values for type Logical contained in FoxPro – True, designated by ‘T’ and False, designated by ‘F’. Also, there are two possible values for MySQL which are type BOOLEAN or BOOL – 1 represents True and 0 represents False. TINYINT(1) is the common synonym for both types. Initial data ‘as is’ may need to be preserved in special case. In such a situation, ENUM(‘T,’F) are best suited for type mapping.
- Set of characters that are different
There is also a possibility that ‘encoding in the header’ details are empty or incorrect in their corresponding DBF files. There are codepages in DBF files which convert data. Correct conversion is best derived from proper analysis of the results of conversion. It is the duty of the database administrator to run conversions again with another codepage should in case some parts of the text are wrong.
So, how can the problems above be addressed with several recommendations to migrate from FoxPro to MySQL? Simple! FoxPro tables also known as DBF files are exported as CSV (comma separated value) after which they are imported to MySQL. A common tool for conversion of DBF files is dbf2csv. Then MySQL statement ‘LOAD DATA INFILE’ will import data from CSV files into the target database. In a destination file, copy CSV files in a data folder. For security reasons, CSV file must be present in the data folder before data is loaded in MySQL.
Post-processing processes are manual methods that are used because these two difficulties cannot be fixed by the said approach. Thus, script dbf2sql.php is important for the conversion of DBF files into SQL scripts. CSV file is an intermediate step that can be avoided by using this method. Also, the table can be built and filled with data in the process. But the drawbacks are:
- No opportunity to set up FoxPro logic type mapping
- Lack of specific user-defined encoding
- Unintelligible sorting of potential problems of migration from FoxPro to MySQL.
Intelligent Converters, a software company has commercially produced FoxPro to MySQL tool. If you want to personalize all conversion process parameters, this conversion tool is suitable. From encoding process to processing of logical values, this tool has many functionalities. During the migration process, the features help to avoid manual efforts and intermediate steps.
In addition, the migration process is very quick. Local MySQL script file accepts data in the SQL statements. Then tables are generated and subsequently filled with data. When remote connections are not accepted by target MySQL, the next option is most suitable.
