|
 |
 |
|
|
|
||
|
|
|
|
||||||||||||||||||||||||
 |
 |
 |
5/15/2004
Note: Additional information is contained within the <database>_instructions.txt file created during the file generation process. The <database>_instructions.txt file contains the actual file names created by FmPro Migrator. This document uses an example FileMaker 6 database table named "example" which is migrated to PostgreSQL. The files created for your database will use the names of the database tables in your FileMaker database.
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 Other 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.
The ODBC DSN entered into FmPro Migrator for the source and destination
databases is used to generate scripts.
2) Select or create the destination directory for the migration 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. Transfer this file to the PostgreSQL server and execute
this code from the command line as the postgres user to create the table
which will contain the FileMaker data. [If a migration is done from
FileMaker 7.0v1 to PostgreSQL with FmPro Migrator for Windows, please
see the troubleshooting notes for more info about column type issues.]
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_postgresql_xfer_odbc1.pl
program will fail. Then re-generate the conversion scripts.
8) Transfer the example_fmpro_to_postgresql_xfer_odbc1.pl program to
a computer running Windows [or VirtualPC on MacOS X]. At the present
time the example_fmpro_to_postgresql_xfer_odbc1.pl Perl script needs
to be run from Windows due to the lack of an ODBC driver for Mac OS
X.
9) Install the Perl DBI and DBD::ODBC modules on the Windows computer
along with the FileMaker ODBC Driver, and the PostgreSQL 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_fmpro_odbc_dsn ODBC DSN as a System
DSN 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.
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.
12) Create the example_pg_odbc_dsn ODBC DSN as a System DSN in the ODBC
Control Panel. Fill in the Database, Server, and Port fields within
this DSN. Change the Max LongVarChar parameter from 8190 to 64000 on
Page 1 of the configuration panel. (Page 1 is shown after clicking the
Datasource button.)
13) Execute the example_fmpro_to_postgresql_xfer_odbc1.pl program from
a Windows command prompt to transfer the data from FileMaker to PostgreSQL
via an ODBC network connection.
------------ 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_odbc_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 MacOS X) 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.
----------------------- 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/EE edition feature] Click the Select button then select the name of the FileMaker application which will appear in 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 select an invalid program name, 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.
[For Windows, enter the ODBC DSN name and select your FileMaker database
version from the Source Database menu prior to pressing the Refresh
button. Make sure that the Local and Remote Data Access Companion plug-ins
are enabled for each FileMaker database.] 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/EE edition feature] Enter the list of FileMaker text
fields which will contain more than 255 characters. Fields containing
more than 255 characters of text will be converted to PostgreSQL TEXT
columns when converted into the PostgreSQL database table. The list
of column names within this field should represent the PostgreSQL column
names for the equivalent FileMaker fields. To determine how each FileMaker
field name will be converted for use within PostgreSQL, generate the
database report file (named: example_report1.txt) by pressing the Migrate
button. Examine the database report file example_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 text columns
with Access.
Destination Database menu - Select PostgreSQL as the database which will be the destination for the data.
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.
----------------------- Other Folder Tab - Field Descriptions
Hostname - The hostname or TCP/IP address of the PostgreSQL server should be entered in the Hostname field.
Port - The default TCP/IP port used by PostgreSQL is 5432. This value will not normally need to be changed if standard install and startup parameters were used to install the PostgreSQL database.
Database Name - Enter the name of the PostgreSQL database into the Database Name field. This is the name which was specified as the database was created.
Username - Enter the username which will own the new PostgreSQL table being created for storing the FileMaker data. The username entered in this field must already exist in the PostgreSQL database. The PostgreSQL tablename will be similar to the original FileMaker database name, however spaces will be replaced with underscores and special characters will be removed.
Password - Enter the password for the PostgreSQL database account which will own the table.
----------------------- Usage Notes -----------------------
Large Text Fields [STD/EE edition]
FileMaker fields containing more than 255 characters of text will be
converted to PostgreSQL TEXT columns when converted into the PostgreSQL
database. The list of these column names should be entered into the
Large Text Fields field. Data is transferred between FileMaker and PostgreSQL
by the example_fmpro_to_postgresql_xfer_odbc1.pl program. This program
makes use of bind variables to specify field names and ODBC field types.
Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to
be transferred to the PostgreSQL database, these fields should be removed
before generating the migration 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 driver and client software installation [STD/EE edition]
The only way to transfer data in large text fields and FileMaker Container
fields is by using the example_fmpro_to_postgresql_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 PostgreSQL.
The example_fmpro_to_postgresql_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 example_fmpro_to_postgresql_xfer_odbc1.pl
program needs to be installed on a Windows computer (or VirtualPC) which
has PostgreSQL ODBC driver 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_postgresql_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 a PostgreSQL
database running on Windows. Note: If FileMaker 7 is the source database,
the example_fmpro_to_postgresql_xfer_odbc1.pl script must be run from
the same Windows computer which is running the FileMaker 7 database.
Repeating Fields
There is no data type within a PostgreSQL database which is exactly
equivalent to the Repeating Fields feature within FileMaker. Data from
within repeating fields is copied correctly to the PostgreSQL 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 PostgreSQL database with the child records
having a common foreign key value pointing back to the primary key of
the parent record.
Note: In order for all repeating field data values to be transferred
from FileMaker, the data type must be a Text field. If repeating values
are present within FileMaker numeric fields, only the first value will
be transferred to the destination database. Therefore it is recommended
that repeating fields be converted to Text fields within FileMaker unless
the FileMaker database is being redesigned using a relational database
structure prior to being migrated.
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 migration 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 PostgreSQL lo (binary large object) columns. The lo
column type is created as a custom object type within the PostgreSQL
database by the table creation sql file. 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 PostgreSQL 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 into a FileMaker Pro database via an ODBC connection on either Windows or Macintosh platforms.
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_postgresql_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_postgresql_xfer_odbc21.pl
program.
Working with Stand-Alone Solutions Files - FmPro Migrator STD/EE Editions can read the database structure from stand-alone solution files created with FileMaker Pro Developer. On Windows, change the extension of the stand-alone solution files to match the extension used by FileMaker. Then open the file within FileMaker. On MacOS X, drag and drop the icon of the stand-alone solution file onto the icon of the FileMaker application in the dock in order to open the file in FileMaker.
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 PostgreSQL database table. The primary key column of the PostgreSQL database table will be migrated as primary key column using a compatible datatype derived from the original FileMaker database. 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. On Windows, the first numeric NOT NULL field is selected as the primary key because UNIQUE attribute for a field cannot be determined via an ODBC connection to FileMaker.
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)
----------------------- Troubleshooting -----------------------
[Windows issue] After installing the PostgreSQL ODBC driver on Windows, the driver does not show up within the Drivers tab of the Windows ODBC Data Source Administrator Control Panel.
Solution: After performing a PostgreSQL ODBC driver installation,
the installation of the driver will generally be visible within the
Windows registry and the C:WINNTODBC_INST.INI file.
Information about configured ODBC DSNs is stored within the C:WINNTODBC.INI
file.
To work-around this issue, you may create a System DSN within the ODBC
Data Source Administrator Control Panel for any database. This Control
Panel can be kept open throughout the following steps, it does not have
to be closed when editing the ODBC.INI file. For this example, a FileMaker
Pro DSN named test_dsn was created. Then open the ODBC.INI file with
a text editor and find the test_dsn line item near the top of the file.
The DSN will look similar to the following example:
test_dsn=FileMaker Pro (32 bit)
Later on in the file there will be the following text showing the location
of the dll file:
[test_dsn]
Driver32=C:Program FilesCommon FilesODBCFileMakerFMFMP50.DLL
Change the DSN to point to whichever PostgreSQL driver
you want to use instead of the FileMaker Pro driver:
test_dsn=PostgreSQL (32 bit)
[test_dsn]
Driver32=C:WINNTsystem32psqlodbc.dll
Save the changes to this text file. Note: Save the changes with a text editor like UltraEdit (www.ultraedit.com), don't save the changes with a program which might add its own formatting to the file.
Open the test_dsn DSN with the ODBC Data Source Administrator
Control Panel.
The DSN should now open with the PostgreSQL configuration panel.
Error: psql:example_create_table1.sql:36: ERROR: function
int4out(lo) does not exist
This error may be observed when running the example_create_table1.sql
file to create the new database table within the PostgreSQL database.
Solution: If container fields exist within the FileMaker
database, an object type of "lo" needs to be created within
the PostgreSQL database. The "lo" object type is used to define
a special column type of "lo" which is used to store binary
large object data from the FileMaker database. This type is used because
it is supported by the psqlodbc ODBC driver.
If the PostgreSQL database version is less than 7.3, then the "CREATE
TYPE lo" statement should be uncommented and run within the example_create_table1.sql
file. If this command is executed on a PostgreSQL 7.3 or higher database,
error 36 will be displayed. This means that the "CREATE DOMAIN
lo" statement should be used.
[Windows only issue] When performing a FileMaker 7 to PostgreSQL migration, FileMaker container fields are migrated as text fields and every field is set to require "Not Empty" data validation. This occurs when FileMaker 7.0v1 on Windows is used as the source of the migration in FmPro Migrator.
FmPro Migrator for Windows makes an ODBC connection to
the FileMaker database which is being migrated in order to obtain database
structure information. When FileMaker 7 is used as the source of the
migration, the ODBC driver incorrectly reports that Container fields
are text fields and that all fields are "NOT NULL" fields.
There are two solutions to this issue:
Solution1: If a version of the database file is available within the
FileMaker 3/4/5/6 format, then use the older version of the file as
the source of the migration for FmPro Migrator. Versions of the FileMaker
ODBC driver prior to FileMaker 7 do not display this behavior. FmPro
Migrator will query the database and obtain the correct data types and
NULL/NOT NULL status for creating the example_create_table1.pl file.
Use of the older database file is only required when creating the migration
scripts. After the scripts have been created, use the FileMaker 7 version
of the file for the actual import of the data into the new PostgreSQL
database table.
Solution2: If an older version of the source database file is not available,
then use FmPro Migrator to create the migration scripts from the FileMaker
7 source database file. Then make changes to the contents of the example_create_table1.pl
file prior to creating the new PostgreSQL table. To make these change
manually, change the VARCHAR(254) field types to "lo" for
each of the container fields. Then perform a search and replace within
a text editor to change all "NOT NULL" text to "NULL".
It is likely that very few database fields will require "NOT NULL"
validation so these few fields can then be updated manually.
Error: DBI connect('example_pg_odbc_dsn','HASH(0x1a7f070)',...)
failed: at example_fmpro_to_postgresql_xfer_odbc1.pl line 113
Can't connect to the PostgreSQL example_pg_odbc_dsn database: FATAL:
no pg_hba.conf entry for host "10.1.0.15", user "HASH(0x1a7f070)",
database "database1"
(SQL-28000)(DBD: db_login/SQLConnect err=-1)
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
Solution: This error means that the TCP/IP address or host name of the computer running the example_fmpro_to_postgresql_xfer_odbc1.pl script has not been added to the pg_hba.conf file on the PostgreSQL server. Edit the pg_hba.conf file on the PostgreSQL server to allow access to the server from the computer running the migration scripts, then issue the pg_ctl reload command to reload the configuration information within the pg_hba.conf file.
Error: DBD::ODBC::st execute failed: ERROR: value too
long for type character varying(254) (SQL-HY000)(DBD: st_execute/SQLExecute
err=-1) at example_fmpro_to_postgresql_xfer_odbc1.pl line 139.
DBD::ODBC::st execute failed: ERROR: value too long for type character
varying(254) (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at example_fmpro_to_postgresql_xfer_odbc1.pl
line 139.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
Solution: This error means that an attempt was made to
insert more than 254 characters into a VARCHAR(254) field within the
PostgreSQL database. The recommended solution to this problem is to
run the example_fmpro_max_fieldsize1.pl program to determine the maximum
number of characters located within each FileMaker database field.
Solution1: PostgreSQL columns in the destination database may be designed
to handle up to 4096 characters of information. Modifying the column
sizes within the example_create_table1.sql file before creating the
PostgreSQL table can solve this problem.
Solution2: FileMaker fields which contain more than 4096 text characters
may be migrated to PostgreSQL TEXT columns which can contain up to 1GB
of text. Add the names of these large text columns to the Large Text
Fields field on the FileMaker tab of FmPro Migrator, then press the
Migrate button to regenerate the migration scripts.
|
||
|
|
|
|
||
