.com Solutions Inc. - FmPro Migrator

FmPro Migrator - Oracle Files - Title Graphic

File: contact_management_instructions1.txt Features: This file contains the setup, usage and troubleshooting instructions for FmPro Migrator.

This output file was created by FmPro Migrator version 1.23 on Thu Apr 3 12:21:05 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net

----------------------- Overview ----------------------- There are multiple versions of FmPro Migrator with differing feature sets. This file was produced by FmPro Migrator STD Edition. These features are summarized below: Demo Edition - The Demo edition creates output files via a set of internally stored parameters. The resulting output files are representative of the typical files which would be created by the Lite Edition of the application.

Lite Edition - The Lite Edition supports the conversion of FileMaker Pro database data into SQL INSERT statements for insertion into an Oracle database table. This conversion method is suitable for small datasets which do not involve text fields over 4000 characters each. The conversion of data from FileMaker Container fields is not supported. The Lite Edition creates conversion scripts for the 1st database listed within the Open Databases field of FmPro Migrator.

STD Edition - The STD Edition supports all of the features of the Lite Edition plus the additional features of transferring large text fields and FileMaker Container fields. FileMaker supports as many as 64000 characters of text within a text field - which greatly exceeds the standard 4000 characters allowed within an Oracle VARCHAR2(4000) column. FileMaker Container field data may also be converted via the Perl DBI/DBD::Oracle program which is created by the STD Edition. The STD edition provides the convenience of automatically generating scripts for each open FileMaker Pro database (up to 50).

----------------------- Theory of Operation ----------------------- FmPro Migrator uses Apple Events via AppleScript to query the FileMaker Pro application for information about each of the database files which are currently open. The information gathered from each database is summarized within a report file. (Info concerning Auto-Enter and Validation options are not gathered during this process because this info is not available via AppleScript.) This information is also used to generate Perl and SQL scripts used for creating Oracle database tables and moving the data from FileMaker to Oracle. FmPro Migrator Lite Edition makes use of tab separated or comma separated value data files exported from FileMaker Pro. The exported data is converted into SQL INSERT statements used to insert the data into the Oracle database. This data transfer method is intended for transferring small amounts of record data for a relatively small number of records.

FmPro Migrator STD Edition generates a Perl DBI/DBD::ODBC program which reads the data from FileMaker Pro via an ODBC connection and inserts the data into Oracle. This Perl program also supports transferring large text fields and Container field information from FileMaker Pro into Oracle.

----------------------- Usage Instructions ----------------------- 1) Fill in the fields within the FileMaker and Oracle 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 one (or multiple) FileMaker database files. 5) Press the Migrate button to generate the conversion scripts and database documentation files. 6) Examine the contact_management_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. 7A) If using the Lite edition of FmPro Migrator, decide whether to perform a tab delimited file export or a comma separated file export. Export the FileMaker data using one of the following two filenames: contact_managem_export_data.tab or contact_managem_export_data.csv 8A) Depending upon the type of file export performed, run either the contact_management_create_inserts_from_tab1.pl or contact_management_create_inserts_from_csv1.pl program to create the contact_management_insert_data1.sql file. The contact_management_insert_data1.sql file contains commit statements after each insert which are commented out. You may edit the resulting file to uncomment each commit statement with Find and Replace feature of a text processing program, or you may manually add a single commit statement at the end of the file. 9A) Transfer the contact_management_insert_data1.sql file via an ASCII FTP transfer to the Oracle server and execute it from within sqlplus. Alternately, if Oracle client or server software is installed on Mac OS X, sqlplus can make a remote connection to any Oracle server named within the tnsnames.ora file.

7B) 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 contact_management_fmpro_to_oracle_xfer_odbc1.pl program will fail. Then re-generate the conversion scripts. 8B) If using the STD edition of FmPro Migrator, transfer the contact_management_fmpro_to_oracle_xfer_odbc1.pl program to a Windows server. At the present time the contact_management_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. 9B) 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

