In my previous articles detailing the installation of the Oracle 9i Developer Release on Mac OS X, I mentioned some of the differences between FileMaker and Oracle databases. In this article I provide practical migration information using FmPro Migrator on Mac OS X to automate FileMaker-to-Oracle database migrations.
I include real-world tips based on database migrations I have completed for customers with dozens of files and gigabytes of data. The prospective reader of this article is someone who is familiar with creating and using FileMaker databases, but who may not be as familiar with the equivalent tasks with an Oracle database.
The development of the FmPro Migrator application occurred as a direct result of my three-part series [Part 1, Part 2, Part 3] detailing the installation of Oracle 9i Developer Edition on Mac OS X.
I decided there would be some Mac OS X users interested in migrating their existing FileMaker databases to Oracle. I chose to use AppleScript to obtain detailed information about each open FileMaker database because AppleScript doesn't require any additional configuration by the user. Once I gather enough info to produce a report about the FileMaker database structure, I also have enough info to create table-creation SQL code and Perl migration scripts as well.
Figure 1. AppleScript usage by FmPro Migrator.
I created the graphical interface for FmPro Migrator using the Revolution development environment by Runtime Revolution Ltd. Using Revolution, I incorporate a couple of dozen AppleScript scripts, the Perl code that generates the migration scripts, and wrap it all up within a Mac OS X native application.
Since Revolution builds double-clickable applications for a dozen additional operating systems, I'm able to reuse the Perl CGI scripting functionality of FmPro Migrator to create the CGIScripter application for Mac OS X, Windows, Linux, and Solaris.
|
Related Reading 
Oracle Essentials |
The result of this development effort has been two new applications that generate revenue from software licensing, FileMaker migration services, and Perl CGI programming. Search-engine optimization and publicity about both of these applications has also provided additional business opportunities for consulting services that extend beyond simple migration tasks. These business opportunities have expanded the range of my client base beyond the local area to include clients from around the world.
The typical reasons for customers wishing to migrate from FileMaker include FileMaker file-size limitations and performance issues. FileMaker 6 has a maximum file-size limitation of 2 GB. A FileMaker 6-database file that exceeds 2 GB in size generally gets corrupted almost immediately. Oracle 9i individual-tablespace files can range from 8 GB to 128 GB depending on block size, and up to 8 exabytes with Oracle 10G.
Depending upon the type of tasks being performed with a FileMaker database, it may not be possible to even get close to the typically recommended maximum limit of 250 users per database. Tasks such as searching the database, long-running report scripts, or large numbers of records being calculated within summary fields can use up available CPU performance rather quickly. Oracle databases have been tested with up to 64,000 simultaneous users connected with the MTS feature. Oracle databases can also scale up much higher in terms of the hardware they can run on. A Sun Fire 15K Server can contain 106 CPUs and 500 GB of RAM in its maximum configuration. An Oracle database running on Mac OS X can easily be moved to higher-end hardware by using the Oracle export/import utilities.
An Oracle database can be recovered in many ways and also offers control over commit and rollback operations on a transactional basis. If a FileMaker database can't be recovered with the Recover... feature under the File menu, then you will probably have to send it to FileMaker Inc. for recovery.
Some of the technical challenges with manual migrations of FileMaker databases include changing database and column names, dealing with large numbers of FileMaker fields, migrating text fields greater than 4000 bytes, and migrating images from FileMaker. The migration process implemented with FmPro Migrator involves the use of Perl programs to migrate the actual data via an ODBC connection between the FileMaker and Oracle databases.
• FileMaker Database Name Issues -- A FileMaker database file name can contain characters (i.e. spaces, special characters) that can't be used within a SQL statement. The first task is to change the database name to remove any special characters from the filename. FileMaker will give a warning message if related files can't be found, but this won't affect the migration process because each file gets transferred individually to the Oracle database.
• FileMaker Field Name Issues -- FileMaker field names may also contain characters that would be illegal for an Oracle database column name. Fortunately FmPro Migrator handles this conversion process automatically when it generates migration scripts. But you do have to watch out for situations in which duplicate column names may be created. FmPro Migrator removes special characters from field names and changes spaces into underscore characters. Oracle column names also get truncated to the Oracle mandated limit of 30 characters. When migrating a FileMaker database containing hundreds of fields, it is generally more efficient to create the first pass of migration scripts and then try to create a table with the resulting table creation code. If there are duplicate column names, SQL*Plus will provide an error message so that changes can be made within the original FileMaker database. The scripts can then be regenerated by FmPro Migrator to reflect these changes within FileMaker.
• Too Many FileMaker Fields -- Within FileMaker there is no built-in mechanism to keep track of variables while running a script or calculation. Therefore FileMaker databases often contain dozens of global fields used to store this type of information. FileMaker summary and calculation fields are also handled differently within a database like Oracle. Calculation features during record insertion are usually handled via triggers, which fire during the insert or update of a record within Oracle. Calculations are handled either via PL/SQL stored procedures or inline PL/SQL code within the trigger itself. The problem with having so many fields is that you can exceed the maximum number of fields that can be created within an Oracle table (1,024 columns). It is generally possible to remove all global fields, unstored calculation fields, and summary fields from the FileMaker database prior to starting the migration process. Removing or at least disabling unstored FileMaker calculation fields and summary fields is also advisable in order to prevent FileMaker from dropping the ODBC connection while waiting for these calculations to be completed. FileMaker will usually issue a "fetch forward" error when this occurs.
• Migrating FileMaker Large Text Fields -- FileMaker can store as many as 64,000 characters within each text field. However the Oracle VARCHAR2 column can only store up to 4,000 characters. Therefore, fields identified as Large Text fields are migrated by FmPro Migrator as Oracle CLOB columns that can store up to 4 GB of data per record. It is not possible to copy large amounts of data into Oracle by using standard SQL insert statements because the maximum size of an insert statement is 2,499 bytes. Oracle error SP2-0027 will result if this limit is exceeded. The scripts generated by FmPro Migrator utilize a Perl DBI/DBD:ODBC connection to the Oracle database in order to avoid this limitation.
• Migrating FileMaker Repeating Fields -- There is no data type within an Oracle database that is equivalent to the Repeating Fields feature within FileMaker. Repeating fields within FileMaker will only be completely transferred into Oracle if the fields are text fields. FileMaker numeric fields that contain repeating values should be converted to text fields within FileMaker in order to transfer each of the repeating values. Otherwise, Oracle will only recognize the first value of a numeric field. Each repeating field entry is separated by an ASCII (29) character, which is used by FileMaker Pro to separate values within repeating fields. For full support of this functionality, redesign of the FileMaker Pro database structure should be considered. This type of feature should be implemented via separate rows of data within an Oracle database with the child records having a common foreign key value pointing back to the primary key of the parent record.
• Migrating FileMaker Container Fields -- FileMaker Pro uses Container fields for storing pictures, sound, and QuickTime video. Data located within FileMaker Pro Container fields is transferred into Oracle BLOB columns. There are some limitations that have been discovered while retrieving Container field data from FileMaker Pro. I have only been able to determine the storage format for FileMaker JPEG images, as the internal format does not appear to be publicly documented. Therefore, only JPEG Container field data is supported by the migration scripts. 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 data 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. So if there is sound information stored within a Container field, you will end up getting a JPEG image of a sound icon copied into the Oracle database. FileMaker appears to be unique in handling information storage in this manner. All other databases I have worked with simply store the exact binary information that you entered into the field. This feature of FileMaker appears to be related to the tight integration of the user interface with the database engine technology. There appears to be a desire by the FileMaker developers to always show some type of image representing the type of info within the field, even if the type of data does not lend itself to a visual display.
• Migrating FileMaker Calculations -- FileMaker field calculations and calculated summary values need to be re-engineered either as PL/SQL stored procedures, triggers, or application-specific code by the application providing the graphical interface to the Oracle database. There are too many differences between how FileMaker handles these tasks to make it practical to automate the migration of calculation formulas.
• Migrating FileMaker Scripts -- The functionality incorporated within FileMaker scripts will also typically be incorporated within stored procedures in an Oracle database. These procedures might be called by a scheduled job within the Oracle job system or a remotely triggered script that could be scheduled via a CRON job on a UNIX/Linux/Mac OS X server.
• Migrating the FileMaker User Interface -- The last hurdle facing most people who migrate from FileMaker to Oracle is the implementation of a user interface for the migrated data. There are many excellent development tools available for this type of development effort, which I mentioned in the previous article series. However, I decided that I wanted to provide more complete functionality within FmPro Migrator so I added a Perl CGI-scripting feature. Once the migration scripts have been generated, FmPro Migrator then generates a group of Perl CGI scripts for each layout of the FileMaker database. These scripts support the insert, query, update, and delete of records in the migrated database table. Full support is also provided for handling images and large text fields, which is often a challenge when writing software for an Oracle database. Since FileMaker layouts are often used to restrict access to data, this same type of structure is implemented when creating the Perl CGI scripts. If you have five layouts within your FileMaker database, then you will see that five complete sets of HTML pages and Perl CGI scripts will also be generated by FmPro Migrator.
The issue with FileMaker ODBC drivers on Mac OS X is a continuing issue that has not yet been resolved. The FileMaker ODBC driver installed on Mac OS X can only be used by the FileMaker database itself. This means that Perl and other programs simply can't access the FileMaker database using this driver. The best and most concise answer I have received concerning this issue came from OpenLink Software support.
Here is their very concise one sentence answer:
"The FileMaker ODBC Client Driver can only be used by FileMaker, at present, because FileMaker is the only ODBC Client Application that has been built with linkage to the Carbon-based DataDirect Driver Manager for Mac OS X."
OpenLink Software is the developer of the open source iODBC Driver Manager, which is bundled with Mac OS X.
|
Related Reading 
Book of FileMaker 6 |
They also provide drivers for a variety of non-FileMaker databases, which work with the iODBC Driver Manager. ODBC drivers that use the iODBC driver manager can be linked with the Perl DBI/DBD modules on Mac OS X. In fact, OpenLink Software has an OpenLink Bridge solution running on Mac OS X that would make it possible to create an application running on Mac OS X to access FileMaker via an ODBC connection.
If you need continuous access to a FileMaker ODBC connection from Mac OS X (as in some type of server application), then this would be a solution to consider. If you look at the product details very closely, you will realize that this seemingly impossible task has been accomplished by making a connection to a Windows PC (or Virtual PC on Mac OS X) running the Windows version of the FileMaker ODBC driver. This proxy type of ODBC connection is similar to what can be accomplished by making a Perl DBD:Proxy connection to Perl DBD:ProxyServer software running on a Windows computer.
Another issue involves connecting to Oracle on Mac OS X via either Perl:ODBC or Perl DBD:Oracle. I have not yet been successful compiling the Perl DBD:Oracle module with the Oracle 9i Developer Release on Mac OS X. I even ordered an older Oracle client CD for Macintosh from Oracle, but this didn't work either. So I have put that goal aside right now, while I wait for the Oracle 10G production release for Mac OS X -- which is expected sometime early this year.
The straightforward solution I have implemented simply involves running Perl DBD:ODBC programs on a Windows computer (or Virtual PC), which is able to access both the Oracle and FileMaker databases for the data migration. The Oracle and FileMaker databases can be running on any platform, but Oracle and FileMaker ODBC drivers both need to be installed on Windows. For an Oracle database, this is accomplished by installing the Oracle client CD on Windows -- the ODBC drivers will be installed along with the client software. For FileMaker, the ODBC driver software is installed along with the FileMaker database software. A 30-day demo of FileMaker 6 for Mac OS X and Windows can be downloaded from the FileMaker web site, and it will include the required ODBC driver software. An ODBC DSN is then created for each database connection. The naming of the ODBC DSNs is specified within the scripts generated by FmPro Migrator.
ActiveState Perl needs to be installed on Windows along with the Perl DBI and DBD::ODBC modules. The DBI/DBD::ODBC modules can be downloaded from www.cpan.org or with the following ActiveState PPM commands:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit
I've covered a lot of ground in this article, including differences between FileMaker and Oracle, why you might want to migrate, and various technical difficulties involved with a migration. In Part 2, I'll go through the steps of completing a FileMaker to Oracle migration with FmPro Migrator.
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.