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

 

Free Trial button - Demo Download


.

. .

 

...
.

support how to title image

hline f image

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?


FmPro Migrator Data Only Conversion

FmPro Migrator everything panel
FmPro Migrator consulting panel

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)

hline f image

hline

. .

.

. .

 

 

 

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

Legal Notices

.
.   .
.
Home Products Services Downloads Order Support Contact