FmPro
Migrator Features and Benefits
FmPro
Migrator Demo Available for download...
Bookmark
This Page
File: contact_management_instructions1.txt
Features: This file contains the setup, usage
and troubleshooting instructions
for FmPro Migrator.
Used By: the MySQL DBA and FileMaker developer
Copyright 2003 by .com Solutions Inc.
This output file was created by FmPro Migrator version
1.23 on Thu Apr 3 12:19:39 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
----------------------- Overview -----------------------
There are multiple versions of FmPro Migrator with differing feature
sets. This file was produced by FmPro Migrator STD Edition.
These features are summarized below:
Demo Edition - The Demo edition creates output files via a set of internally
stored parameters. The resulting output files are representative of
the typical files which would be created by the Lite Edition of the
application.
Lite Edition - The Lite Edition supports the conversion
of FileMaker Pro database data into SQL INSERT statements for insertion
into a MySQL database table. This conversion method is suitable for
small datasets which do not involve text fields over 255 characters
each. The conversion of data from FileMaker Container fields is not
supported.
The Lite Edition creates conversion scripts for the 1st database listed
within the Open Databases field of FmPro Migrator.
STD Edition - The STD Edition supports all of the
features of the Lite Edition plus the additional features of transferring
large text fields and FileMaker Container fields. FileMaker supports
as many as 64000 characters of text within a text field - which greatly
exceeds the standard 255 characters allowed within a MySQL VARCHAR column.
FileMaker Container field data may also be converted via the Perl DBI/DBD::mysql
program which is created by the STD Edition.
The STD edition provides the convenience of automatically generating
scripts for each open FileMaker Pro database (up to 50).
----------------------- Theory of Operation -----------------------
FmPro Migrator uses Apple Events via AppleScript to query the FileMaker
Pro application for information about each of the database files which
are currently open. The information gathered from each database is summarized
within 2 report files. (Info concerning Auto-Enter and Validation options
are not gathered during this process because this info is not available
via AppleScript.) This information is also used to generate Perl and
SQL scripts used for creating MySQL database tables and moving the data
from FileMaker to MySQL. FmPro Migrator Lite Edition makes use of tab
separated or comma separated value data files exported from FileMaker
Pro. The exported data is converted into SQL INSERT statements used
to insert the data into the MySQL database. This data transfer method
is intended for transferring small amounts of record data for a relatively
small number of records.
FmPro Migrator STD Edition generates a Perl DBI/DBD::mysql
program which reads the data from FileMaker Pro via an ODBC connection
and inserts the data into MySQL via a Perl DBI/DBD::mysql connection.
This Perl program also supports transferring large text fields and Container
field information from FileMaker Pro into MySQL.
----------------------- Usage Instructions -----------------------
1) Fill in the fields within the FileMaker and MySQL 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 one (or multiple) FileMaker database files.
5) Press the Migrate button to generate the conversion scripts and database
documentation files.
6) Examine the contact_management_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.
7A) If using the Lite edition of FmPro Migrator, decide whether to perform
a tab delimited file export or a comma separated file export. Export
the FileMaker data using one of the following two filenames: contact_managem_export_data.tab
or
contact_managem_export_data.csv
It is important to export the FileMaker data in the same field order
listed in the contact_management_report_mysql file. Otherwise the data
will not be inserted into the correct MySQL database columns.
8A) Depending upon the type of file export performed, run either the
contact_management_create_inserts_from_tab1.pl or contact_management_create_inserts_from_csv1.pl
program to create the contact_management_insert_data1.sql file.
9A) Transfer the contact_management_insert_data1.sql file via an ASCII
FTP transfer to the MySQL server and execute it from within the mysql
client application. Alternately, if MySQL client or server software
is installed on Mac OS X, mysql can make a remote connection to any
MySQL server which is available by a network connection.
7B) 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 contact_management_fmpro_to_mysql_xfer_odbc1.pl
program will fail. Then re-generate the conversion scripts.
8B) If using the STD edition of FmPro Migrator, transfer the contact_management_fmpro_to_mysql_xfer_odbc1.pl
program to a Windows server. At the present time the contact_management_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.
9B) 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
10B) Create the contact_management_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.
11B) 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.
12B) Execute the contact_management_fmpro_to_mysql_xfer_odbc1.pl program
to transfer the data from FileMaker to MySQL via an ODBC and DBD::mysql
network connections. Note: This process can't generally be used to transfer
data to a MySQL database located at your ISP. For security reasons,
it should 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.
----------------------- FileMaker Folder Tab - Field
Descriptions
The FileMaker folder tab provides general info and FileMaker specific
info about the conversion process.
Click the Browse button to select an existing or
to create a new folder which will create the conversion scripts and
reports created by FmPro Migrator. You must have write access to the
output directory selected, otherwise an error dialog will be displayed.
[STD edition feature] Click the Select button then
select the name of the FileMaker application which will appear in the
FileMaker App Name field (if necessary). The name within this field
should represent the name of the FileMaker application or stand-alone
solution file as it appears within the Finder. In most cases, this field
will not need to be changed. However if you desire to retrieve database
structure information from a stand-alone FileMaker solution file, enter
the Finder name of the solution in this field. FmPro Migrator uses this
information to send AppleScript commands to FileMaker in order to retrieve
the structure information from the FileMaker database. If you select
an invalid program name, the Open Databases field will display "Compiler
Error" after pressing the Refresh button.
Open one or more FileMaker database files, then
press the Refresh button. All of the FileMaker databases which are open
will appear in the Open Databases field. There needs to be at least
one database file open for the conversion process to be performed.
Note: You must have unrestricted access to the database (with no password
required) in order for FmPro Migrator to read the database structure
info from the FileMaker database.
[STD edition feature] Enter the list of FileMaker
text fields which will contain more than 255 characters. Fields containing
more than 255 characters of text will be converted to MySQL text columns
when converted into the MySQL database table. The list of column names
within this field should represent the MySQL column names for the equivalent
FileMaker fields. To determine how each FileMaker field name will be
converted for use within MySQL, generate the database report file (named:
contact_management_report1.txt) by pressing the Migrate button. Examine
the database report file contact_management_report1.txt and look at
the Renamed As column in the Field Summary section of the report.
Please see the Large Text Fields information within the Usage Notes
section of this documentation file for more info about using text columns
with MySQL.
Destination Database menu - Select MySQL as the
database which will be the destination for the data.
Path to Perl - This field contains the path to the
Perl executable application for UNIX/Mac OS X servers. In most cases,
no change will be needed to this field because the path to perl on Mac
OS X is the default value. This value may need to be changed if the
platform-independent Perl::ODBC program needs to be run on another type
of UNIX server. If the Perl::ODBC program will be run on a Windows server,
no changes need to be made to this field.
----------------------- MySQL Folder Tab - Field
Descriptions
Hostname - The hostname or TCP/IP address of the
MySQL server should be entered in the Hostname field.
Port - The default TCP/IP port used by MySQL is
3306. This value will not normally need to be changed if standard install
and startup parameters were used to install the MySQL database.
Database Name - Enter the name of the MySQL database
into the Database Name field. This is the name which was specified as
the database was created.
Username - Enter the username which will own the
new MySQL table being created for storing the FileMaker data. The username
entered in this field must already exist in the MySQL database. The
MySQL tablename will be similar to the original FileMaker database name,
however spaces will be replaced with underscores and special characters
will be removed.
Password - Enter the password for the MySQL database
account which will own the table.
# ----------------------- 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 [STD edition or higher]
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
contact_management_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 Connections [STD edition or higher]
The best way to transfer data in large text fields and FileMaker Container
fields is by using the contact_management_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 contact_management_fmpro_to_mysql_xfer_odbc1.pl program transfers
data for all FileMaker field types including text fields up to 64000
bytes and images/movies/sound data from Container fields. The program
makes use of bind variables to specify field names and ODBC field types.
The contact_management_fmpro_to_mysql_xfer_odbc1.pl program requires
the installation of the Perl DBI, DBD::mysql and DBD::ODBC modules along
with the FileMaker Pro ODBC driver (if using FileMaker 6 or higher).
There is no FileMaker ODBC driver supplied with FileMaker 5.5 on Mac
OS X, therefore the contact_management_fmpro_to_mysql_xfer_odbc1.pl
program has to be run from a Windows server. An ODBC DSN needs to be
created to connect to the FileMaker Pro database. The naming of the
FileMaker Pro DSN used to connect within contact_management_fmpro_to_mysql_xfer_odbc1.pl
program is as follows:
contact_management_fmpro_odbc_dsn - is used for connecting to the FileMaker
database
ODBC driver and client software installation
The contact_management_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 contact_management_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
There is no data type within a MySQL database which is equivalent to
the Repeating Fields feature within FileMaker. Data from within repeating
fields is copied correctly to the MySQL database, however the data appears
within one 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 would normally be supported 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.
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 withh
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 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 contact_management_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 contact_management_fmpro_to_mysql_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator
STD Edition can work with FileMaker Pro databases running on Windows
servers for the data transfer phase of the conversion process. However
a copy of the FileMaker Pro database must be running under Mac OS X
in order for FmPro Migrator to gather the list of fields and field attributes
information. FmPro Migrator cannot retrieve database structure information
from stand-alone applications created for the Windows platform because
Apple Events are not available on Windows. Furthermore, it is not possible
to retrieve data from stand-alone applications via ODBC due to the lack
of networking with stand-alone solution files.
Working with Stand-Alone Solutions Files - FmPro
Migrator STD Edition can read the database structure from stand-alone
solutions files created with FileMaker Pro Developer on Mac OS X. If
the developer of the solution has enabled file exporting, then a tab
or comma separated value export file may be created. However it will
not be possible to extract the information from the file by using an
ODBC connection because networking capability is not available with
FileMaker stand-alone files. The stand-alone file will also need to
be the Macintosh version of the executable in order for Apple Events
used by FmPro Migrator to read the structure of the database file.
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 contact_management_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 contact_management_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 contact_management_create_inserts_from_tab1.pl
or contact_management_create_inserts_from_csv1.pl program to create
the contact_management_insert_data1.sql file. If necessary, use the
contact_management_create_table1.sql file to create the MySQL database
table. Then use the contact_management_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 contact_management_create_table1.sql.
Use the contact_management_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.
----------------------- Troubleshooting -----------------------
Compiler Error in Open Databases Field - FmPro Migrator
attempts to use AppleScript commands to send Apple Events to the FileMaker
Pro database or stand-alone application. If the application name is
entered incorrectly, the Apple Events will not find a valid recipient
and will fail. Solution: Select a valid FileMaker application by clicking
the Select button next to the FileMaker App Name field.
ERROR 1045: Access denied for user: - This error
may occur if a password has not been specified during the mysql login
process. Solution: Either enter a password when logging into mysql or
specify a default password in the /etc/my.cnf or ~username/my.cnf file.
ERROR 1136 at line ??: Column count doesn't match
value count at row ??: - This error may occur if the contact_management_insert_data1.sql
file has been edited manually. This error means that the number of data
elements doesn't match the number of columns being inserted into the
database. Solution: Examine the SQL code to determine where the mis-match
has occurred, then edit the SQL code accordingly.