Introduction

DBF-to-MySQL is a program to migrate FoxPro/DBase (DBF) databases to MySQL server. Depending on your privileges on the target MySQL server you can export DBF data into new database or overwrite the contents of existing MySQL database.

Features

  • All DBF data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Supports all FoxPro/DBase code pages
  • Converts individual tables (see Select Tables article for related information)
  • Exports FoxPro database into MySQL script file (see Customize Conversion article for related information)
  • Special approach for VServer users (see VServer User Notes article for related information)
  • Merges DBF data into an existing MySQL database
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Quick Launch
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Supported OS: Windows XP(SP2 or SP3)/2003/Vista/Server 2008/7/8/10
  • Necessary privileges to write into the destination database

Limitations

  • Does not convert indexes


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of DBF-to-MySQL converter called D2SAGENT.EXE. Find this file in the installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--charset=... | MySQL character set
--dest=... | MySQL database or dump file name
--dump | convert DBF database into dump file
--encoding=... | decode all text fields using DBase codepage specified (see the table below)
--engine=... | MySQL storage engine (MyISAM, MEMORY, InnoDB, BerkeleyDB, ARCHIVE, FEDERATED, etc). Please refer to MySQL documentation for the further information.
--help | display help message and exit
--log2int | convert DBase logical fields into MySQL TINYINT(1) according to ANSI standard (true=1, false=0)
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--port=... | MySQL port
--profile=... | path to a profile to load conversion settings from
--silent | use this option to disable program output
--skip_del | don't convert records marked as deleted
--src=... | path to DBF database
--tab_def | convert table definitions only
--tab_file=... | file containing table names to convert (one table name per line)

In the following example the program converts DBF database located in "c:\db 1" folder into MySQL database "db 1" on the remote MySQL server "mysqlhost":

D2SAGENT.EXE --src="c:\db 1" --dest="db 1" --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword

Notes:

  1. You can omit 'mysqlh' parameter to connect local MySQL server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. If you omit 'tab_name' parameter, it will be set to DBF database name
  4. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")

DBase Codepages

Usually code page is stored in the header of .dbf file and extracted by converter automatically. But if it is not happened, you may specify code page manually via '--encoding=...' command line parameter. Below is the list of acceptable values:
0 | Use default encoding (from header of .dbf file)
1 | U.S. MS-DOS
2 | International MS-DOS
3 | Windows ANSI
4 | Standard Macintosh
38 | 866 DOS Russian
87 | 1251 Windows ANSI
100 | Eastern European MS-DOS
101 | Russian MS-DOS
102 | Nordic MS-DOS
103 | Icelandic MS-DOS
104 | Kamenicky (Czech) MS-DOS
105 | Mazovia (Polish) MS-DOS
106 | Greek MS-DOS (437G)
107 | Turkish MS-DOS
150 | Russian Macintosh
151 | Eastern European Macintosh
152 | Greek Macintosh
200 | Eastern European Windows
201 | Russian Windows
202 | Turkish Windows
203 | Greek Windows

MySQL character sets

This parameter specifies correct encoding of text values in the destination MySQL database. It can have one of the following values:

value | Description | value | Description
big5 | Big5 Traditional Chinese | dec8 | DEC West European
cp850 | DOS West European | hp8 | HP West European
koi8r | KOI8-R Relcom Russian | latin1 | cp1252 West European
latin2 | ISO 8859-2 Central European | swe7 | 7bit Swedish
ascii | US ASCII | ujis | EUC-JP Japanese
sjis | Shift-JIS Japanese | hebrew | ISO 8859-8 Hebrew
tis620 | TIS620 Thai | euckr | EUC-KR Korean
koi8u | KOI8-U Ukrainian | gb2312 | GB2312 Simplified Chinese
greek | ISO 8859-7 Greek | cp1250 | Windows Central European
gbk | GBK Simplified Chinese | latin5 | ISO 8859-9 Turkish
armscii8 | ARMSCII-8 Armenian | utf8 | UTF-8 Unicode
ucs2 | UCS-2 Unicode | cp866 | DOS Russian
keybcs2 | DOS Kamenicky Czech-Slovak | macce | Mac Central European
macroman | Mac West European | cp852 | DOS Central European
latin7 | ISO 8859-13 Baltic | cp1251 | Windows Cyrillic
cp1256 | Windows Arabic | cp1257 | Windows Baltic
binary | Binary pseudo charset | geostd8 | GEOSTD8 Georgian
cp932 | SJIS for Windows Japanese | eucjpms | UJIS for Windows Japanese

Please refer to MySQL documentation to better understand the concept of character sets and collations.


Customize Conversion

