Repgen Installgen FmPro Migrator CGIScripter

FileMaker to MySQL Migration with FmPro Migrator for Windows

FmPro Migrator Demo Available for download...

Bookmark This Page

5/15/2004
by David Simpson

Summary:

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 to MySQL running on any platform using FmPro Migrator for Windows. 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/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 implemented by FmPro Migrator 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. Fortunately FmPro Migrator handles this conversion process automatically when it generates migration scripts. But you do have to watch out for situations in which duplicate column names may be created. FmPro Migrator removes special characters from field names and changes spaces into underscore characters. MySQL column names also get truncated to the MySQL mandated limit of 64 characters. When migrating a FileMaker database containing hundreds or even thousands of fields, it is generally more efficient to create the first pass of migration scripts and then try to create a table with the resulting table creation code. If there are duplicate column names, the MySQL will provide an error message so that changes can be made within the original FileMaker database. The scripts can then be regenerated by FmPro Migrator to reflect these changes within FileMaker.

• 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. Future versions of MySQL 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 (roughly 255 - 1500 columns depending upon column size). 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 are considered to be Large Text fields as far as FmPro Migrator is concerned. Fields which are identified as Large Text fields are migrated by FmPro Migrator as MySQL TEXT columns which can store up to 64K of data per record. FileMaker 7 text fields can contain up to 4Gb of text instead of the previous 64K limit, so this column type is automatically changed from VARCHAR(255) to LONGTEXT if this much data needs to be migrated. The scripts generated by FmPro Migrator utilize a Perl DBI/DBD:ODBC connection between the FileMaker and MySQL databases in order to make it easier to migrate large amounts of text data.

• 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 scripts. 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. The Perl CGI scripts generated by FmPro Migrator can be generated to include default and calculated values in order to provide this functionality.

• 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.

How FmPro Migrator Works

FmPro Migrator is a graphical application available for MacOS X and Windows which generates database migration scripts to migrate FileMaker databases to a variety of destination database servers. This article focuses on using FmPro Migrator for Windows to migrate a FileMaker database to just one of those migration targets, which is MySQL. FmPro Migrator for Windows makes ODBC queries to the FileMaker database in order to obtain metadata information about the database. From this metadata info, migration scripts and report files are then produced which migrate the database structure and data to MySQL.

Utilizing an automated process to create migration scripts is often essential for a successful migration project. A FileMaker database may contain dozens or even thousands of fields. Field names often include illegal SQL characters, spaces and MySQL reserved words. Hours or even days of work can be saved by automating just the development of the table creation SQL code.

[Figure 1 - ODBC Metadata Query Process]

After creating the MySQL database table, an FmPro Migrator generated Perl program is used to transfer text and image data from FileMaker Pro to the new MySQL database table. 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.

[Figure 2 - FileMaker to MySQL Data Transfer Process]

Using ODBC with FileMaker

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.

FmPro Migrator creates a Perl DBD:ODBC program which runs on Windows and is able to access both the MySQL and FileMaker databases for the data migration. 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 an MySQL database, this is easily accomplished by installing the MySQL database software on Windows. For FileMaker 5.0 - 6.0, the ODBC driver software is installed along with the FileMaker database software. An ODBC DSN is then created for the FileMaker database connection.

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
PPM> install DBI
PPM> install DBD-ODBC
PPM> install DBD-mysql
PPM> quit

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.

[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 FmPro Migrator attempts to determine the type of stored data within the field based upon the name of the field. If a Date/Time field includes the text "time" within its name, it is migrated to a MySQL Time column. This behavior can be overridden by simply changing the field name within FileMaker so that FmPro Migrator doesn't see the text "time" within the name of 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. The FileMaker database filename will become the MySQL database table name.

Launch FmPro Migrator for Windows, click on the FileMaker folder tab then select FileMaker 6 as the source database and MySQL as the destination database. The ODBC DSN for the source database will be used by FmPro Migrator to obtain metadata from the FileMaker database. Either keep the default "example_fmp_dsn" ODBC DSN name listed in FmPro Migrator or change it to match an existing FileMaker ODBC System DSN on your computer.

Since I frequently perform migration projects for customers, I change the contents of the Customer field to reflect their name. The name within this field is used to create the Copyright information within each generated script, thus keeping my customer's legal department happy.

Click the Browse button to select an output directory for the migration scripts.

[Figure 3 - FileMaker Parameters]

Create the FileMaker ODBC DSN

Open the Windows Data Sources (ODBC) Control Panel to create a new FileMaker 6 System DSN to match the ODBC DSN name entered into FmPro Migrator for the source database. 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 4 - 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 would be selected.

[Figure 5 - Create New FileMaker Datasource]

Enter the name of the ODBC DSN in the first field. This name should exactly match the name entered into the Source Database ODBC DSN field within FmPro Migrator. 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 6 - 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 7 - FileMaker ODBC DSN Advanced Parameters]