10B) Create the contact_management_fmpro_odbc_dsn and contact_management_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. 11B) 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. 12B) Execute the contact_management_fmpro_to_oracle_xfer_odbc1.pl program to transfer the data from FileMaker to Oracle via an ODBC connection.

----------------------- FileMaker Folder Tab - Field Descriptions The FileMaker folder tab provides general info and FileMaker specific info about the conversion process.

Click the Browse button to select an existing or to create a new folder which will create the conversion scripts and reports created by FmPro Migrator. You must have write access to the output directory selected, otherwise an error dialog will be displayed.

[STD edition feature] Enter the name of the FileMaker application within the FileMaker App Name field (if necessary). The name within this field should represent the name of the FileMaker application or stand-alone solution file as it appears within the Finder. In most cases, this field will not need to be changed. However if you desire to retrieve database structure information from a stand-alone FileMaker solution file, enter the Finder name of the solution in this field. FmPro Migrator uses this information to send AppleScript commands to FileMaker in order to retrieve the structure information from the FileMaker database. If you enter an invalid program name here, the Open Databases field will display "Compiler Error" after pressing the Refresh button.

Open one or more FileMaker database files, then press the Refresh button. All of the FileMaker databases which are open will appear in the Open Databases field. There needs to be at least one database file open for the conversion process to be performed. Note: You must have unrestricted access to the database (with no password required) in order for FmPro Migrator to read the database structure info from the FileMaker database.

[STD edition feature] Enter the list of FileMaker text fields which will contain more than 4000 characters. Fields containing more than 4000 characters of text will be converted to Oracle CLOB columns when converted into the Oracle database table. The list of column names within this field should represent the Oracle column names for the equivalent FileMaker fields. To determine how each FileMaker field name will be converted for use within Oracle, generate the database report file (named: contact_management_report1.txt) by pressing the Migrate button. Examine the database report file contact_management_report1.txt and look at the Renamed As column in the Field Summary section of the report. Please see the Large Text Fields information within the Usage Notes section of this documentation file for more info about using CLOB columns with Oracle.

Destination Database menu - Select the version of the Oracle database which will be the destination for the data. Selecting Oracle 8 in this menu will generate dictionary managed tablespace creation code. Selecting Oracle 8i-9i will generate locally managed tablespace creation code. Note: Oracle 8 should be selected in this menu if large text or Container field data will be transferred from FileMaker to an Oracle 8.0.5, 8.1.5, 8.1.6, 8.1.7 or 9.0.1 database. Oracle CLOB and BLOB columns may be used with locally managed tablespaces starting with Oracle 9.2.0.

Path to Perl - This field contains the path to the Perl executable application for UNIX/Mac OS X servers. In most cases, no change will be needed to this field because the path to perl on Mac OS X is the default value. This value may need to be changed if the platform-independent Perl::ODBC program needs to be run on another type of UNIX server. If the Perl::ODBC program will be run on a Windows server, no changes need to be made to this field.

----------------------- Oracle Folder Tab - Field Descriptions

Service Name - The Oracle service name should be entered in the Service Name field if the insert data sql files require connection to a remote database. The service name is not required if the insert data sql files will be run on the Oracle database server.

Username - Enter the username which will own the new Oracle table which will be created for storing the FileMaker data. The Oracle tablename will be similar to the original FileMaker database name, however spaces will be replaced with underscores and special characters will be removed. A pre-existing user account will be used if the Use Existing checkbox is checked, otherwise a new user will be created using the parameters listed on this folder tab.

Password - Enter the password for the Oracle database account which will own the table.

Use Existing Username - checkbox - Check this checkbox to re-use an existing Oracle database account.

Data Tablespace - Enter the default tablespace name which will be used for creating a new Oracle database account.

Tablespace Filename - The Data Tablespace filename should include the full path and filename of the datafile used by the new Oracle database account. This info is only required if a new tablespace is being created.

Use Existing Data Tablespace - checkbox - Check this checkbox to re-use an existing Data Tablespace instead of creating a new one.

INDEX Tablespace - Enter the tablespace name which will be used for storing the primary key constraint index.

