.com Solutions Inc. - Logo  
support header buttons
.
FmPro Script Diffbullet 7f FmPro Migrator bullet 7f CGIScripter
.
.
.
. .

Free Trial button - Demo Download

 


.

. .

 

...
.

 

hline f image

FileMaker to MySQL Migration FAQ
by David Simpson

Note: The info in this article is for an older version of FmPro Migrator. The newest illustrated PDF manuals are located on the support web page.

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?


FmPro Migrator Data Only Conversion

FmPro Migrator everything panel
FmPro Migrator consulting panel


1] Q: How can I transfer records containing special characters from FileMaker to MySQL?
A: If records are exported in CSV/TAB formatted files the data will be exported as ASCII text and will omit accent marks and other special characters. This problem can be resolved by transferring data between FileMaker and MySQL using an ODBC connection between the two databases, this is how FmPro Migrator transfers records between databases. It is also possible to manually write a Perl program to transfer data between FileMaker and MySQL.

2] Q: Can I transfer FileMaker ScriptMaker functionality to MySQL?
A: Yes. FileMaker scripts can be directly converted into PHP scripts within a PHP web application, or LiveCode scripts if you want to build a desktop or mobile application. It can also be helpful to convert FileMaker unstored calculations into MySQL view files in order to offload the computational load from the application to the database server, while improving performance. This feature is also built into FmPro Migrator.

3] Q: What can I use for a user interface to my FileMaker data once it has been transferred to MySQL.
A: There are a wealth of development tools available for creating application interfaces to data stored within MySQL. Some of these options include writing a PHP web application. FmPro Migrator converts FileMaker layouts into functional PHP web applications. The converted PHP web application uses the contents of existing layouts to build dynamic web forms having insert, query, update and delete features. Almost all layout objects are converted, including: charts, image fields, tab controls, custom value list menus, field-based value list menus (single and dual field), checkboxes, radio buttons and vector graphic objects (rectangle, rounded rectangle, oval/circle).

4] Q: How can I transfer images from FileMaker to MySQL?
A: JPEG images can be transferred from FileMaker to MySQL with a Perl program, because FileMaker container fields can't be exported as CSV/TAB formatted files. Here is a link to an example program which transfers JPEG images from FileMaker to MySQL: fmpro_to_mysql_xfer_odbc1.pl. Note: This direct image conversion feature is only available for FileMaker 7 - 10 database files due to changes within the FileMaker ODBC driver.

5] Q: How can I resolve MySQL error 1117 when attempting to create a MySQL table to match my FileMaker database file?
A: 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 version 5.0+ includes 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 or the maximum number of bytes allowed per row of data. MySQL is one of the most generous database servers in regards to these capacities, (2364 columns per table, and max row contents of 65501 bytes) but these limits can still be exceeded by FileMaker. 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. Note: FmPro Migrator includes a feature to remove FileMaker Unstored Calculation, Summary and Global fields at the start of the conversion process. This should be done prior to running the Get Info step.

6] Q: What ODBC settings should I use when transferring data from FileMaker to MySQL?
A: FileMaker 5.0, 5.5 and 6.0 [for Windows] includes an Advanced tab in the FileMaker ODBC Data Source panel. The Max Text Length in this panel should be set to 65000 in order to match the maximum text size for a FileMaker field. The Fetch Chunk Size should generally be set to 100 when transferring fewer than 500 FileMaker fields of data. When working with more than 500 fields, this parameter should be set to 25 or even as low as 10. Setting this parameter too high will often cause FileMaker to crash during the data transfer which will often result in a "fetch forward" error.

ODBC FileMakerPro Driver Setup

7] Q: How can I implement a FileMaker Auto-enter Serial number field in MySQL?
A: A FileMaker Auto-enter Serial number can be implemented as an unsigned integer with the AUTO_INCREMENT option for the column. It is generally a good idea to also define this column as a primary key - as shown below.

CREATE TABLE IF NOT EXISTS example
(
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
text model VARCHAR(255) NULL
PRIMARY KEY(id)
);

