FileMaker to MySQL Migration

7/19/2004
by David Simpson

Author's Info:

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.

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

[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	VARCHAR(255)
Text	TEXT or LONGTEXT (if > 255 bytes)
Number	DOUBLE
Date/Time	DATE
Date/Time - Time Data	TIME
Container	LONGBLOB

[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

[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 6 - Enter FileMaker ODBC DSN Name
[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 7 - FileMaker ODBC DSN Advanced Parameters

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

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.

#!/usr/bin/perl

      # Script: example_fmpro_max_fieldsize1.pl

      # Features: This Perl program reads all of the

      # data from the FileMaker database

      # then produces a report showing

      # the maximum amount of data stored

      # within each field. This info is

      # used to provide guidance when sizing

      # the column widths in the migration

      # destination database.

      #

      # Requirements: 

      # Perl DBI module

      # Perl DBD::ODBC

      # FileMaker ODBC driver.

      # FileMaker must be running in Multi-User mode

      # with the Local and Remote Data Access Companions

      # enabled. The ODBC Max Text Length parameter

      # needs to be increased from 255 to 65000 for

      # FileMaker 5/6 versions.

      # The name of the FileMaker database must not

      # contain spaces or special characters.

      #

      # Database Connections:

      # FileMaker ODBC DSN Name: example_fmp_dsn

      #

      # Usage: perl example_fmpro_max_fieldsize1.pl

      #

      #

      # ---------------------- Revision History ---------------

      # Date By Changes

      # 7-18-2004 dsimpson Initial Release

      #

use strict; use DBI qw(:sql_types);

my $outputfile = 'example_fmpro_max_fieldsize_report.txt'; my $db_connect_string_fmpro = 'example_fmp_dsn'; my $filemaker_database_name = 'example'; my $schema_name = ''; my $schema_password = ''; my $debug=0; # DBI tracing enable/disable my $long_readlength = 100000; # maximum number of bytes for large text and container field data read from FileMaker - increase this value as needed my $record_count=0; my @rowdata = (); my $number_of_fields=44; my @list_of_fmpro_fields = qw(asset_id model item category cost date_purchased date_created date_modified total_cost serial_number location assigned_to date_placed purchased_from depreciation_life book_value remaining_life total_book_value depreciation total_depreciation picture date_assigned template_information_global created_by information date_check_in date_check_out date_due overdue_ check_out_days hilitelibrary hilitesortedby hilitecategory hiliteassignedto hiliteitem hilitemodel hiliteserialnumber hilitelocation assigned_display sample_calc time_modified type work first); my @list_of_fmpro_field_types = qw(number text text text number date/time date/time date/time number text text text date/time text number number number number number number container date/time text text text date/time date/time date/time text number container text container container container container container container number number date/time text text text); my @list_of_destination_field_types = qw(INT VARCHAR(255) VARCHAR(255) VARCHAR(255) DOUBLE DATE DATE DATE DOUBLE VARCHAR(255) TEXT VARCHAR(255) DATE VARCHAR(255) DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE LONGBLOB DATE VARCHAR(255) VARCHAR(255) VARCHAR(255) DATE DATE DATE VARCHAR(255) DOUBLE LONGBLOB VARCHAR(255) LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB DOUBLE DOUBLE TIME VARCHAR(255) VARCHAR(255) VARCHAR(255)); my @list_of_fmpro_field_lengths = qw(0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0); my $spaces_string1 = ''; my $spaces_string2 = ''; my $spaces_string3 = ''; my $spaces_string4 = ''; # ----------------------- create_output_spaces -------------------------- # This sub returns a string which contains the number of space characters specified # as an input parameter # inut parameters are: column width, actual data string

sub create_output_spaces($$) {

my ($column_width,$input_data_string)=@_; my $finished_string=''; my $number_of_spaces=0; my $input_data_string_length=0;

$input_data_string_length = length($input_data_string); $number_of_spaces = $column_width - $input_data_string_length;

if ($number_of_spaces > 0) { # cycle thru number of spaces to create for (my $count=0;$count < $number_of_spaces+1; $count++) { $finished_string = $finished_string . ' '; } } return $finished_string; } # ----------------------- create_output_spaces -----------------------

# ---------- get_column_size ----------- # This sub checks the size of data in the column # which has been passed in, and notes whether # it exceeds the highest value yet retrieved # from the database.

sub get_column_size { my $rowdata_count = $_[0]; # get rowdata array element number passed into this sub my $prefix_position=0; my $temp_length = length($rowdata[$rowdata_count]); if ($temp_length > $list_of_fmpro_field_lengths[$rowdata_count]) { # new length is longer than any previous length found - update summary info $list_of_fmpro_field_lengths[$rowdata_count] = $temp_length; } } # ---------- get_column_size -----------

my $fmpro_dbh = DBI->connect ("dbi:ODBC:$db_connect_string_fmpro", "$schema_name", "$schema_password", {RaiseError => 1, PrintError => 0, AutoCommit => 0}) or die "Can't connect to the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n"; $fmpro_dbh->{LongReadLen} = $long_readlength; $fmpro_dbh->{LongTruncOk} = 0;

if ($debug == 1) { # turn on DBI tracing unlink 'dbitrace.log' if -e 'dbitrace.log'; DBI->trace(2, 'dbitrace.log'); } # ----------- retrieve records from FileMaker my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");

$fmpro_sth->execute();

while ( @rowdata = $fmpro_sth->fetchrow_array()) {

# check amount of data in each column &get_column_size(0); &get_column_size(1); &get_column_size(2); &get_column_size(3); &get_column_size(4); &get_column_size(5); &get_column_size(6); &get_column_size(7); &get_column_size(8); &get_column_size(9); &get_column_size(10); &get_column_size(11); &get_column_size(12); &get_column_size(13); &get_column_size(14); &get_column_size(15); &get_column_size(16); &get_column_size(17); &get_column_size(18); &get_column_size(19); &get_column_size(20); &get_column_size(21); &get_column_size(22); &get_column_size(23); &get_column_size(24); &get_column_size(25); &get_column_size(26); &get_column_size(27); &get_column_size(28); &get_column_size(29); &get_column_size(30); &get_column_size(31); &get_column_size(32); &get_column_size(33); &get_column_size(34); &get_column_size(35); &get_column_size(36); &get_column_size(37); &get_column_size(38); &get_column_size(39); &get_column_size(40); &get_column_size(41); &get_column_size(42); &get_column_size(43);

$record_count++; print "Processed record# $record_count\n"; }

$fmpro_sth->finish();

# disconnect from FileMaker database $fmpro_dbh->disconnect or warn "Can't disconnect from the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n";

# open output file, overwrite old file open (FILE1,">$outputfile") || die ("Could not open output file $outputfile for writing.");

print FILE1 " example Field Length Summary Report\n"; print FILE1 "\n"; print FILE1 "Copyright 2004 by .com Solutions Inc.\n"; print FILE1 "\n"; print FILE1 "----------------------- Field Length Summary Report --------------------------\n"; print FILE1 "Renamed Field Name Size FileMaker Type Destination Type\n\n"; print "***********************************************\n"; print "Completed analyzing $record_count FileMaker records.\n"; print "***********************************************\n"; print " Field Length Summary Report\n"; print "***********************************************\n"; print "Number of Fields: $number_of_fields\n"; print "\n"; for (my $count=0;$count < $number_of_fields; $count++) { # columns 1 and 2 $spaces_string1 = create_output_spaces(63,$list_of_fmpro_fields[$count]); # columns 2 and 3 $spaces_string2 = create_output_spaces(10,$list_of_fmpro_field_lengths[$count]);

# columns 3 and 4 $spaces_string3 = create_output_spaces(20,$list_of_fmpro_field_types[$count]);

# columns 4 and 5 $spaces_string4 = create_output_spaces(20,$list_of_destination_field_types[$count]);

print "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]\n"; print FILE1 "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]$spaces_string2$list_of_fmpro_field_types[$count]$spaces_string3$list_of_destination_field_types[$count]$spaces_string4\n";

} # close the output file close (FILE1);

exit;

[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 12 - Opening Windows Command Prompt Menu Item

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

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

# Script: example_create_table1.sql

      # Features: This sql script creates the MySQL

      # table for the FileMaker data. 

      #

      # Notes:

      # This file only needs run if

      # the table does not already exist

      # in the destination database. 

      # The DBA should modify the table creation

      # commands as needed before running this file.

      # 

      # Usage: mysql test -u user1 < example_create_table1.sql

      #

      # Used By: run manually by the MySQL DBA on the database server

      #

      # ---------------------- Revision History ---------------

      # Date By Changes

      # 7-18-2004 dsimpson Initial Release

      #

      # specify database name

      use test;

# drop table command - commented out # DROP TABLE IF EXISTS example;

# create MySQL table to match structure of FileMaker file CREATE TABLE IF NOT EXISTS example ( asset_id INT UNSIGNED AUTO_INCREMENT NOT NULL, model VARCHAR(25) NULL, item VARCHAR(25) NULL, category VARCHAR(25) NULL, cost DOUBLE NULL, date_purchased DATE NULL, date_created DATE NULL, date_modified DATE NULL, total_cost DOUBLE NULL, serial_number VARCHAR(25) NULL, location TEXT NULL, assigned_to VARCHAR(25) NULL, date_placed DATE NULL, purchased_from VARCHAR(25) NULL, depreciation_life DOUBLE NULL, book_value DOUBLE NULL, remaining_life DOUBLE NULL, total_book_value DOUBLE NULL, depreciation DOUBLE NULL, total_depreciation DOUBLE NULL, picture LONGBLOB NULL, date_assigned DATE NULL, template_information_global VARCHAR(25) NULL, created_by VARCHAR(25) NULL, information VARCHAR(25) NULL, date_check_in DATE NULL, date_check_out DATE NULL, date_due DATE NULL, overdue_ VARCHAR(25) NULL, check_out_days DOUBLE NULL, hilitelibrary LONGBLOB NULL, hilitesortedby VARCHAR(25) NULL, hilitecategory LONGBLOB NULL, hiliteassignedto LONGBLOB NULL, hiliteitem LONGBLOB NULL, hilitemodel LONGBLOB NULL, hiliteserialnumber LONGBLOB NULL, hilitelocation LONGBLOB NULL, assigned_display DOUBLE NULL, sample_calc DOUBLE NULL, time_modified TIME NULL, type VARCHAR(25) NULL, work VARCHAR(25) NULL, first VARCHAR(25) NULL, PRIMARY KEY(asset_id) ) TYPE=MyISAM;

quit

[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 14 - Verify MySQL Table Creation

[Figure 8 - Verify MySQL Table Creation]

The ODBC Data Transfer Script

Listing 3 (below) shows the contents of the example_fmpro_to_mysql_xfer_odbc1.pl script which will be used to transfer all of the records from the FileMaker database to the new MySQL table.

#!/usr/bin/perl

      # Script: example_fmpro_to_mysql_xfer_odbc1.pl

      # Features: This Perl program copies all

      # of the records from the FileMaker

      # database, then inserts the records

      # into the MySQL database.

      #

      # Requirements: 

      # Perl DBI module

      # Perl DBD::ODBC module reads data from FileMaker

      # Perl DBD::mysql module writes data into MySQL

      # MySQL software must be installed on the

      # computer running this program.

      # FileMaker ODBC driver.

      # FileMaker must be running in Multi-User mode

      # with the Local and Remote Data Access Companions

      # enabled. The ODBC Max Text Length parameter

      # needs to be increased from 255 to 65000.

      # The name of the FileMaker database must not

      # contain spaces or special characters.

      #

      # Database Connections:

      # FileMaker ODBC DSN Name: example_fmp_dsn

      # MySQL Connection: database=test:host=localhost:port=3306

      #

      # Notes: 

      # This program handles large text

      # fields from FileMaker as Text

      # columns in the MySQL table.

      # FileMaker container fields are

      # written to MySQL LongBLOB columns.

      #

      # Usage: perl example_fmpro_to_mysql_xfer_odbc1.pl

      #

      # Used By: run manually by the MySQL DBA

      #

      # ---------------------- Revision History ---------------

      # Date By Changes

      # 7-18-2004 dsimpson Initial Release

      #

use strict; use DBI qw(:sql_types);

my $db_connect_string_mysql = 'database=test:host=localhost:port=3306'; my $db_connect_string_fmpro = 'example_fmp_dsn'; my $filemaker_database_name = 'example'; my $mysql_tablename = 'example'; my $schema_name = 'user1'; my $schema_password = 'user1pwd'; my $debug=0; # DBI tracing enable/disable my $long_readlength = 100000; # maximum number of bytes for Text, LongBLOB type data read from FileMaker - increase this value as needed my $record_count=0; my @rowdata = ();

# ---------- fixup_container_data ----------- # This sub removes the HTTP 1.0 data which usually # prefixes FileMaker Pro container field data.

sub fixup_container_data { my $rowdata_count = $_[0]; # get rowdata array element number passed into this sub my $prefix_position=0; my $temp_string=substr($rowdata[$rowdata_count],0,200); if (substr($rowdata[$rowdata_count],0,4) eq "HTTP" ) { # if 1st 4 characters of container field data contains the text "HTTP" then # this prefix data needs to be removed before insertion into the MySQL table ##print substr($rowdata[$rowdata_count],0,10);# debug***** $temp_string =~ /Content-length: /g; # find the position within the data which contains the "Content-length: " text $prefix_position = pos($temp_string); # loop until end of numeric value defining content-length is reached while (substr($temp_string,$prefix_position,1) =~ /\d/) { $prefix_position++; } $prefix_position += 4; ##print "**POs $prefix_position \n";# debug***** if ($prefix_position < 200) { # truncate the container field prefix info - only if within the 1st 200 bytes substr($rowdata[$rowdata_count],0,$prefix_position)=""; } } } # ---------- fixup_container_data -----------

my $fmpro_dbh = DBI->connect ("dbi:ODBC:$db_connect_string_fmpro", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 0}) or die "Can't connect to the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n"; $fmpro_dbh->{LongReadLen} = $long_readlength; $fmpro_dbh->{LongTruncOk} = 0;

if ($debug == 1) { # turn on DBI tracing unlink 'dbitrace.log' if -e 'dbitrace.log'; DBI->trace(2, 'dbitrace.log'); } my $mysql_dbh = DBI->connect ("dbi:mysql:$db_connect_string_mysql", "$schema_name", "$schema_password", {RaiseError => 1, PrintError => 1, AutoCommit => 0 }) or die "Can't connect to the MySQL $db_connect_string_mysql database: $DBI::errstr\n"; $mysql_dbh->{LongReadLen} = $long_readlength; $mysql_dbh->{LongTruncOk} = 0;

$mysql_dbh->do("SET OPTION SQL_BIG_TABLES = 1"); my $mysql_sth = '';

# ----------- retrieve records from FileMaker my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");

$fmpro_sth->execute();

while ( @rowdata = $fmpro_sth->fetchrow_array()) {

# ----------- insert data into MySQL $mysql_sth = $mysql_dbh->prepare("insert into $mysql_tablename (asset_id, model, item, category, cost, date_purchased, date_created, date_modified, total_cost, serial_number, location, assigned_to, date_placed, purchased_from, depreciation_life, book_value, remaining_life, total_book_value, depreciation, total_depreciation, picture, date_assigned, template_information_global, created_by, information, date_check_in, date_check_out, date_due, overdue_, check_out_days, hilitelibrary, hilitesortedby, hilitecategory, hiliteassignedto, hiliteitem, hilitemodel, hiliteserialnumber, hilitelocation, assigned_display, sample_calc, time_modified, type, work, first) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

# remove start of container field data added by FileMaker Pro &fixup_container_data(20); &fixup_container_data(30); &fixup_container_data(32); &fixup_container_data(33); &fixup_container_data(34); &fixup_container_data(35); &fixup_container_data(36); &fixup_container_data(37);

$mysql_sth->bind_param( 1,$rowdata[0],SQL_INTEGER); # asset_id - integer column $mysql_sth->bind_param( 2,$rowdata[1],SQL_VARCHAR); # model - varchar column $mysql_sth->bind_param( 3,$rowdata[2],SQL_VARCHAR); # item - varchar column $mysql_sth->bind_param( 4,$rowdata[3],SQL_VARCHAR); # category - varchar column $mysql_sth->bind_param( 5,$rowdata[4],SQL_DOUBLE); # cost - double column $mysql_sth->bind_param( 6,$rowdata[5],SQL_DATE); # date_purchased - date column $mysql_sth->bind_param( 7,$rowdata[6],SQL_DATE); # date_created - date column $mysql_sth->bind_param( 8,$rowdata[7],SQL_DATE); # date_modified - date column $mysql_sth->bind_param( 9,$rowdata[8],SQL_DOUBLE); # total_cost - double column $mysql_sth->bind_param( 10,$rowdata[9],SQL_VARCHAR); # serial_number - varchar column $mysql_sth->bind_param( 11,$rowdata[10],SQL_LONGVARCHAR); # location - text column $mysql_sth->bind_param( 12,$rowdata[11],SQL_VARCHAR); # assigned_to - varchar column $mysql_sth->bind_param( 13,$rowdata[12],SQL_DATE); # date_placed - date column $mysql_sth->bind_param( 14,$rowdata[13],SQL_VARCHAR); # purchased_from - varchar column $mysql_sth->bind_param( 15,$rowdata[14],SQL_DOUBLE); # depreciation_life - double column $mysql_sth->bind_param( 16,$rowdata[15],SQL_DOUBLE); # book_value - double column $mysql_sth->bind_param( 17,$rowdata[16],SQL_DOUBLE); # remaining_life - double column $mysql_sth->bind_param( 18,$rowdata[17],SQL_DOUBLE); # total_book_value - double column $mysql_sth->bind_param( 19,$rowdata[18],SQL_DOUBLE); # depreciation - double column $mysql_sth->bind_param( 20,$rowdata[19],SQL_DOUBLE); # total_depreciation - double column $mysql_sth->bind_param( 21,$rowdata[20],SQL_LONGVARBINARY); # picture - blob column $mysql_sth->bind_param( 22,$rowdata[21],SQL_DATE); # date_assigned - date column $mysql_sth->bind_param( 23,$rowdata[22],SQL_VARCHAR); # template_information_global - varchar column $mysql_sth->bind_param( 24,$rowdata[23],SQL_VARCHAR); # created_by - varchar column $mysql_sth->bind_param( 25,$rowdata[24],SQL_VARCHAR); # information - varchar column $mysql_sth->bind_param( 26,$rowdata[25],SQL_DATE); # date_check_in - date column $mysql_sth->bind_param( 27,$rowdata[26],SQL_DATE); # date_check_out - date column $mysql_sth->bind_param( 28,$rowdata[27],SQL_DATE); # date_due - date column $mysql_sth->bind_param( 29,$rowdata[28],SQL_VARCHAR); # overdue_ - varchar column $mysql_sth->bind_param( 30,$rowdata[29],SQL_DOUBLE); # check_out_days - double column $mysql_sth->bind_param( 31,$rowdata[30],SQL_LONGVARBINARY); # hilitelibrary - blob column $mysql_sth->bind_param( 32,$rowdata[31],SQL_VARCHAR); # hilitesortedby - varchar column $mysql_sth->bind_param( 33,$rowdata[32],SQL_LONGVARBINARY); # hilitecategory - blob column $mysql_sth->bind_param( 34,$rowdata[33],SQL_LONGVARBINARY); # hiliteassignedto - blob column $mysql_sth->bind_param( 35,$rowdata[34],SQL_LONGVARBINARY); # hiliteitem - blob column $mysql_sth->bind_param( 36,$rowdata[35],SQL_LONGVARBINARY); # hilitemodel - blob column $mysql_sth->bind_param( 37,$rowdata[36],SQL_LONGVARBINARY); # hiliteserialnumber - blob column $mysql_sth->bind_param( 38,$rowdata[37],SQL_LONGVARBINARY); # hilitelocation - blob column $mysql_sth->bind_param( 39,$rowdata[38],SQL_DOUBLE); # assigned_display - double column $mysql_sth->bind_param( 40,$rowdata[39],SQL_DOUBLE); # sample_calc - double column $mysql_sth->bind_param( 41,$rowdata[40],SQL_DATE); # time_modified - time column $mysql_sth->bind_param( 42,$rowdata[41],SQL_VARCHAR); # type - varchar column $mysql_sth->bind_param( 43,$rowdata[42],SQL_VARCHAR); # work - varchar column $mysql_sth->bind_param( 44,$rowdata[43],SQL_VARCHAR); # first - varchar column

$mysql_sth->execute() or warn $mysql_sth->errstr(); # check for error $record_count++; print "Processed record# $record_count\n"; }

$fmpro_sth->finish(); $mysql_sth->finish();

# disconnect from FileMaker database $fmpro_dbh->disconnect or warn "Can't disconnect from the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n";

# disconnect from MySQL database $mysql_dbh->disconnect or warn "Can't disconnect from the MySQL $db_connect_string_mysql database: $DBI::errstr\n";

print "***********************************************\n"; print "Completed inserting FileMaker records into MySQL database.\n"; print "***********************************************\n"; print "$record_count Records processed.\n"; print "***********************************************\n";

exit;

[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 the fixup_container_data() subroutine. The fixup_container_data() 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 at 0).

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

Figure 9 - Migrating Data to MySQL

[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

[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

[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

[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 button.

Figure 15 - Enter SequeLink Driver Parameters for FileMaker 7

[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]

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