Compared to other DBMS, migration of database from FoxPro to MySQL is somewhat simple and straightforward. It is also because of the absence if complex databases such as views, triggers, and procedures. The corresponding application stores data handling logic whereas FoxPro is used only as a storage. The lack of sophistication is the reason that most companies are moving their databases from FoxPro to MySQL database. But this task isn’t a totally simple type due to intricacies such as:
- Data types which are unmatched
The two possible values that can be found in FoxPro are ‘True’ and ‘False’ both of which are stored as symbols ‘T’ and ‘F’. The corresponding type BOOLEAN also accepts two possible values. However, it is synonymous to TINYINT(1). With both ‘T’ and ‘F’ designated with possible values of 1 and 0 respectively, this semantic equivalence requires that they are mapped that way. The original data “as is” should be preserved in some cases, and ENUM(‘T’,’F’) is the most appropriate type mapping.
- Different sets of character
Storage of encoding is exclusive to the header in the DBF file store whether incorrect or empty. The best way to mitigate this problem is to check that the conversion of data is without error in the codepage of the DBF file. Thus, the database administrator is advised to convert files again if the texts are incorrect.
What are the possible solutions that FoxPro to MySQL migration can offer?
Before importing the FoxPro tables, Comma Separated Values format is the best method to export the files before importing to MySQL. With dbf2csv, conversion of DBF files is done. But the latter part of the procedure is carried out using the ‘LOAD DATA INFILE’ of MySQL as:
- In the destination MySQL database, copy CSV files in the corresponding data folder. This is because only data contained in the folder is loaded for security reasons.
- Then, run the following statement:
LOAD DATA INFILE ‘student.csv’ INTO TABLE database.student
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;
It is important to implement post-processing steps so that solutions can be proffered to the challenges above. This is a manual solution.
A database administrator can convert DBF files into SQL scripts using dbf2sql.php. By using this, such administrator can avoid the CSV file step because of the creation and subsequent filling of tables. A drawback is the inability of the configuration of the FoxPro logic type mapping. Thus, preventing user-defined encoding. In addition, there is no iota of intelligence in resolving possible issues in migration from FoxPro to MySQL.
Intelligent Converters is a commercial company that creates professional tools such as FoxPro-to-MySQL tool for the customization of the process of conversions such as processing of logical values and the choice of the encoding process. With these features, manual efforts and intermediate steps are avoided during the process of migration. If the target MySQL refuses to accept remote connections,FoxPro to MySQL converter can export the source database into MySQL script file that can be loaded to the server using standard tools.
Visit official product page to learn more about FoxPro to MySQL converter: https://www.convert-in.com/dbf2sql.htm