The Auto-Increment starting value should be to be set to 1 digit higher than the highest record number within each FileMaker database file. For MyISAM tables this can be done as follows:

ALTER TABLE example AUTO_INCREMENT = 100;

8] Q: Why do I get a MySQL 1064 error when attempting to create a MySQL table?
A: This error may be caused by a syntax error in the table creation SQL code. FileMaker field names often contain spaces and special characters or consist of MySQL database reserved words. These types of problems will cause MySQL error 1064 to be displayed while trying to create the database table. MySQL column names and table names consisting of reserved words should be renamed to avoid this type of conflict. Spaces should be removed or replaced with underscore "_" characters in order to create a syntactically correct MySQL column name.

9] Q: Can FileMaker relationships be transferred to MySQL.
A: Yes, the related records can all be transferred to MySQL as separate tables. Since the records are related by the data within the tables, these relationships will be maintained. MySQL does not currently implement a foreign key mechanism, but this is not a problem as long as all related FileMaker database files are transferred into MySQL tables. The only manual intervention which needs to be taken is setting the Auto-Increment starting value to be 1 digit higher than the highest record number within each FileMaker database file.

10] Q: Can FileMaker files over 2Gb be transferred to MySQL?
A: Yes, FileMaker Pro 7 database files may grow to 8TB in size. But with FileMaker Pro 5.0, 5.5 and 6.0, database files cannot exceed 2GB in size. If one of these older database files does exceed 2GB in size the file will generally become corrupted.

11] Q: How can I resolve a MySQL max_allowed_packet error when transferring data to MySQL?
A: MySQL's default configuration is a setting of 1Mb for the max-allowed-packet parameter. This parameter defines the maximum amount of data which can be written to a single record, If large images or large numbers of text fields are being transferred from FileMaker to MySQL, this limit may be exceeded.
The solution to this problem is to increase the max-allowed-packet configuration parameter within the mysqld configuration file (my.ini on Windows or /etc/my.conf on UNIX):
max-allowed-packet=8M

12] Q: How can I prevent a FileMaker "fetch forward" error when transferring data to MySQL via an ODBC connection?
A: In general, this error means that FileMaker is no longer serving data through an ODBC connection. There are several problems which can cause this error, including:
1) FileMaker has crashed.
If FileMaker 5.0, 5.5 or 6.0 [for Windows] is the source database, then the Fetch Chunk Size parameter in the ODBC FileMakerPro Drive Setup panel should be set to a maximum of 100. When working with more than 500 fields, this parameter should be set to 25 or even as low as 10. Setting this parameter too high will often cause FileMaker to crash during the data transfer which will usually result in a "fetch forward" error. This ODBC configuration parameter does not exist with the FileMaker 7 ODBC configuration.

2) FileMaker is too busy performing calculations to respond to requests for data through an ODBC connection.
If FileMaker unstored calculation or summary fields exist within the database, then these calculations will have to complete before the first record is returned through an ODBC connection. If the amount of time required to complete these calculations for every record of the FileMaker database exceeds an internal threshold level, then the ODBC connection will fail with the "fetch forward" error. Since calculation and summary fields are not available within MySQL, it is best to disable these calculations or delete these fields within FileMaker prior to transferring the data to MySQL. Within the FileMaker Define Fields dialog these calculation and summary type fields can easily be changed to text or numeric fields. Note: FmPro Migrator includes a feature to remove FileMaker Unstored Calculation, Summary and Global fields at the start of the conversion process. This should be done prior to running the Get Info step.

3). The computer which is running the FileMaker database is so busy with CPU or disk activity that FileMaker can't respond to requests for data through an ODBC connection in a timely manner.
It is not recommended that any other CPU or disk intensive tasks be run on the computer which is running the FileMaker database while transferring data in order to prevent this problem. Tasks such as defragmenting the disk, copying large numbers of files or running time intensive queries within FileMaker (or any other application) could cause FileMaker to drop the ODBC connection with the "fetch forward" error.

hline f image

hline

. .

.

. .

 

 

 

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

Legal Notices

.
.   .
.
Home Products Services Downloads Order Support Contact