DBF-to-MySQL converter provides two options to convert DBF database into MySQL data source:

  • Move to MySQL server directly - use this approach to export the contents of the source database into a database on the target MySQL server. You need MySQL server launched and also necessary privileges to write into a database on the target server. See Connect to MySQL Server article for related information.
  • Store into dump file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local SQL script file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

You can choose one of these approaches on the first page of DBF-to-MySQL wizard using the corresponding radio buttons.


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. DBF-to-MySQL converter is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run DBF-to-MySQL converter from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import DBF database contents into an existing MySQL database. See VServer User Notes article for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by DBF-to-MySQL converter. See Troubleshooting article to learn how to resolve this problem.


Connecting to MySQL over SSH

Database administrators and security people know that it is good practice to open only local access to MySQL server for security reasons. However, if you want to connect any of Intelligent Converters applications to MySQL server hosted on a Linux/Unix system, it is necessary to open remote access from your local IP address on the destination server. Obviously it makes MySQL server less secure. To improve the situation you can use SSH protocol for secure data transfer between local machine running Intelligent Converters application and MySQL server.

The most easy way of connecting MySQL through SSH is to use port-forwarding through an SSH tunnel. This technique is based on creating an alias that makes any client application think it's connecting to "localhost" machine, while it's really connecting to the remote server through the tunnel.

If you are using the command line ssh, the command would look like this:

ssh -L <localport>localhost<remoteport> <username>@<servername>

If you use the standard MySQL port, both "localport" and "remoteport" must be set to 3306. You can do the same via tools having graphical user interface like Putty or SecureCRT.

Once you've got the ssh tunnel going, you can open any of Intelligent Converters application and enter in the details for remote MySQL server, using 'localhost' as the server host. See below example of MySQL connection page for DBF-to-MySQL converter.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

DBF-to-MySQL converts FoxPro/DBase types into the most appropriate MySQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original FoxPro/DBase database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.


Conversion Settings

On this wizard screen you can customize the conversion process using the following settings:

  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
  • Convert logical values 'as is' - select this checkbox to preserve the original DBF format of boolean columns (true='T', false='F'). By default, all logical values will be mapped into MySQL TINYINT(1) according to ANSI standard (true=1, false=0)
  • Convert table definitions only - select this checkbox to convert only table definitions of FoxPro database. All the data will not be migrated.
  • Don't convert deleted records - tells the program to skip all DBF records marked as deleted

By default DBase files contain information about encoding. But sometimes this information is missing, therefore DBF-to-MySQL allows users to manually specify encoding for all such files via "Use Encoding" combo box.


Frequently Asked Questions

On this page you can find the most frequently asked questions regarding DBF-to-MySQL. If your question is not answered here, please contact us at service@convert-in.com

  • What kind of FoxPro/DBase data is converted to MySQL?
  • What are the system requirements for DBF-to-MySQL?
  • What kinds of DBase/FoxPro formats and MySQL versions are supported?
  • Does DBF-to-MySQL support automation?
  • How to bypass error "The program does not support authentication protocol requested by server..."
  • How to specify .dbf file(s) to convert in the command line version of DBF-to-MySQL (d2sagent.exe)?
  • It is possible to connect to MySQL over SSH with DBF-to-MySQL?
  • How to uninstall DBF-to-MySQL converter?

What kind of FoxPro/DBase data is converted to MySQL?
DBF-to-MySQL converts table structures with all necessary attributes and the data. All FoxPro/DBase data types are supported (including all types of memo objects). DBF-to-MySQL does not convert indexes. Demo version of the product converts only 5 records per table.
Go to Top

What are the system requirements for DBF-to-MySQL?
DBF-to-MySQL reads .dbf files and connects to MySQL server directly, that's why it does not require any 3rd party software installation. The product does not use ODBC or any other middleware libraries.
Go to Top

What kinds of DBase/FoxPro formats and MySQL versions are supported?
DBF-to-MySQL works with DBase III, DBase III Plus, DBase IV, DBase Level 5 and Level 7. The program supports SMT, FPT and DBT formats of MEMO files. Also, all versions of Windows and Unix/Linux MySQL are supported.
Go to Top

Does DBF-to-MySQL support automation?
DBF-to-MySQL converter does not support automation directly. However, there is console version of the program m2sagent.exe in the installation directory. It supports command line arguments and can be called from any script. So, it is possible to write a simple batch script and execute it recurrently using Windows Task Scheduler. Please refer to 'Command line' article of the product documentation to learn more about d2sagent.exe.
Go to Top

How to bypass error "The program does not support authentication protocol requested by server..."
MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by DBF-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with DBF-to-MySQL. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;
Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with DBF-to-MySQL.
Go to Top

