by David Simpson
He has developed several cross platform database applications including FmPro Migrator, which automates migrations from FileMaker to MySQL and other databases. He can be contacted via his website at www.fmpromigrator.com. He has also written articles for AdvisorMedia, O'Reilly and LinuxJournal.
FileMaker is unmatched in usability thus allowing even novices to quickly create useful database applications for themselves and other users. FileMaker solutions are often developed by individuals and departments who are working independently from a corporate IT department due to cost factors or reduced IT staffing. However as the number of users grows, it may be necessary to migrate a FileMaker database solution to MySQL. MySQL offers inexpensive hosting options and greater scalability which is comparable to an Oracle database (as shown in the well known eWeek benchmarks).
This article covers the procedures involved with migrating a FileMaker 6 database running on Windows to a MySQL database running on any platform. Additional info is included regarding changes to this procedure which are required to convert FileMaker 7 to MySQL.
Why Migrate From FileMaker to MySQL?
MySQL is the most popular open-source database available today with an estimated user base exceeding 5 million installations. This popularity has led to wide availability of MySQL database servers within businesses and by ISPs. Most ISPs offer economical MySQL database hosting with their web hosting plans, thus making MySQL a popular choice for web developers. FileMaker database hosting is generally available at a premium price from a limited number of web hosting providers.
MySQL also offers better scalability and features such as transactions and database replication. FileMaker Pro Unlimited or Server editions are generally limited to 250 (or fewer) simultaneous users, while MySQL can be expected to serve thousands of simultaneous users. In fact, a Ziff-Davis Media, Inc eWeek benchmark test has shown MySQL to have performance comparable to Oracle 9i.
High availability installations which include database replication and failover capability can be crucial for business critical database installations. FileMaker does not include any type of built-in replication functionality for high availability installations. MySQL does offer a built-in active/passive replication feature which is similar to an Oracle standby database configuration.
The FileMaker database has generally been known as a desktop oriented database with limited connectivity to the database from other applications. FileMaker ODBC connectivity is limited depending upon the platform upon which the FileMaker database is installed. With FileMaker Pro 5 - 6.0, a limited number of users were able to connect with the database through an ODBC/JDBC connection, as long as the connection was made from a Windows computer. FileMaker Pro 7 now limits database connectivity to the Windows host running the database, with no ODBC connectivity available from MacOS X clients. Full ODBC connectivity from Windows computers is reserved for the FileMaker 7 Advanced Server product. MySQL offers a variety of connectivity options for all platforms which can host a MySQL database. Applications can connect with a MySQL database through ODBC, Perl, PHP and Java. There are also no fixed limits to the number of hosts or users which can connect to the database.
Technical Challenges with Migration
Some of the technical challenges with manually migrating FileMaker databases include changing database and column names, dealing with large numbers of FileMaker fields, migrating text fields greater than 255 characters, and migrating images from FileMaker. The migration process described here involves the use of Perl programs to migrate the actual data via an ODBC connection between the FileMaker and MySQL databases.
FileMaker Database Name Issues - A FileMaker database file name can contain characters (i.e. spaces and special characters) which can't be used as a MySQL table name. The first task is to manually change the database name to remove any special characters from the filename. FileMaker will give a warning message if related files can't be found, but this won't affect the migration process because the contents of each FileMaker file get transferred individually to the MySQL database.
FileMaker Field Name Issues - FileMaker field names may also contain characters which would be illegal for an MySQL database column name. When creating the MySQL database table, special characters should be removed from field names and spaces should be removed or changed into underscore characters. MySQL column names should also be truncated to the MySQL mandated limit of 64 characters.
Too Many FileMaker Fields - FileMaker versions prior to FileMaker 7 do not include a built-in mechanism to keep track of variables while running a script or calculation. Therefore many FileMaker databases contain dozens of global fields used to store this type of information. FileMaker summary and calculation fields are also handled differently within a database like MySQL. Calculation features during record insertion are usually handled via the software which is providing the interface to the database. MySQL 5.0 will offer stored procedures and triggers in order to provide this functionality. The problem with having so many fields is that you can exceed the maximum number of columns which can be created within the MySQL table. It is generally possible to remove all global fields, unstored calculation fields and summary fields from the FileMaker database prior to starting the migration process. Removing or at least disabling unstored FileMaker calculation fields and summary fields is also advisable in order to prevent FileMaker from dropping the ODBC connection while waiting for these calculations to be completed. FileMaker will usually issue a "fetch forward" error when this occurs.
Migrating FileMaker Large Text Fields - FileMaker 6 can store as many as 64,000 characters within each text field. However the MySQL VARCHAR column only stores up to 255 characters. Fields containing more than 255 characters should be migrated to MySQL TEXT columns which can store up to 64K of data per record. FileMaker 7 text fields can contain up to 2Gb of text instead of the previous 64K limit, so this column type should be changed to LONGTEXT if this much data needs to be migrated.
Migrating FileMaker Repeating Fields - There is no data type within a MySQL database which is exactly equivalent to the Repeating Fields feature within FileMaker. Repeating fields within FileMaker will only be completely transferred into MySQL if the fields are text fields. FileMaker numeric fields which contain repeating values should be converted to text fields within FileMaker in order to transfer each of the repeating values. Otherwise, MySQL will only recognize the first value of a numeric field. Each repeating field entry is separated by an ASCII (29) character which is used by FileMaker Pro to separate values within repeating fields. For full support of this functionality, redesign of the FileMaker Pro database structure should be considered. This type of feature should be implemented via separate rows of data within a MySQL database with the child records having a common foreign key value pointing back to the primary key of the parent record.
Migrating FileMaker Container Fields - FileMaker Pro uses container fields for storing pictures, sound and QuickTime video. Data located within FileMaker Pro container fields is transferred into MySQL LONGBLOB type columns. There are some limitations which have been discovered while retrieving container field data from FileMaker Pro. I have only been able to determine the storage format for FileMaker JPEG images as the internal format is not well documented. Therefore only JPEG container field data is supported by the migration script shown in this article. All container fields include the text "HTTP/1.0" prefixed to the data. This header information is removed from the data, with the remaining JPEG data being transferred into the MySQL database table. Other types of container field data are also prefixed with the "HTTP/1.0" file header information which is followed by a JPEG icon representing the type of data contained within the field. So if sound information is stored within a container field, you will end up getting a JPEG image of a sound icon copied into the MySQL database. FileMaker appears to be unique in handling information storage in this manner. All other databases I have worked with simply store the exact binary information which you entered into the field. This feature of FileMaker appears to be related to the tight integration of the user interface with the database engine technology. There appears to be a desire by the FileMaker developers to always show some type of image representing the type of info within the field, even if the type of data does not lend itself to a visual display.
Migrating FileMaker Calculations - FileMaker field calculations and calculated summary values need to be re-engineered, usually as application specific code by the application which will be providing the graphical interface to the MySQL database. There are too many differences between how FileMaker handles these tasks to make it practical to automate the migration of calculation formulas.
Migrating FileMaker Scripts - The functionality incorporated within FileMaker scripts may be called by a remotely triggered script which could be scheduled via a CRON job on a UNIX/Linux/MacOS X server or a Scheduled Task on Windows.
The Migration Process
There are several steps to the FileMaker to MySQL migration process which I will discuss in this article. The flowchart in Figure 1 provides an overview of this process.
[Figure 1 - FileMaker to MySQL Migration Process]
Define MySQL Table Name - The MySQL table name will not necessarily be identical to the name of the FileMaker database file. FileMaker database filenames may include spaces and special characters which would not be usable as a MySQL table name. It is also a good idea to change the FileMaker database filename to match the MySQL table name because the FileMaker database name will be used within a SQL statement to transfer data to MySQL.
Define MySQL Column Names from FileMaker Fields - FileMaker field names may also include spaces and special characters which can't be used as MySQL column names, so they will need to be renamed prior to being used within MySQL. The original FileMaker field names don't need to be changed within the FileMaker database because the ODBC transfer script will be performing a "select * from database name" SQL query.
Determine Max Fieldsizes Within FileMaker Database - In order to determine the column sizes of the MySQL columns it is necessary to determine the maximum amount of data stored within each FileMaker field throughout all of the records in the database. If the MySQL columns are too small, the FileMaker data will be truncated during the data transfer. There is no feature within FileMaker which will provide this info, but I will show how you can write a Perl program to calculate this info.
Create MySQL Table - Once the column sizes have been determined, the MySQL table can be created. This is also when the final determination will be made concerning which column types will be used in the table. Each FileMaker 5/5.5/6.0 text field can store 64000 characters of information, and each FileMaker 7 text or container field can store 2Gb of info. However a typical text field in actual practice will generally contain much less info. Text fields containing fewer than 255 characters can be transferred to VARCHAR columns, text fields containing between 255 and 64000 characters can be transferred to TEXT columns, FileMaker 7 text fields can be transferred to LONGTEXT columns. [Please see Table 1 for more info.]
Transfer Data via ODBC from FileMaker to MySQL - After the MySQL table has been created, the data will be transferred via an ODBC connection between the FileMaker and MySQL databases. Another Perl program will be written in order to complete this data transfer. A Perl DBD::ODBC connection is made to a FileMaker 6 database running on MacOS X, MacOS 9 or Windows. The destination MySQL database may be located locally on any OS platform or at a remote location such as an ISP.
The FileMaker Example Database
The example database used for this article is a database named Example.fp5. This database includes a wide selection of fields so that we can see how the various data types get transferred to MySQL. The following table shows the FileMaker data types and the compatible column types used within the MySQL database.
FileMaker Field Type
|MySQL Column Type|
|Text||TEXT or LONGTEXT (if > 255 bytes)|
Date/Time - Time Data
[Table 1 - FileMaker and MySQL Data Types]
FileMaker Date/Time fields can store Date or Time information and FileMaker 7 even includes a new Timestamp field which can be used for storing more precise Time data. However FileMaker does not provide any external way to query the database to determine which type of data is actually stored within the field. Therefore it is necessary to either know something about how the FileMaker database is used or view the records within the FileMaker database in order to determine the type of stored data within the field.
Getting FileMaker Database Info
The first step in the migration process is to launch FileMaker 6, open the Example.fp5 database file and add a few records of sample data to the database. Make sure that the Local and Remote Data Access Companion plug ins are enabled and that file sharing is enabled for the Example.fp5 FileMaker database file. For this article, the FileMaker database filename (without the .fp5 extension) will become the MySQL database table name.
Select Define Fields from the File menu to bring up the Define Fields dialog. Each field within the FileMaker database will be listed in this dialog. Use this information to construct a list of MySQL column names.
[Figure 2 - FileMaker Define Fields Dialog]
Create the FileMaker ODBC DSN
Open the Windows Data Sources (ODBC) Control Panel to create a new FileMaker
6 System DSN. The Data Sources (ODBC) control panel is located within the Administrative
Tools folder of the Windows Control Panel.
Note: With Windows XP, it may be necessary to select the Classic View for the Control Panel in order to see the Administrative Tools folder.
[Figure 3 - Data Sources (ODBC) Control Panel]
This ODBC DSN should be created as a System DSN, so click on the System DSN
tab, then click the Add button.
Select the FileMaker Pro driver, then click the Finish button.
Note: If FileMaker 7 is being used as the source database then the DataDirect 32-BIT SequeLink 5.4 driver should be selected.
[Figure 4 - Create New FileMaker Datasource]
Enter the name of the ODBC DSN in the first field. It is not necessary to enter
the Remote Connection information unless the actual FileMaker database file
is being accessed from another computer. Click the Advanced tab of the FileMakerPro
ODBC Driver Setup panel.
[Figure 5 - Enter FileMaker ODBC DSN Name]
On the Advanced tab, change the Max Text Length from 255 to 65000. FileMaker 6 databases can store up to 64K of text within a text field, so this change in the ODBC Driver Setup panel insures that data is not truncated as it is read from the FileMaker database.
The Fetch Chunk Size determines how many records will be retrieved from the
FileMaker database at a time. When reading records from the FileMaker database
you will generally notice that 100 records are retrieved, then there will be
a pause while the next 100 records are read from the database. This process
will continue until all of the records have been read. The default Fetch Chunk
Size is 100, and this value should generally not be increased. Increasing the
ODBC Fetch Chunk Size can cause FileMaker to crash while serving database files.
In fact this parameter may need to be reduced to as few as 10 records if there
are more than 500 fields within the FileMaker database file.
Click the Ok button.
[Figure 6 - FileMaker ODBC DSN Advanced Parameters]
Using ODBC with FileMaker and Perl
FileMaker 5, 5.5 and 6.0 include an ODBC driver which is usable by 3rd party applications on Windows. FileMaker Pro 7 for Windows includes an ODBC driver which only allows a connection to the database from the localhost IP address - from the computer running the database. For more details about using ODBC with FileMaker 7, please see the FileMaker 7 section at the end of this article.
In this article I am providing a Perl DBD:ODBC program which runs on Windows and is able to access both the MySQL and FileMaker databases for the data migration. I am also providing the MaxFieldsize Perl script which will determine the maximum amount of data stored within each FileMaker field. The MySQL and FileMaker databases can be running on different platforms, but MySQL software and a FileMaker ODBC driver needs to be installed on Windows. For a MySQL database, this is easily accomplished by installing the MySQL database software on Windows.
ActiveState Perl needs to be installed on Windows along with the Perl DBI, DBD::ODBC and DBI::mysql modules. These Perl modules can be downloaded from www.cpan.org or downloaded and installed automatically with the following ActiveState PPM commands:
PPM> install DBI
PPM> install DBD-ODBC
PPM> install DBD-mysql
Checking FileMaker Field Sizes
Prior to creating the MySQL database table, it is necessary to determine the maximum amount of data stored within each field of the FileMaker database. This task is accomplished with the example_fmpro_max_fieldsize1.pl script shown in Listing 1. When this script runs, it reads each record from the FileMaker database and produces a text report file showing the name of each field and the maximum amount of data stored within the field among all of the records of the database.
[Listing 1 - example_fmpro_max_fieldsize1.pl Script]
This Perl script can be customized for a particular database migration by changing the ODBC DSN connection info along with the lists of column names and column types listed near the beginning of the script.
Now that the script has been written, the script should be run against the FileMaker database to gather info. Unlike Microsoft Access databases, FileMaker databases need to be open and sharing needs to be enabled within FileMaker in order to make an ODBC connection to the database.
Open the Windows Command Prompt by selecting Start > Programs > Accessories
> Command Prompt.
Use the cd command to navigate into the folder where your script is located.
[Figure 7- Opening Windows Command Prompt]
Run the example_fmpro_max_fieldsize1.pl program from the Windows command prompt to create the fieldsize report file.
After this program finishes, it will produce the example_fmpro_max_fieldsize_report.txt
file. Examine the contents of the example_fmpro_max_fieldsize_report.txt
file to determine whether there are more than 255 characters of information
stored within any of the text fields of the FileMaker database file. For this
article, the FileMaker field named location is used as an example
of a text field which contains more than 255 characters. This field will be
migrated to a MySQL TEXT column instead of a VARCHAR column.
Creating the MySQL Table
Listing 2 (below) shows the contents of the create table SQL code which will be used to create the equivalent MySQL table for the migrated FileMaker database.
[Listing 2 - example_create_table.sql MySQL Create Table SQL Script]
The example_create_table.sql script has been designed with the data obtained from the example_fmpro_max_fieldsize_report.txt report file.
This particular FileMaker example includes a primary key field named asset_id. Within the table creation SQL file, a primary key column has been created as UNSIGNED AUTO_INCREMENT NOT NULL, in order to provide an incrementing sequence upon record insertion. The table creation SQL code may be manually modified as needed prior to creating the MySQL database table.
The example_create_table1.sql file makes use of LONGBLOB columns for storing FileMaker container field data as large binary objects within the MySQL database table.
For this example, the MySQL database is running on a Windows server, but a
remote connection can be made to a MySQL database running on any operating system.
Execute the command listed at the top of this file to create the MySQL database table.
mysql test -u user1 < example_create_table1.sql
Once the table has been created, its structure can easily be verified with
the graphical MySQL Administrator utility as shown in Figure 8.
[Figure 8 - Verify MySQL Table Creation]
The ODBC Data Transfer Script
Listing 3 (below) shows the contents of the
script which will be used to transfer all of the records from the FileMaker
database to the new MySQL table.
[Table 3 - The ODBC Data Transfer Script]
There are a number of areas where this script can be customized for transferring data from different FileMaker databases to MySQL.
The db_connect strings for each database are easily changed to reflect the connection info for the FileMaker or MySQL databases which are being used.
The FileMaker select statement uses a "select *" statement to retrieve data from FileMaker. The use of a "select *" SQL statement allows this code to be unaffected by FileMaker field names containing spaces or special characters. This part of the code also doesn't need to be changed when migrating different FileMaker database files because the table name is specified with a variable which is defined just below the database connection strings.
The MySQL insert statement contains a list of each column into which data is inserted within the MySQL table with bind variable placeholders used for passing the data into the database.
Following the MySQL insert statement are a series of statements which call
fixup_container_data() subroutine. The
subroutine processes image data from FileMaker container fields and writes JPEG
data into MySQL LONGBLOB columns. This subroutine gets called for each FileMaker
container field within the database and receives as its single argument the
column number of the FileMaker database field (with column numbers starting
The last section of code which requires customization is the DBI bind parameter section. Each column number and column type is defined in this part of the code, along with comments in order to make it easier to keep track of the column names.
Migrating the Data
Now that the MySQL table has been created, the Perl DBI program which transfers
the data from FileMaker to MySQL can be run.
Run the example_fmpro_to_mysql_xfer_odbc1.pl script from the Windows
command prompt window to copy the data from the FileMaker database to the new
As the script runs, each processed record number will be displayed on the screen.
[Figure 9 - Migrating Data to MySQL]
The transferred data can be verified by using SQL commands in mysql or by using a graphical utility such as Navicat. Navicat is a graphical utility available for MacOS X, Windows and Linux for managing MySQL databases. Navicat also includes features to display and edit the contents of images in LONGBLOB columns, and text data within TEXT (memo) columns.
[Figure 10 - Verifying Migrated MySQL Data in Navicat]
This concludes the tasks involved with migrating a FileMaker 6 database to MySQL. The following sections include information regarding differences in the ODBC procedures involved with migrating a FileMaker 7 database to MySQL.
Installing the FileMaker 7 ODBC Driver
Note: The information within this section of the article is intended only for FileMaker 7 to MySQL migrations.
The introduction of FileMaker 7 represents the most significant upgrade for the FileMaker database product over the last ten years. This new version of the FileMaker database incorporates over 100 features with improvements to all major parts of the application. One of these changes includes the introduction of a new FileMaker database driver from DataDirect. This new FileMaker 7 ODBC driver is currently only available on the Windows platform.
Unlike previous versions of FileMaker Pro, the FileMaker Pro ODBC driver is no longer automatically installed along with the database software. A separate ODBC installer needs to be launched from the xDBC folder on the FileMaker 7 CD in order to install the ODBC driver. Launch the DataDirect setup.exe installer from the FileMaker 7 installation CD. The default installation options may be used during the install process.
[Figure 11 - FileMaker 7 ODBC Installer]
Due to a problem with the ODBC installer, the DataDirect SequeLink software
needs to be installed twice in order to work correctly on Windows.
Double-click the setup.exe installer again to remove the DataDirect SequeLink software. Use the default options to de-install the software.
Double-click the setup.exe installer again to install the DataDirect SequeLink software for the 2nd time. DataDirect 32Bit SequeLink will now show up under the list of drivers within the the Microsoft ODBC Administrator control panel.
Since we will be making an ODBC connection to a FileMaker 7 database, enable the ODBC/JDBC Sharing Companion for all users of the example.fp7 FileMaker 7 database.
[Figure 12 - Enabling FileMaker 7 ODBC/JDBC Sharing]
Creating the FileMaker 7 ODBC DSN
Open the Windows Data Sources (ODBC) Control Panel to create a new FileMaker
7 System DSN. Click the System DSN tab, then click the Add button.
[Figure 13 - Create New FileMaker 7 DataSource]
Select DataDirect 32Bit SequeLink 5.4 as the driver for the new FileMaker 7
data source, then click the Finish button.
Note: Do not select FileMaker Pro as the driver, because the FileMaker Pro driver is only used for the older versions of the FileMaker database.
[Figure 14 - Select DataDirect 32Bit SequeLink 5.4 Driver]
Enter the name of the ODBC DSN in the first field. This name will be used
as the name of the connection string used within the Perl scripts. Fill in the
SequeLink Server Host (127.0.0.1), and SequeLink Server Port (2399) fields as
shown in the image below.
Note: The operational features of the FileMaker 7 ODBC Driver are very different from previously shipped FileMaker ODBC drivers. The DataDirect SequeLink FileMaker driver included with FileMaker Pro 7 only permits ODBC connections to be made to a local FileMaker 7 database running on the same computer. This is why the SequeLink Server Host field must be filled in with the TCP/IP address of 127.0.0.1 or the hostname "localhost". The feature of allowing external computers to make an ODBC connection to a FileMaker 7 database is reserved for the FileMaker Server 7 Advanced product.
Click the ... button to the right of the Server Data Source field,
then select the name of the new FileMaker 7 database (example). Click the Ok
[Figure 15 - Enter SequeLink Driver Parameters for FileMaker 7]
In order to verify that a connection can be made to the FileMaker 7 database, it is a good idea to test the ODBC connection before attempting to use the new ODBC DSN. DataDirect has provided a helpful Test Connect button within their driver setup window which enables the testing of ODBC database connectivity.
Click the Test Connect button shown in Figure 16 to verify that a connection
can be made to the FileMaker 7 database.
Enter Admin as the username, with no password, then click the Ok button.
Click the Ok button to close the test results dialog box.
Note: If the connection test fails, verify that ODBC/JDBC sharing is turned on and that an older version of FileMaker is not running with the Local or Remote Data Access Companion plug-ins enabled.
[Figure 16 - Test FileMaker 7 ODBC DSN]
FileMaker Pro is a versatile and easy to use workgroup database application. But if you need functionality not included in FileMaker Pro such as better scalability, replication, or inexpensive hosting at an existing ISP, MySQL is an excellent alternative to consider. By using the migration process described in this article, database developers can utilize the best database for their needs. Databases can be easily prototyped and tested with FileMaker Pro and then migrated to MySQL if the need arises.