Migrating the Data
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.
Tips for Migrating Very Large FileMaker Databases
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:
- Avoid making two passes through the data: This means that I don't run the fieldsize report for very large database files. This also means that I don't have the information required to properly size the columns in the destination database.
- Make each column as large as possible: For each text column of the destination table, I use the largest possible text compatible column size, which means using a CLOB data type in place of text, numeric, and date fields. FileMaker Container fields get transferred to Oracle BLOB columns, so these columns don't require any changes. It is easy to use BBEdit to manually change the table-creation SQL code by performing a global search-and-replace within the file. This step resolves the problem of data potentially getting truncated due to the Oracle column size not being large enough. This also means that data-type errors within the FileMaker database will not prevent the data from being written into the Oracle database. You generally don't want to be part of the way through migrating a 500MB file and get a data-type mismatch error. It will be quicker to find and fix these types of errors during a data cleansing operation at a later point in time.
- Manually change DBI data types: Once the new table has been created, I then manually modify the Perl DBI transfer program to reflect these changes. When dealing with large numbers of columns, it is much quicker to manually change the bind variable data types with a text editor rather than manually enter each column name into FmPro Migrator as a Large Text Field.
The changes I make include changing all:
SQL_VARCHARbind variable data types to
SQL_DATEbind variable data types to
SQL_DOUBLEbind variable data types to
QL_LONGVARBINARY columns remain unchanged because these columns get transferred to BLOB columns, which store up to 4GB of data per record.
Tips for the Migration of FileMaker Standalone Files
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.
Web Interface for Migrated Data
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.
The web site for the FmPro Migrator application. This site provides a demo version of FmPro Migrator, tech notes, and example files.
Runtime Revolution Ltd., developers of the multi-platform Revolution development environment.
OpenLink Software, developers of the iODBC open source ODBC driver manager and commercial ODBC drivers for Mac OS X, Linux, Windows, and UNIX.
iODBC open source ODBC driver manager web site.
Return to the Mac DevCenter