How to specify .dbf file(s) to convert in the command line version of DBF-to-MySQL (d2sagent.exe)?
DBF-to-MySQL interprets folder as a database while .dbf files are tables. So, you should point to the folder with .dbf files to convert via '--src=...' parameter like this:

d2sagent.exe --src="c:\my dbf folder\"
Then you can specify certain .dbf file(s) to convert via parameter '--tab_file=...' that points to the file containing list of .dbf files WITHOUT extension (one name per line). For exmaple:
dbf_file_name_1
dbf_file_name_2
...
dbf_file_name_N
Go to Top

It is possible to connect to MySQL over SSH with DBF-to-MySQL?
Yes, DBF-to-MySQL supports SSH connection to MySQL server. Please refer to this article for the further information.
Go to Top

How to uninstall DBF-to-MySQL converter?
If you need to remove DBF-to-MySQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE dbf2sql.log
Go to Top


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and Overwriting

If you try to convert DBF database into an existing MySQL database, DBF-to-MySQL converter allows you to choose one of the following options: overwrite, merge or specify another destination name.

  • Select 'Overwrite' option to replace contents of the destination MySQL table with DBF data. Note that all data contained in MySQL table will be lost.
  • Select 'Merge' option to merge DBF data into the destination MySQL database. DBF-to-MySQL converter implements quite simple merging algorithm. It appends data to the corresponding MySQL table. Note that structures of DBF table and destination MySQL table must be identical.

If you decide to enter another destination name just click 'Cancel' button.


MySQL dump file settings

On this wizard screen you can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection characteristics (host, user name, password) and other conversion settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.d2s) file with left mouse button. This will cause DBF-to-MySQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Select Databases

On this wizard screen you should enter path to DBF files, MySQL database name or path to a dump file (depending on which conversion scenario you choose).


Select Tables

On this wizard page you can manage the list of DBF tables to convert. DBF-to-MySQL converter considers as a table every file with ".dbf" extension in the specified directory.

In the left part of the wizard page you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new DBF table for converting into MySQL format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available DBF tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

To edit selected table double click the corresponding item in "Selected tables" list box. Then the dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information.


Troubleshooting

While working with DBF-to-MySQL converter you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by DBF-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with DBF-to-MySQL converter. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with DBF-to-MySQL converter.

P: Error message "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating DBF data into a new MySQL database, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending DBF data to an existing MySQL database, you should remove the conflicting unique index in the destination MySQL database.

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server. See VServer User Notes article for related information.

P: I cannot uninstall DBF-to-MySQL.
R: If you need to remove DBF-to-MySQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE dbf2sql.log

P: Indexes are missing in the resulting MySQL database.
R: Currrent version of DBF-to-MySQL does not convert indexes.

P: Some table names and/or field names in the resulting MySQL database differ from the original.
R: DBF and MySQL accept different sets of characters for table names and field names. When DBF-to-MySQL converts a name containing prohibited characters it replaces them with '_' in the destination database.

P: National symbols like á, â, ã, ç, é, î, ï, ô, ò, õ, ú, ù and others are not converted properly.
R: DBF-to-MySQL uses the codepage given in header of DBF file to interpret text fields of the tables. However, sometimes DBF file stores incorrect value of codepage. For such situations DBF-to-MySQL allows users to specify codepage that will be used to interpret text fields instead of the built-in one. You can do this via "Use Encoding" combobox on "Step 2 of 5" wizard page.

P: I see the error message "$filename$: Memo field not found"
R: In FoxPro/DBase format data of memo fields is stores in separate file. It is expected that the corresponding memo file is in the same folder as .dbf file and has the same name with one of the following extensions: .dbt, .fpt, .smt. Make sure there is the appropriate memo file in the same folder where .dbf file is located. Copy it from another folder if necessary.

P: Error message "Access denied for user: <user name> to database <database>"
R: If the destination database already exists, you should have necessary privileges to add/remove tables. If the destination database doesn't exist, you should have sufficient privileges to create new databases on the target MySQL server. See VServer User Notes article for related information.

If you came across some other kind of problem while using DBF-to-MySQL converter, please contact our support department at service@convert-in.com


VServer User Notes

If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

  1. ask your Virtual Server administrator to create blank database for you
  2. run DBF-to-MySQL converter and select this database as the conversion target
  3. click "Yes" when you will be asked if you'd like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source DBF database will be imported into the specified MySQL database.


Contents

Command Line

Connecting to MySQL over SSH

Connect to MySQL Server

Conversion Settings

Customize Conversion

Edit Table

Frequently Asked Questions

Importing dump files

Introduction

Merging and Overwriting

MySQL dump file settings

Profile

Quick Launch

Select Databases

Select Tables

Troubleshooting

VServer User Notes