Now that the FileMaker ODBC DSN has been created, FmPro Migrator can query the FileMaker database to obtain the metadata required for creating MySQL migration scripts. Click the Refresh button in FmPro Migrator. The example.fp5 database file opened in FileMaker 6 should show up in the Open Databases field.

[Figure 8 - FileMaker Open Databases]

Configuring MySQL Database Parameters

MySQL database parameters are entered on the FmPro Migrator "Other" tab. These parameters include hostname, TCP/IP port number, database name, username, and password information which will be used within the generated migration scripts.

[Figure 9 - MySQL Database Parameters]

Generating Migration Scripts

Press the Migrate button in FmPro Migrator to generate migration scripts and database documentation files. Scripts which start with the name of the original FileMaker database file(s) will then be created within the output directory as shown below.

[Figure 10 - Generated MySQL Migration Files]

If these scripts are not created, select the Status Window menu item from the FmPro Migrator File menu. The Status Window will display info about any problems which occurred during the script generation process.

[Figure 11 - FmPro Migrator Status Window]

There are 10 files created for the migration process, but we will only need to use the 5 files which are described in Table 2.

[Table 2 - Migration File Descriptions]

Checking 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 which is generated by FmPro Migrator.

Open the Windows Command Prompt by selecting Start > Programs > Accessories > Command Prompt.
Use the cd command to navigate into the folder of generated scripts.

[Figure 12 - Opening Windows Command Prompt]

Run the example_fmpro_max_fieldsize1.pl program from the Windows command prompt to create the fieldsize report file.

perl example_fmpro_max_fieldsize1.pl

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. To designate this field as a Large Text field, enter “location” within the FmPro Migrator Large Text Fields field. Then press the Migrate button to re-generate the migration scripts.

 

[Figure 13 - Defining Large Text Fields]

Creating the MySQL Table

Open the example_create_table1.sql file in a text editor. Using the fieldsize report you can determine the optimum size to use for each column of the new MySQL table. It is generally a good idea to reduce text column widths from the default value of VARCHAR(255) to be closer to the actual amount of data contained within the FileMaker database.

This particular FileMaker example includes a primary key field named asset_id. FmPro Migrator has automatically determined that this FileMaker database contains a primary key by looking for the first numeric field with the attributes of "Not Empty" and "Unique". 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 best strategy to follow is to make any column name changes within the original FileMaker database, then regenerate the scripts with FmPro Migrator. In most cases there is no reason to try to manually track the dependencies between the generated output files, so just let FmPro Migrator recreate these files for you.

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 14.

[Figure 14 - Verify MySQL Table Creation]

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 MySQL table.
As the script runs, each processed record number will be displayed on the screen.

[Figure 15 - 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 16 - 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. Some of these changes include the introduction of a new FileMaker database driver from DataDirect. This new FileMaker 7 ODBC driver is currently only available on the Windows platform.

FileMaker Pro 7 for Windows includes the DataDirect SequeLink ODBC driver which only allows a connection to the a FileMaker Pro 7 database from the localhost IP address - from the computer running the database. Unlike previous versions of FileMaker, the FileMaker Pro ODBC driver is no longer automatically installed along with the database software. A separate ODBC installer is 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 17 - 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 18 - 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 to match the default ODBC DSN name used by FmPro Migrator. Click the System DSN tab, then click the Add button.

[Figure 19 - 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 20 - Select DataDirect 32Bit SequeLink 5.4 Driver]

Enter the name of the ODBC DSN in the first field. This name should exactly match the ODBC DSN name which was entered into the ODBC DSN field for the Destination Database within FmPro Migrator. 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 button.

[Figure 21 - 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 21 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 22 - Test FileMaker 7 ODBC DSN]

Conclusion

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 simplifying the migration process from FileMaker Pro to MySQL, FmPro Migrator enables database developers to 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.

 

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices