How to migrate from FileMaker
Pro to Oracle
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 Oracle 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.
Note: If any FileMaker database files contain spaces, these should be
removed before opening the file.
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 Oracle table columns created as VARCHAR2(4000)
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. The sequence code (if included) should
be modified to change the starting and increment values. The starting
sequence value should be changed to be one higher than the highest increment
used within the FileMaker database. Transfer this file to the Oracle
server and execute this code from within sqlplus to create the tablespaces,
user accounts and table which will contain the FileMaker data. [If a
migration is done from FileMaker 7.0v1 to Oracle with FmPro Migrator
for Windows, please see the troubleshooting notes for more info about
column types.]
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_oracle_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts.
8) Transfer the example_fmpro_to_oracle_xfer_odbc1.pl program to a Windows
server. At the present time the example_fmpro_to_oracle_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_fmp_dsn and example_oracle_odbc_dsn
ODBC DSN entries 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. Make certain that the Enable LOBs feature is checked
within the Oracle ODBC driver and that the TNS Service Name is selected.
The TNS Service Name will contain a listing of every entry within the
tnsnames.ora file. The User ID should not be entered into the Oracle
ODBC 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_oracle_xfer_odbc1.pl program to transfer
the data from FileMaker to Oracle via an ODBC connection.
Use the following instructions if there are repeating
fields which need transferred from FileMaker to Oracle.
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 Oracle 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. Also, do
not make this change before transferring the data from FileMaker to
Oracle in Step #12 of this procedure.)
15) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer
the repeating fields data from FileMaker to Oracle 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 Oracle 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
Oracle databases store Date and Time info within the same column of
the database. FileMaker databases store Date and Time info within separate
fields. There are several options available for converting Date/Time
info to Oracle:
Option #1 - One option is to create a new FileMaker text field for storing
the Date and Time fields together in the Oracle Date/Time format. The
Date/Time format should match the Oracle NLS_LANG setting specified
in the files created by FmPro Migrator. Create a FileMaker script using
the Insert Calculated Result script step in order to calculate this
value for all records of the FileMaker database. Once this calculation
has completed, make a copy of the FileMaker database then delete the
original Date and Time format fields from the FileMaker database. Use
FmPro Migrator to create the conversion scripts based upon the newly
modified FileMaker database file.
Option #2 - If the FileMaker database can't be modified
then consider changing the example_create_inserts_from_csv1.pl or example_create_inserts_from_tab1.pl
Perl programs to concatenate the Date/Time information into one field
during the creation of the Insert SQL files. If FmPro Migrator STD edition
is being used, then make similar modifications to the example_fmpro_to_oracle_xfer_odbc1.pl
file. The example_create_table1.sql file will also require manual modification
in order to reflect the fact that only one Date column should be created
within the Oracle database table.
Large Text Fields [EE Edition]
FileMaker fields containing more than 4000 characters of text will be
converted to Oracle CLOB columns when converted into the Oracle database.
The list of these column names should be entered into the Large Text
Fields field. Data is transferred between FileMaker and Oracle by the
example_fmpro_to_oracle_xfer_odbc1.pl program. This program makes use
of bind variables to specify field names and ODBC field types. This
functionality of using bind variables for CLOB columns requires Oracle
8.1.6 or higher. Oracle CLOB and BLOB columns may be used with locally
managed tablespaces starting with Oracle 9.2.0. Previous Oracle database
versions should use dictionary managed tablespaces for storing tables
containing CLOB and BLOB columns.
Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to
be transferred to the Oracle 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
The best way to transfer data in large text fields and FileMaker Container
fields is by using the example_fmpro_to_oracle_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 Oracle.
The example_fmpro_to_oracle_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 use of bind
variables for CLOB/BLOB columns requires Oracle 8.1.6 or higher.
Using bind variables requires 8.1.6 or higher. The example_fmpro_to_oracle_xfer_odbc1.pl
program requires the installation of the Perl DBI and DBD::ODBC modules
along with the FileMaker Pro ODBC driver on Windows. There is no FileMaker
ODBC driver supplied with FileMaker on Mac OS X, therefore the example_fmpro_to_oracle_xfer_odbc1.pl
program must be run from a Windows server. ODBC DSNs need to be created
to connect to each database. The naming of the DSNs used within odbc.pl
program is as follows:
example_oracle_odbc_dsn - is used for connecting to the Oracle database
example_fmp_dsn - is used for connecting to the FileMaker database
ODBC driver and client software installation
The example_fmpro_to_oracle_xfer_odbc1.pl program needs to be installed
on a Windows server which has Oracle client 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_oracle_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 an Oracle database
running on any platform (including Mac OS X, Windows, Solaris etc).
Value Lists
There is no direct support for Value Lists within Oracle, therefore
this information is not transferred to the Oracle database. This functionality
is also application dependent in its implementation. It is possible
to create a separate lookup table in Oracle containing the items within
the value list. This feature could be implemented as a relationship
within FileMaker Pro database. Then each related database file could
be individually converted and inserted into Oracle by the scripts generated
with FmPro Migrator.
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. On MacOS x, the detection of repeating
fields is performed automatically. On Windows, you need to enter in
the list of repeating fields within the Repeating Fields field on the
FileMaker Tab of FmPro Migrator. 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.
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 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 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 Oracle 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 example_fmpro_to_oracle_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_oracle_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator
can work with FileMaker Pro 5/5.5/6 databases running on MacOS X or
Windows servers for the data transfer phase of the conversion process.
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 - 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, however Oracle only permits 30
character column names. FmPro Migrator automatically truncates field
names to 30 characters while converting FileMaker Pro field names to
Oracle column names. Spaces are replaced with underscore characters
and special characters are removed from the name. However the process
of shortening the FileMaker Pro field names may result in duplication
of column names when creating the Oracle database table. The FileMaker
Pro developer should manually change any FileMaker Pro fields to insure
that shortened field names will still be converted to unique Oracle
column names.
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 Oracle database table. 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. The Oracle DBA should determine whether additional sequences
need created and whether changes need to be made to the PL/SQL code
used for the Oracle trigger in order to match the FileMaker Pro entry
options.
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)
