How to migrate from FileMaker
Pro to MySQL
This older article was written before the addition of the PHP Migration feature along with improved functionality in the FmPro Migrator user interface. The process is much easier today!
How can FmPro Migrator help you?
Note: Additional information is contained within the <database
name>_instructions.txt file created during the file generation process.
The <database name>_instructions.txt file contains the actual
file names created by FmPro Migrator. This document uses an example
FileMaker Pro database named "example", the file created for
your database will use the name of the FileMaker database you are converting.
Note: FmPro Migrator for Windows requires that Perl be
installed on your Windows computer before generating scripts. Activestate
Perl may be downloaded from www.activestate.com. FmPro Migrator for
MacOS X utilizes the version of Perl which was automatically installed
with your operating system.
1) Fill in the fields within the FileMaker and Other tabs of the FmPro
Migrator application.
For Windows, enter the FileMaker ODBC DSN on the FileMaker tab. Make
sure that the Local and Remote Data Access Companion plug-ins are enabled
for each FileMaker database. Each FileMaker database should be configured
with no password required for access to the entire database, while FmPro
Migrator is gathering info about the database.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open one (or multiple) FileMaker database files, then press the Refresh
button.
Note: If any FileMaker database files contain spaces, these should be
removed before opening the file. [For Windows, enter the ODBC DSN name
and select your FileMaker database version from the Source Database
menu prior to pressing the Refresh button.]
5) Press the Migrate button to generate the conversion scripts and database
documentation files.
6) Examine the example_create_table1.sql file to determine if any changes
need to be made. The MySQL table columns created as VARCHAR(255) should
be modified as needed prior to creating the table. These columns should
only be created as large as needed in order to make it possible to create
indexes in the future. Transfer this file to the MySQL server and execute
this code from within the mysql client application to create the table
which will contain the FileMaker data. [If a migration is done from
FileMaker 7.0v1 to MySQL with FmPro Migrator for Windows, please see
the troubleshooting notes for more info about column type issues.]
7) Change the name of the FileMaker Pro database to remove any spaces
or special characters, otherwise the SQL query which retrieves data
from FileMaker within the example_fmpro_to_mysql_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts.
8) Transfer the example_fmpro_to_mysql_xfer_odbc1.pl program to a Windows
server. At the present time the example_fmpro_to_mysql_xfer_odbc1.pl
Perl script needs to be run from a Windows server due to the lack of
an ODBC driver for Mac OS X.
9) Install the Perl DBI, DBD::ODBC and DBD::mysql modules on the Windows
server along with the FileMaker ODBC Driver. (The DBI/DBD::ODBC modules
can be downloaded from www.cpan.org) or installed with Activestate PPM.
Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> install DBD-mysql
PPM> quit
10) Create the example_fmpro_odbc_dsn entry in the ODBC
Control Panel. Change the configuration of the Max Text length parameter
from 255 to 65000 in the Advanced tab of the FileMaker DSN.
11) Enable the Local and Remote Data Access Companions within the FileMaker
application preferences dialog. Enable Multi-User access and both the
Local and Remote Data Access Companions by selecting Sharing from the
File menu of each database file. Each database should allow complete
access without requiring a password in order for FmPro Migrator to read
the structure of each database file.
12) Execute the example_fmpro_to_mysql_xfer_odbc1.pl program to transfer
the data from FileMaker to MySQL via an ODBC and DBD::mysql network
connections. Note: It may not be possible to use this process to transfer
data to a MySQL database located at your ISP. For security reasons,
it may not be possible to access the MySQL database via an external
network connection. See the Usage Notes for more info concerning how
to complete this type of data transfer.
Use the following instructions if there are repeating
fields which need transferred from FileMaker to SQL Server.
13) If repeating fields need to be extracted from the FileMaker database,
use the example_repeating_fields_create_table1.sql SQL script to create
the MySQL example_repeating table for the repeating fields data. The
column sizes defined within this script should be adjusted as necessary
prior to creating this table.
14) Change each FileMaker repeating field to be a TEXT field so that
each of the repeating values will be transferred properly. (Note: Do
not make this change prior to generating the migration scripts or the
repeating fields will be created with the wrong datatypes.)
15) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer
the repeating fields data from FileMaker to MySQL via an ODBC network
connection.
16) Execute the example_drop_repeating_fields_columns1.sql SQL script
to drop the unneeded repeating fields columns from the MySQL example
table.
Note: There are additional descriptive comments located
at the top of each output file which is generated.
---------- Usage Instructions - Image Export Script ----------
[Enterprise Edition feature]
The example_fmpro_image_export1.pl Perl script provides a JPEG image
export feature for FileMaker Pro databases. This script exports one
specified container field as a JPEG file into a user-defined directory
on the computer where this script is running. The name used for writing
the JPEG file is retrieved from a user-specified FileMaker database
field.
1) Fill in the fields of the FileMaker and either the Oracle or Other
tabs of the FmPro Migrator application.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open the FileMaker database file.
5) Press the Migrate button to generate the migration scripts and database
documentation files.
6) Create the example_fmpro_dsn ODBC DSN entry in the ODBC Control Panel.
Change the configuration of the Max Text length parameter from 255 to
65000 in the Advanced tab of the FileMaker DSN.
7) Make the following changes within the example_fmpro_image_export1.pl
Perl script in order to specify the name of the container field, filename
field and destination directory.
The following example shows that the container field has been named
"image_field", the filename field has been named "filename_field"
and the output directory is named "my_images" at the top level
of the C: drive. The output directory needs to be specified using \
directory separators on Windows and needs to contain a trailing directory
separator at the end of the directory name.
--------- Example code follows
my $fmpro_image_data_field = 'image_field';
my $fmpro_image_filename_field = 'filename_field';
my $image_output_directory_path = 'c:\\my_images\\';
---------
8) Execute the example_fmpro_image_export1.pl Perl script on a PC running
Windows (or Vitual PC running on a Macintosh) as follows:
perl example_fmpro_image_export1.pl
The JPEG images will be exported from the FileMaker database
and written to the output directory. Repeat steps 7 and 8 as needed
if there are multiple container fields which need exported.
Additional Usage Notes
Time Fields
Both MySQL and FileMaker Pro databases support Time fields. When FmPro
Migrator uses AppleScript to retrieve field type info from FileMaker
Pro, it is not possible to automatically determine whether the field
contains date or time information. Therefore FmPro Migrator assigns
the field type as a time field if the text "time" is contained
within the field name. Otherwise the field type is assigned to be a
MySQL date format field. If a field is mistakenly assigned to be a time
format field by FmPro Migrator, then this automated behavior can be
overridden by simply changing the name of the field so that the text
"time" does not appear within the field name.
Date Field Format
MySQL utilizes a date format of YYYY-MM-DD which is different from the
default FileMaker Pro format of MM-DD-YYYY. FmPro Migrator automatically
converts data from the MM-DD-YYYY format used by FileMaker Pro to the
YYYY-MM-DD format used by MySQL.
Large Text Fields
FileMaker fields containing more than 255 characters of text will be
converted to MySQL text columns when converted into the MySQL database.
The list of these column names should be entered into the Large Text
Fields field. Data is transferred between FileMaker and MySQL by the
example_fmpro_to_mysql_xfer_odbc1.pl program. This program makes use
of bind variables to specify field names and ODBC field types.
Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to
be transferred to the MySQL database, these fields should be removed
before generating the conversion scripts. This can be easily accomplished
by making a copy of the FileMaker database, then removing the extra
fields from the copied database file. This technique reduces the chance
of making a mistake by manually editing the files generated by FmPro
Migrator.
ODBC driver and client software installation
The best way to transfer data in large text fields and FileMaker Container
fields is by using the example_fmpro_to_mysql_xfer_odbc1.pl program.
This program is generated by FmPro Migrator based upon the specified
FileMaker database structure in order to facilitate the transfer of
data to MySQL.
The example_fmpro_to_mysql_xfer_odbc1.pl program transfers data for
all FileMaker field types including text fields up to 64000 bytes and
JPEG image data from Container fields. The program makes use of bind
variables to specify field names and ODBC field types. The example_fmpro_to_mysql_xfer_odbc1.pl
program needs to be installed on a Windows server which has MySQL client
(or server) software, Perl, Perl DBI module, Perl DBD::ODBC module,
Perl DBD::mysql module and FileMaker Pro ODBC driver software installed.
This is due to the lack of a FileMaker Pro ODBC driver for Mac OS X.
The example_fmpro_to_mysql_xfer_odbc1.pl script running on the PC can
then read the data from FileMaker Pro (hosted on either Mac OS, Mac
OS X or Windows) and then be written into a MySQL database running on
any platform (including Mac OS X, Windows, Solaris etc).
Repeating Fields
If repeating fields are detected within the FileMaker database, two
extra scripts will be created in order to extract the repeating fields
data from FileMaker to MySQL. The example_repeating_fields_create_table1.sql
creates a new database table for the related data contained within the
repeating fields. The example_repeating_fields_xfer_odbc1.pl should
then be run in order to extract the repeating fields data into the new
example_repeating database table.
Notes:
It is necessary for FmPro Migrator to accurately determine the primary
key field within the FileMaker database in order to properly generate
the repeating fields extraction scripts. The primary key for the primary
database table is used to reference each repeating field record. Therefore
at least one field in the main database table must be set within FileMaker
with the UNIQUE and NOT EMPTY validation parameters. It is recommended
that this field be a numeric field using an auto-enter serial number
within FileMaker. This field will then be used as the primary key column
for the transferred data.
In order for all repeating field data values to be transferred from
FileMaker, the data type within FileMaker must be a Text field. Each
repeating field entry is separated by an ASCII (29) character which
is used by FileMaker Pro to separate values within repeating fields.
The example_repeating_fields_xfer_odbc1.pl script splits the data values
into separate records by using the ASCII (29) character as a record
delimiter and writes out each repeating field value into a new record
within the example_repeating table.
If repeating values are present within FileMaker numeric, date or time
fields, only the first value will be transferred to the destination
database. Therefore each repeating field needs to be converted to Text
fields within the Define Fields dialog box within FileMaker. This needs
to be done after creating the migration scripts. This change should
not be made within FileMaker before the migration scripts are created,
or the repeating field database table will be created with the wrong
datatypes (i.e. all fields will be TEXT).
FileMaker container fields which are configured as repeating fields
cannot be extracted.
Relationships
FileMaker Pro relationships are not automatically traversed and converted
by FmPro Migrator because full pathname info is not available for each
file. The FileMaker Pro developer should manually open each related
file and then FmPro Migrator STD Edition will create conversion scripts
for each open file. Please note that it may not be necessary to manually
open each related file because some files may be opened automatically
in the background by FileMaker. In this case, no additional action is
required. FmPro Migrator Lite edition requires the opening of each file
individually prior to starting the conversion process because only one
file at a time is processed.
Container Fields
FileMaker Pro uses Container fields for storing pictures, sound and
QuickTime video. Data located within FileMaker Pro Container fields
is transferred to MySQL LONGBLOB columns. There are some limitations
which have been discovered while retrieving Container field data from
FileMaker Pro. The only Container field data supported by transfer with
FmPro Migrator is JPEG image data. 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 file data in the field
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.
FileMaker Pro does not support writing Container field
data into a FileMaker Pro database via an ODBC connection on either
Windows or Macintosh systems.
Container Field Workaround:
One workaround to the issue of transferring container field data is
to store only a pathname to the data within the FileMaker Pro database.
The example_fmpro_to_mysql_xfer_odbc1.pl program can be modified to
read the pathname from the FileMaker Pro database, then read the binary
data directly from a local or remotely shared directory. An example
of this type of modification is included in the example_fmpro_to_mysql_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator
can work with FileMaker Pro databases running on MacOS X or Windows
servers for the data transfer phase of the conversion process. FmPro
Migrator can retrieve database structure information from stand-alone
applications created for the Windows platform if the file is opened
within FileMaker.
Working with
Stand-alone Solutions Files - Stand-alone solution files can usually
be opened on Windows computers by changing the custom file extension
used by FileMaker Developer to create the files. Changing the extension
to ".fp5" and opening the file if the file was originally
created with FileMaker 5/5.5/6 software. On MacOS X, dragging the file
onto the open FileMaker application will also open the file for processing.
Long FileMaker Field Names - FileMaker Pro allows field
names to be up to 60 characters long, and as of MySQL 3.21.20 column
names may be up to 64 characters long. Therefore FmPro Migrator does
not need to truncate table name lengths. Spaces are replaced with underscore
characters and special characters are removed from the name. Note: FmPro
Migrator is designed to support MySQL 3.23.50 (April 2002) and higher
versions.
Primary Key Determination -
FmPro Migrator examines the structure of the FileMaker Pro database
in order to determine which column should represent the primary key
for the MySQL database table. The primary key column of the MySQL database
table will be migrated as an auto-increment UNSIGNED INTEGER column.
FmPro Migrator selects the first FileMaker Pro field which is configured
with UNIQUE and NOT NULL options as the primary key. This determination
is made by examining each field option in the field creation order,
as is listed on the database structure report. To insure that FmPro
Migrator selects the correct field as the primary key, it may be necessary
to temporarily disable either the UNIQUE or NOT NULL options for other
database fields while generating conversion scripts
Transferring Data to a Remote MySQL Database:
It is common to have access to a MySQL database remotely located at
an ISP. However it may not be possible to use the example_fmpro_to_mysql_xfer_odbc1.pl
program to directly transfer your FileMaker Pro data to the remote MySQL
database. This is due to the requirement for the example_fmpro_to_mysql_xfer_odbc1.pl
program make a direct network connection to the MySQL database. For
security reasons, your ISP may block direct network access to the MySQL
databases hosted at their facility. There are two options for resolving
this situation:
Option #1 - Option #1 can be used if container field data
is not being migrated from FileMaker Pro to MySQL. The first step is
to use either the example_create_inserts_from_tab1.pl or example_create_inserts_from_csv1.pl
program to create the example_insert_data1.sql file. If necessary, use
the example_create_table1.sql file to create the MySQL database table.
Then use the example_insert_data1.sql file to insert the data into the
local MySQL database. Once the data has been verified in the local MySQL
database, use mysqldump to export the data to a file. Transfer the file
created by mysqldump to your ISP account and import the file with mysqlimport.
Option #2 - Option #2 needs to be used if FileMaker Pro
container field data has been migrated into the MySQL database. Create
the local MySQL database table using the example_create_table1.sql.
Use the example_fmpro_to_mysql_xfer_odbc1.pl Perl program to transfer
data from FileMaker Pro to the local MySQL database. Once the data has
been verified in the local MySQL database, shutdown the MySQL database.
Then tar/gzip the MySQL datafiles (*.frm, *.MYD, and *.MYI files) for
the relevant database table(s) which need transferred to the remote
MySQL database. Re-create the directory for the table at the remote
MySQL database server, then untar/gunzip the files into this directory.
This procedure can be used regardless of the machine architecture for
MySQL 3.23+ versions of MySQL. For more info concerning this process,
please see the MySQL documentation.
Report Differences
FmPro Migrator for Windows generates a slightly different database report
compared to FmPro Migrator for MacOS X. FmPro Migrator for Windows utilizes
a ODBC connection to the FileMaker database since AppleScript is not
available for Windows. The only information available about the database
thru an ODBC connection is:
The Name of the Database
The List of Field Names
The Type of each Field
The Empty Ok attribute for each Field
The following information listed on the report consists
of default values used to fill in the report:
Field IDs (always an incrementing value)
Unique Values (always set to Not Unique)
Repeating Values (always set to No)
Global Values (always set to No)
Access Status for each field (always set to read/write)
Protection Status for each field (always set to formulas/protected)
Calculation for each field (always blank)
Script Names (none listed)
Layout Names (only 1 listed - named All Fields, containing all fields)
