Editor's note: FileMaker Pro 6 is a terrific database, as long as you stay within the confines of its design. In part one of this series, David Simpson provided practical migration information using FmPro Migrator on Mac OS X to automate the move from FileMaker to Oracle. Here in part two, he goes through the steps of completing a FileMaker-to-Oracle transition.
The example database used for this article is the Contact_Management.fp5 database that is supplied as a template with FileMaker Pro. (Notice that I have replaced the space with an underline character.) This database includes a wide selection of fields so that we can see how the various data types get transferred to Oracle. The following table shows the FileMaker data types and the compatible column types for the Oracle database.
|
FileMaker Field Type |
Oracle Column Type |
| Text | VARCHAR2(4000) |
| Text | CLOB (if > 4000 bytes) |
| Number | NUMBER |
| Date/Time | DATE |
| Container | BLOB |
Table 1. FileMaker and Oracle Data Types
|
|
The first steps in the migration process are to launch FileMaker, open the Contact_Management.fp5 database file, and add a few records of sample data to the database. Then launch FmPro Migrator on Mac OS X and click on the FileMaker folder tab. Since I usually do a migration project for a customer, I change the Customer field to reflect their name. The name within this field is used to create the Copyright information within each generated script, thus keeping my customer's legal department happy. Click the Browse button to select an output directory, since there will be over a dozen files created for the migration process.
If the name of your FileMaker application is not "FileMaker Pro", then click on the Select button beside the FileMaker App Name field to select the actual name of your FileMaker application. Some common variations of the FileMaker application name include "FileMaker," "FileMaker Pro," and "FileMaker Developer." This information is used to construct the AppleScript commands that will be sent to the FileMaker database application.
Select Oracle 8i-9i from the Destination Database menu. Selecting the Oracle 8i-9i menu option instead of the Oracle 8 option will change the table/tablespace creation code to enable the use of locally managed tablespaces. This is generally a good feature to implement with newer versions of Oracle in order to improve performance.
The Path to Perl field will not require modification in most cases.
|
|
|
Related Reading 
Oracle Essentials |
The Oracle-specific parameters are entered on the Oracle tab. These parameters include the Service Name as it is listed within the tnsnames.ora file. You don't need to specify the an IP address or TCP/IP port number when connecting to an Oracle database, because these parameters are specified as part of the tnsnames.ora service name entry. All you have to do is simply provide the service name, and Oracle networking takes care of looking up the specifics within the tnsnames.ora. For this example, I am using a database service name of dev1, which corresponds to my database instance that uses the same name.
The Username and Password are used to connect to the Oracle database.
The Data and Index Tablespace names and Filenames specify where the new database table and index will be located. Unchecking the Use Existing check boxes will cause FmPro Migrator to generate SQL code to create any of these items; otherwise, existing accounts and tablespaces will be used. Specifying the exact tablespace and data path for the tablespace enables the Oracle DBA to perform load balancing with the database server hardware.
If you plan to migrate a FileMaker database that is expected to be used frequently, you may choose to locate it on a different part of a disk array from other heavily utilized database tables. The need to perform this type of hardware load balancing is minimized with fast disk array hardware such as the Xserve Raid system from Apple. Modern disk arrays can do a lot of this load balancing automatically when set up in a RAID 0+1 configuration.
But what if you have a project that is so heavily utilized that you have two Xserve arrays? In this case, you can put one set of tables on the first array and the second group of tables on the second array. Load balancing in this manner can prevent you from fully saturating the communications channels going to a single array. Based upon the high performance specs of an Xserve Raid array, it would be difficult to saturate a single channel, but this type of planning is still important.
|
Selecting the Status Menu item from the File menu brings up the FmPro Migrator Status Window. This feature enables you to see the STDOUT results from the file generation process. There are rarely problems with the process, but if you do encounter issues, this is a good place to look. If there are problems executing the Perl code that generates the migration scripts, you see the error within the Status Window.
|
|
Since all of the parameters have been entered, clicking on the Migrate button will cause the migration files to be generated.
There are 10 files created for the migration process, but we will only need to use five of these files, which are described in Table 2.
| Filename | Description |
| contact_management_instructions1.txt | This file contains detailed instructions concerning the migration process and is customized for the database that is currently being migrated. |
| contact_management_report_oracle1.txt | This report file shows the structure of the FileMaker database, including the names of fields, field calculation formulas, layouts, and scripts. This file also shows how the FileMaker fields will be renamed when creating the Oracle table. |
| contact_management_create_table1.sql | This is the SQL file that creates the Oracle user account, tablespace, and database table. |
| contact_management_fmpro_max_fieldsize1.pl | This is the Perl script that reads each of the FileMaker records and then creates a report showing the maximum number of characters within each field. |
| contact_management_fmpro_to_oracle_xfer_odbc1.pl | This Perl script reads the data from the FileMaker database and then writes the data into the Oracle database via an ODBC connection. |
Table 2. Migration File List
Transfer the generated files from the Mac OS X computer to the Windows computer (or to the instance of VirtualPC). Create both of the ODBC DSNs, which are listed at the top of the contact_management_fmpro_to_oracle_xfer_odbc1.pl file. These DSNs should be created as System DSNs within the Windows Data Sources (ODBC) control panel.
When creating the FileMaker DSN, click the Use Remote Connection check box if you are connecting to a FileMaker database that is not running on the same computer as the Perl scripts. Then enter a hostname or IP address for the computer that will be running the FileMaker database. If you are running Virtual PC on Mac OS X, you need to make sure that a TCP/IP connection can be made from the Windows OS to the FileMaker database running on Mac OS X. Usually, your VirtualPC instance will have a separate IP address from your Mac OS X, so this will be easy to configure. Just enter the IP address or hostname of the Mac OS X computer in the Server Address field.
|
|
Replace the default value of 255 with 65000 in the Max Text Length field on the Advanced tab of the ODBC FileMaker Setup dialog. It is generally best to leave the Fetch Chunk Size parameter at the default value of 100. Otherwise, FileMaker seems to have a greater tendency to crash while transferring data via ODBC. In fact, I have transferred over 700,000 records within a single FileMaker database file without seeing a single crash. But I have noticed that setting this value to 1000 has resulted in FileMaker crashing while transferring much smaller files.
|
|
The contact_management_fmp_dsn is used to connect to the FileMaker database, and the contact_management_ora_dsn makes a connection to the Oracle database. Enable the Local and Remote Data Access Companions from the Sharing... menu item of the FileMaker File menu. Then run the contact_management_fmpro_max_fieldsize1.pl script from the Windows command prompt. This script will make an ODBC connection to the FileMaker database in order to determine the maximum content sizes for each of the fields. The results from this program will be displayed on the screen and will also be written into the newly created contact_management_fmpro_max_fieldsize_report.txt file within the directory where the contact_management_fmpro_max_fieldsize1.pl script was run.
The main purpose for creating the contact_management_fmpro_max_fieldsize_report.txt report is to determine if there are any text fields within the FileMaker database that contain more characters than will fit within an Oracle VARCHAR2(4000) table column. Since FileMaker text fields can easily store up to 65000 characters, it is commonly necessary to migrate them to the Oracle database by using the CLOB column type.
The Oracle CLOB column type can store up to 4GB of text per record, so this will work fine for any FileMaker text field. For the purposes of this example, I have selected the Template Information Global field as being a field that I want to migrate to Oracle as a CLOB column. To make this change, I enter Template_Information_Global into the Large Text Fields field within FmPro Migrator. The column names entered into this field need to be the actual names used within the destination database after special characters are changed or removed. The contact_management_report_oracle1.txt report file will show you how the FileMaker field names will get renamed when creating the new Oracle table. Press the Migrate button, and all of the scripts will be regenerated. There are many dependencies within the scripts regarding a column-type change, but each script will be completely rewritten to reflect this change.
Transfer the updated scripts to Windows and open the contact_management_create_table1.sql file in a text editor. Using the fieldsize report, you can determine the optimum size to use for each column of the new Oracle table. It is generally a good idea to reduce text column widths from the default value of VARCHAR2(255) to be closer to the actual amount of data contained within the FileMaker database. You will also need to pay attention to the number of columns that will be created within the Oracle table. I have migrated some FileMaker databases containing over 1500 fields; a FileMaker database with this many fields can't be migrated unchanged into a single Oracle database table, because Oracle can't have more than 1024 columns within a table.
This particular FileMaker example database does not contain a primary key. This is OK, if all we want to do is perform a quick migration to get the data into Oracle, and then copy the data to another table later. But if we want the migration process to produce a table that is immediately ready for production use, we should add a primary key to the table. FmPro Migrator automatically determines that you have a primary key within the FileMaker database by looking for the first numeric field, which has the attributes of Not Empty and Unique. Within the table-creation SQL file, a primary key column will be created as NOT NULL, with a primary key constraint using the user-defined INDEX tablespace along with an incrementing sequence and an insert trigger. Any of this code may be manually modified as needed prior to creating the Oracle database table. But if you have never previously created an Oracle sequence or trigger, you will probably find this code to be helpful.
After you have made manual modifications to the table-creation code, you can run it and see if Oracle is able to create the database table for you. Duplicate column name errors are common at this point, due to special characters having been removed during the automatic renaming process. If you are migrating a FileMaker database containing a small number of fields, then you can visually look at the proposed column names to see if you have duplicates. However, if you are migrating a FileMaker database containing 200 fields, you might as well just run the table-creation code with SQL*Plus and see whether you get any errors -- Oracle will let you know!
To create the database table, run the following command at the command prompt on Windows or the Terminal window on Mac OS X (depending upon where you have your database installed).
bash2.05a$ sqlplus /nolog @contact_management_create_table1.sql
The best strategy to follow is to make any column name changes within the original FileMaker database, and then regenerate the scripts with FmPro Migrator. In most cases, there is no reason to try to manually track the dependencies between the generated output files, so just let FmPro Migrator recreate these files for you.
|
Now that the Oracle table has been created, the Perl DBI program that transfers the data from FileMaker to Oracle can be run. This program also needs to be run from Windows, just like the fieldsize report program.
C:\ds\oracle_article> perl contact_management_fmpro_to_oracle_xfer_odbc1.pl
As this program runs, it prints the processed record number on the screen so that its progress can be monitored. There will usually be a pause as records are retrieved from FileMaker; then, record numbers will scroll up the screen in groups of 100 (if that is the Fetch Chunk Size that was set in the ODBC DSN).
The most common errors that occur while transferring data include network connectivity issues between servers, forgetting to actually create the ODBC DSN, and formatting issues with the type of data being transferred from FileMaker. Oracle is rather strict about the data formats it allows within Date fields, so if you have some mistyped records within the FileMaker database, you will either need to fix them or change the field type from Date/Time to text within FileMaker. Then, as usual, regenerate the migration scripts after making this change.
Another interesting issue can occur on Mac OS X laptop computers running FileMaker. If you put the laptop to sleep at any time after launching FileMaker, then you may not be able to establish a network connection to that FileMaker instance or any of its opened databases at a future point in time. The solution is to quit the FileMaker application, relaunch it, and reopen each FileMaker database file.
An ODBC "fetch forward" error from FileMaker often indicates that FileMaker did not respond quickly enough to an ODBC command. This can happen if the computer is very busy doing something else (i.e. emptying the Trash with lots of files), or FileMaker could not complete a calculation before the ODBC connection timed out. This issue occurs most frequently when there are summary fields attempting to summarize all of the records within the FileMaker database. You may be able to work around this issue by simply running the Perl DBI script a second time, after the calculations have completed one time. Otherwise, you will need to disable the summary and unstored calculation fields within the FileMaker database.
When migrating very large FileMaker database files (over 500MB), there are a few modifications I make to the migration process in order to reduce the overall processing time. The typical scenario I have encountered is a situation in which the client wants to migrate the FileMaker data into a large corporate database server. The migration task is part of a larger re-engineering effort to properly normalize the FileMaker data and create a corporate-wide user interface for the data. Migration of the data represents only the first step in the process. The second part of the engineering effort involves using SQL commands to cleanse the data and copy the data to the new normalized database tables. This task can be performed more efficiently in two steps because the second step will involve working with data that will be entirely contained within a large database server like Oracle.
I apply the following steps in this type of situation:
The changes I make include changing all:
SQL_VARCHAR bind variable data types to SQL_LONGVARCHAR.SQL_DATE bind variable data types to SQL_LONGVARCHAR.SQL_DOUBLE bind variable data types to SQL_LONGVARCHAR.The SQL_LONGVARBINARY columns remain unchanged because these columns get transferred to BLOB columns, which store up to 4GB of data per record.
The FileMaker Developer version of FileMaker enables developers to package one or more FileMaker database files as a standalone package that does not require the FileMaker database software. The FileMaker Developer Tool creates a folder for the solution that contains the FileMaker runtime executable and each of the individual FileMaker database files. On Windows, each of the FileMaker database files will be given a developer-assigned extension that by default consists of the characters usr. So a FileMaker database that was originally named filename.fp5 will now be named filename.usr.
To work with standalone solutions files on Windows, just change the filename extension from .usr to .fp5 and use the FileMaker application to open the file. On Mac OS X, the database filename extension will typically remain as .fp5. All you have to do is drag and drop the filename onto the running FileMaker application icon in the dock.
If you just double-click on the file instead of opening it with the FileMaker application, the FileMaker runtime application will launch the file -- and this is not what you want to have happen. You want the database to be opened within FileMaker, so that you can enable the Local and Remote Data Access Companions and make changes to the fields within the Define Fields dialog. Once you have the database file open in FileMaker, you can use FmPro Migrator to generate migration scripts in the same manner as any other FileMaker database file.
My customers commonly ask what they can use for a user interface to their migrated data once the data has been transferred to Oracle or some other database. FileMaker includes a graphical user interface within the database application, so this is not an issue with FileMaker. But once the data has been migrated to another database, some provision needs to made for accessing the data.
To address this need, I added a Perl CGI scripting feature to FmPro Migrator. FmPro Migrator has all of the information available concerning the structure of the database after it creates the migration scripts, so it can also generate Perl CGI scripts for each layout of the original FileMaker database. These scripts follow Perl CGI scripting best practices in regards to forms processing and security. Each group of scripts provides the capability to insert, query, update, and delete records within the Oracle database table. Some examples of these scripts are available on the FmPro Migrator web site.
It is gratifying to see people around the world (literally!) using FmPro Migrator and making use of its features. It has also been a very positive experience to see my business revenue increase during tough economic times as a result of software licensing and migration projects I have completed for customers. The automation that has resulted from the use of FmPro Migrator has made it possible for me and my customers take on projects that would have been too expensive or time-consuming to be practical without an automated tool. FmPro Migrator has also made it possible for the FileMaker database to be used as a RAD (Rapid Application Development) tool for enterprise-class databases such as Oracle.
What does the future hold? Some of the very best feature requests I receive are from either potential or existing customers. This article has covered migrating from FileMaker to Oracle, but as a result of customer requests, I have also added support to FmPro Migrator for several additional databases, including MySQL. In fact, MySQL migration requests have been one of my most popular migration requests. So I expect to continue listening to my customers and adding these requests to my bug/feature database, which, incidentally, is a FileMaker database.
www.fmpromigrator.com
The web
site for the FmPro Migrator application. This site provides a demo version of
FmPro Migrator, tech notes, and example files.
www.runrev.com
Runtime Revolution Ltd., developers of the multi-platform Revolution development environment.
www.openlinksw.com
OpenLink Software, developers of the iODBC open source ODBC driver manager and commercial
ODBC drivers for Mac OS X, Linux, Windows, and UNIX.
www.iodbc.org
iODBC open source ODBC driver
manager web site.
David Simpson is president of .com Solutions Inc. and the developer of the Installgen application.
Return to the Mac DevCenter
Copyright © 2007 O'Reilly Media, Inc.