How to migrate from FileMaker
Pro to FrontBase
Note: Additional information is contained within the <database>_instructions.txt
file created during the file generation process. The <database>_instructions.txt
file contains the actual file names created by FmPro Migrator. This
document uses an example FileMaker 6 database table named "example"
which is migrated to FrontBase. The files created for your database
will use the names of the database tables in your FileMaker database.
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 included with MacOS X.
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.
Enter the names of any repeating fields within the FileMaker database
into the Repeating Fields field within FmPro Migrator for Windows. These
repeating field names should match the names of the fields within FileMaker,
including the use of spaces and special characters.
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 FrontBase 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. You may choose to designate
FileMaker fields containing large amounts of text as Large Text Fields
within FmPro Migrator. These fields will get migrated as FrontBase CLOB
columns. Using the CLOB datatype for these columns will prevent searching
the contents of these columns, but will improve data import time since
indexes won't need updating. Transfer this file to the FrontBase server
and execute this code from within the sql92 client application to create
the table which will contain the FileMaker data. [If a migration is
done from FileMaker 7.0v1 to FrontBase 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_frontbase_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts.
8) Transfer the example_fmpro_to_frontbase_xfer_odbc1.pl program to
a Windows server. At the present time the example_fmpro_to_frontbase_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 and DBD::ODBC 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> 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) Create the example_fb_dsn entry in the ODBC Control Panel.
12) 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.
13) Execute the example_fmpro_to_frontbase_xfer_odbc1.pl program to
transfer the data from FileMaker to FrontBase via an ODBC network connection.
14) If repeating fields need to be extracted from the FileMaker database,
use the example_repeating_fields_create_table1.sql SQL script to create
a new FrontBase database table for the repeating fields data. The column
sizes defined within this script should be adjusted as necessary prior
to creating this table.
15) 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.)
16) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer
the repeating fields data from FileMaker to FrontBase via an ODBC network
connection.
------------ Usage Instructions - Image Export Script-------------
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 the 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_odbc_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.
----------------------- 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.
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.
[For Windows, enter the ODBC DSN name and select your FileMaker database
version from the Source Database menu prior to pressing the Refresh
button. Make sure that the Local and Remote Data Access Companion plug-ins
are enabled for each FileMaker database.] 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.
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 PostgreSQL TEXT columns when converted
into the PostgreSQL database table. The list of column names within
this field should represent the PostgreSQL column names for the equivalent
FileMaker fields. To determine how each FileMaker field name will be
converted for use within PostgreSQL, generate the database report file
(named: example_report1.txt) by pressing the Migrate button. Examine
the database report file example_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 Access.
Destination Database menu - Select PostgreSQL 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.
----------------------- Other Folder Tab - Field Descriptions
Hostname - The hostname or TCP/IP address of the PostgreSQL
server should be entered in the Hostname field.
Port - The default TCP/IP port used by PostgreSQL is 5432.
This value will not normally need to be changed if standard install
and startup parameters were used to install the PostgreSQL database.
Database Name - Enter the name of the PostgreSQL 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 PostgreSQL
table being created for storing the FileMaker data. The username entered
in this field must already exist in the PostgreSQL database. The PostgreSQL
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 PostgreSQL database
account which will own the table.
----------------------- Usage Notes -----------------------
Time Fields
Both FrontBase 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
FrontBase 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
FrontBase utilizes a date format of "DATE '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 "DATE 'YYYY-MM-DD'"D format used by
FrontBase.
Large Text Fields
FileMaker fields containing large amounts of text can be converted to
FrontBase CLOB columns when converted into the FrontBase database. This
is not mandatory, as the VARCHAR column type can handle up to 1Gb of
text information, and it will be searchable too. But using a CLOB column
may be used to improve the performance of database insertion of large
text items. The list of these large text column names would be entered
into the Large Text Fields field. Data is transferred between FileMaker
and FrontBase by the example_fmpro_to_frontbase_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 FrontBase 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 example_fmpro_to_frontbase_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_frontbase_xfer_odbc1.pl
program needs to be copied to a Windows server which has FrontBase server
software, Perl, Perl DBI module, Perl DBD::ODBC 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_frontbase_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 FrontBase
database running on any platform (including Mac OS X, Windows, Linux
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 FrontBase. 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.
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 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.
Container Fields
FileMaker Pro uses Container fields for storing pictures, sound and
QuickTime video. Data located within FileMaker Pro Container fields
is transferred to FrontBase BLOB columns. There are some limitations
which have been discovered while retrieving Container field data from
FileMaker Pro. The only Container field data supported for 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 FrontBase 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_frontbase_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_frontbase_xfer_odbc21.pl
program.
Working with FileMaker Pro 5.0/6.0 on Windows - FmPro
Migrator can work with FileMaker Pro 5.0/6.0 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 MacOS X or Windows if the actual database file
is opened with FileMaker (instead of the stand-alone application).
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 FrontBase column names may
be up to 128 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.
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 FrontBase database table.
The primary key column of the FrontBase database table will be migrated
as an INT column using an auto-incrementing value starting at 1. 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.
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 - unless entered manually into FmPro
Migrator)
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)
----------------------- Troubleshooting -----------------------
Compiler Error in Open Databases Field - FmPro Migrator
on MacOS X 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.
[Windows only issue] When performing a FileMaker 7 to
FrontBase migration, FileMaker container fields are migrated as text
fields and every field is set to require "Not Empty" data
validation. This occurs when FileMaker 7.0v1 on Windows is used as the
source of the migration in FmPro Migrator for Windows.
[Windows only issue] When performing a FileMaker to FrontBase
migration, none of the repeating fields are recognized within the FileMaker
database.
Solution: When using FmPro Migrator for Windows, it is
necessary to manually enter the names of each FileMaker repeating field
into the Repeating Fields field. Each field name should be entered as
it appears within the Define Fields window of FileMaker, including the
use of spaces and special characters. Look at the example_report_frontbase1.txt
report file generated by FmPro Migrator in order to verify that each
repeating field has been correctly noted on the report.
FmPro Migrator for Windows makes an ODBC connection to
the FileMaker database which is being migrated in order to obtain database
structure information. When FileMaker 7.0v1 is used as the source of
the migration, the data types returned from the ODBC driver report that
Container fields are text fields and that all fields are "NOT NULL"
fields. There are two solutions to this issue:
Solution1: If a version of the database file is available within the
FileMaker 5/6 format, then use the older version of the file as the
source of the migration for FmPro Migrator. Versions of the FileMaker
ODBC driver prior to FileMaker 7 do not display this behavior. FmPro
Migrator will query the database and obtain the correct data types and
NULL/NOT NULL status for creating the example_create_table1.pl file.
Use of the older database file is only required when creating the migration
scripts. After the scripts have been created, use either version of
the file for the actual import of the data into the new FrontBase database
table.
Solution2: If an older version of the source database file is not available,
then use FmPro Migrator to create the migration scripts from the FileMaker
7 source database file. Then make changes to the field types and validation
settings within the example_create_table1.pl file prior to creating
the new FrontBase table. To make these changes manually, change the
VARCHAR(255) field types to BLOB for each of the container fields. Then
perform a search and replace within a text editor to remove all of the
"NOT NULL" attribute text. It is likely that very few database
fields will require "NOT NULL" validation so these few fields
can be updated manually.
Error: DBD:ODBC:st execute failed: [FrontBase Inc.][FrontBase
ODBC]Semantic error 124. The degree of the table expression doesn't
match the INSERT.
Solution: This error is caused by having a mismatch between
the number of columns specified in the insert statement and the number
of data elements being inserted into the table. This error shouldn't
occur unless the example_fmpro_to_frontbase_xfer_odbc1.pl file has been
edited manually, thus causing this mismatch.
Error: DBD:ODBC:st execute failed: [FrontBase Inc.][FrontBase
ODBC]Exception 358. Integrity constraint violation (PRIMARY KEY, example._C0000000003(id)).
Solution: The primary key constraint referenced within
the error message has been violated. Since primary key column values
must be unique within the table, this generally means that an attempt
was made to insert a duplicate value within the primary key column.
This problem could occur if the example_fmpro_to_frontbase_xfer_odbc1.pl
script was run twice.
Error: DBD:ODBC:: db prepare failed: [FrontBase Inc.][FrontBase
ODBC]Syntax error 035. Improper DATE literal.
Syntax error 108. Expected ';' not found.
Exception 363. Transaction rollback.
Solution: This error may occur while transferring data
from FileMaker to FrontBase with the example_fmpro_to_frontbase_xfer_odbc1.pl
script if an older version of the FrontBase ODBC driver is installed.
Upgrading to FrontBase ODBC driver version 1.20.00.79 or higher will
resolve this problem. This version of the FrontBase ODBC driver also
includes the automatic addition of the DATE/TIME/TIMESTAMP literal which
is a feature utilized by the example_fmpro_to_frontbase_xfer_odbc1.pl
script.
Error: The example_fmpro_to_frontbase_xfer_odbc1.pl script
crashes when run, giving a generic Windows error message.
Solution: This error has been observed with older versions
of the FrontBase ODBC driver when writing many fields of data (over
40 fields) into FrontBase.
Upgrading to FrontBase ODBC driver version 1.20.0081 or higher resolves
this problem.
Error: DBD:ODBC:: st execute failed: [FrontBase Inc.][FrontBase
ODBC]Exception 239. Data exception - string data, right truncation.
Exception 363. Transaction rollback.
Solution: This error may occur if the example_fmpro_to_frontbase_xfer_odbc1.pl
script attempts to write too much data into a VARCHAR column. This problem
can be resolved by using the report generated by the example_fmpro_max_fieldsize1.pl
script to create each FrontBase column large enough to hold the data
which is actually contained within the FileMaker database file.