INDEX Tablespace Filename - The INDEX Tablespace filename should include the full path and filename of the datafile used by primary key constraint index. This info is only required if a new tablespace is being created.

Use Existing INDEX Tablespace - checkbox - Check this checkbox to re-use an existing INDEX Tablespace instead of creating a new one.

# ----------------------- 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 contact_management_create_inserts_from_csv1.pl or contact_management_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 contact_management_fmpro_to_oracle_xfer_odbc1.pl file. The contact_management_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 [STD edition or higher] 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 contact_management_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 [STD edition or higher] The best way to transfer data in large text fields and FileMaker Container fields is by using the contact_management_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 contact_management_fmpro_to_oracle_xfer_odbc1.pl program transfers data for all FileMaker field types including text fields up to 64000 bytes and images/movies/sound 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 contact_management_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 (if using FileMaker 6 or higher). There is no FileMaker ODBC driver supplied with FileMaker 5.5 on Mac OS X, therefore the contact_management_fmpro_to_oracle_xfer_odbc1.pl program has to 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: contact_management_oracle_odbc_dsn - is used for connecting to the Oracle database contact_management_fmpro_odbc_dsn - is used for connecting to the FileMaker database

ODBC driver and client software installation The contact_management_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 contact_management_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 There is no data type within an Oracle database which is equivalent to the Repeating Fields feature within FileMaker. Data from within repeating fields is copied correctly to the Oracle database, however the data appears within one 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 would normally be supported 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.

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 STD Edition 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. FmPro Migrator Lite edition requires the opening of each file individually prior to starting the conversion process because only one file at a time is processed.

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 withh 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 contact_management_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 contact_management_fmpro_to_oracle_xfer_odbc21.pl program.

Working with FileMaker Pro on Windows - FmPro Migrator STD Edition can work with FileMaker Pro databases running on Windows servers for the data transfer phase of the conversion process. However a copy of the FileMaker Pro database must be running under Mac OS X in order for FmPro Migrator to gather the list of fields and field attributes information. 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 - FmPro Migrator STD Edition can read the database structure from stand-alone solutions files created with FileMaker Pro Developer on Mac OS X. If the developer of the solution has enabled file exporting, then a tab or comma separated value export file may be created. However it will not be possible to extract the information from the file by using an ODBC connection because networking capability is not available with FileMaker stand-alone files. The stand-alone file will also need to be the Macintosh version of the executable in order for Apple Events used by FmPro Migrator to read the structure of the database file.

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.

Primay 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.

Data Export Format - Scripts generated by FmPro Migrator expect that the default formatting will be used for comma separated and tab separated format export files. The export option of "Don't Format Output" is especially important for Date and Time fields in order to match the format which has been specified for Oracle. If this export option needs to be changed, then consider whether the Oracle Date Format model 'YYYY-MM-DD HH24:MI:SS' also needs to be changed.

----------------------- Troubleshooting ----------------------- ORA-1843 - not a valid date SQL-2203 - numeric value out of range If these errors occur while inserting data, check to make sure that a FileMaker Time format field is not being inserted into an Oracle Date field. One option which can be used to resolve this problem 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 be 'YYYY-MM-DD HH24:MI:SS' in order to 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.

ORA-1401 - inserted value too large for column ORA-1461 - can bind a LONG value only for insert into a LONG column These errors will occur if the amount of data being inserted from FileMaker Pro exceeds the size of the Oracle column. One solution to this problem is to use Oracle CLOB columns to store FileMaker Pro fields containing large amounts of text (over 4000 characters). When using FmPro Migrator STD Edition, this feature may be enabled by listing the Oracle CLOB columns in the Large Text Fields field on the FileMaker folder tab.

Compiler Error in Open Databases Field - FmPro Migrator attempts to use AppleScript commands to send Apple Events to the FileMaker Pro database or stand-alone application. If the application name is entered incorrectly, the Apple Events will not find a valid recipient and will fail. Solution: Enter a valid FileMaker application name within the FileMaker App Name field.