.com Solutions Inc. - FmPro Migrator

FmPro Migrator - MySQL Files - Title Graphic

File: contact_management_instructions1.txt Features: This file contains the setup, usage and troubleshooting instructions for FmPro Migrator.

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.