|
 |
 |
|
|
|
||
|
|
|
|
||||||||||||||||||||||||
 |
 |
 |
Note: Additional information is contained within the <database name>_instructions.txt file created during the file generation process. The <database name>_instructions.txt file contains the actual file names created by FmPro Migrator. This document uses an example FileMaker Pro database named "example", the file created for your database will use the name of the FileMaker database you are converting.
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.
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.
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) Create a new Sybase database and a new Sybase database user account
(if needed).
7) Examine the example_create_table1.sql file to determine if any changes
need to be made. Transfer this file to the Sybase server and execute
this code with the command line isql utility to create the table which
will contain the FileMaker data. Note: If the Sybase database is running
on MacOS X, the example_create_table1.sql file needs to be modified
after it is generated to replace UNIX line endings to Macintosh line
endings before being executed by the isql utility. This conversion can
be done with BBEdit on MacOS X.
8) 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_sybase_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts. [If a migration
is done from FileMaker 7.0v1 to Sybase with FmPro Migrator for Windows,
please see the troubleshooting notes for more info about column type
issues.]
9) Transfer the example_fmpro_to_sybase_xfer_odbc1.pl program to a Windows
server. At the present time the example_fmpro_to_sybase_xfer_odbc1.pl
Perl script needs to be run from a Windows server due to the lack of
a FileMaker ODBC driver for Mac OS X.
10) Install the Perl DBI and DBD::ODBC modules on the Windows server
along with the FileMaker ODBC Driver and the Sybase ODBC driver. The
Sybase Open Client software will automatically be installed as part
of the Sybase installation process, or it can be installed separately.
(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
11) Create the example_fmpro_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.
12) Create the example_sybase_dsn entry in the ODBC Control Panel.
13) 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.
14) Execute the example_fmpro_to_sybase_xfer_odbc1.pl program to transfer
the data from FileMaker to Sybase via an ODBC network connection.
Use the following instructions if there are repeating
fields which need transferred from FileMaker to Sybase.
15) If repeating fields need to be extracted from the FileMaker database,
use the example_repeating_fields_create_table1.sql SQL script to create
the Sybase example_repeating table for the repeating fields data. The
column sizes defined within this script should be adjusted as necessary
prior to creating this table.
16) Change each FileMaker repeating field to be a TEXT field so that
each of the repeating values will be transferred properly. (Note: Do
not make this change prior to generating the migration scripts or the
repeating fields will be created with the wrong datatypes. Also, do
not make this change before transferring the data from FileMaker to
Sybase in Step #14 of this procedure.)
17) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer
the repeating fields data from FileMaker to Sybase via an ODBC network
connection.
18) Execute the example_drop_repeating_fields_columns1.sql SQL script
to drop the unneeded repeating fields columns from the Sybase example
table.
------------- 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_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 a Macintosh) 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.
----------------------- Usage Notes -----------------------
Large Text Fields
FileMaker fields containing more than 255 characters of text will be
converted to Sybase TEXT columns when migrated to the Sybase database.
The list of these column names should be entered into the Large Text
Fields field. Data is transferred between FileMaker and Sybase by the
example_fmpro_to_sybase_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 Sybase 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 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_sybase_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 Sybase.
The example_fmpro_to_sybase_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_sybase_xfer_odbc1.pl
program needs to be installed on a Windows server which has Sybase Open
Client and ODBC 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_sybase_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 Sybase
database.
Repeating Fields
If repeating fields are detected within the FileMaker database, two
extra scripts will be created in order to extract the repeating fields
data from FileMaker to Sybase. The example_repeating_fields_create_table1.sql
creates a new database table for the related data contained within the
repeating fields. The example_repeating_fields_xfer_odbc1.pl should
then be run in order to extract the repeating fields data into the new
example_repeating database table.
Notes:
It is necessary for FmPro Migrator to accurately determine the primary
key field within the FileMaker database in order to properly generate
the repeating fields extraction scripts. The primary key for the primary
database table is used to reference each repeating field record. Therefore
at least one field in the main database table must be set within FileMaker
with the UNIQUE and NOT EMPTY validation parameters. It is recommended
that this field be a numeric field using an auto-enter serial number
within FileMaker. This field will then be used as the primary key column
for the transferred data.
In order for all repeating field data values to be transferred from
FileMaker, the data type within FileMaker must be a Text field. Each
repeating field entry is separated by an ASCII (29) character which
is used by FileMaker Pro to separate values within repeating fields.
The example_repeating_fields_xfer_odbc1.pl script splits the data values
into separate records by using the ASCII (29) character as a record
delimiter and writes out each repeating field value into a new record
within the example_repeating table.
If repeating values are present within FileMaker numeric, date or time
fields, only the first value will be transferred to the destination
database. Therefore each repeating field needs to be converted to Text
fields within the Define Fields dialog box within FileMaker. This needs
to be done after creating the migration scripts. This change should
not be made within FileMaker before the migration scripts are created,
or the repeating field database table will be created with the wrong
datatypes (i.e. all fields will be TEXT).
FileMaker container fields which are configured as repeating fields
cannot be extracted.
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/EE 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 Sybase Image 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 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 Sybase 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_sybase_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_sybase_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator STD/EE Editions 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 can read the database structure from stand-alone solutions files created with FileMaker Pro Developer on Mac OS X. 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.
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 Sybase database table. The primary key column of the Sybase database table will be migrated as a primary key column using a NUMERIC datatype with the IDENTITY attribute. 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 before generating conversion scripts.
Sybase IDENTITY columns - Sybase databases support an IDENTITY column parameter. The IDENTITY starting and increment values may be reset by using bcp to dump and re-import the database table.
Exporting Data - All table data formats may be exported via the bcp program supplied with the database by Sybase. The following command line provides a bcp example using the example database table:
bcp database1.user1.example out example.dmp -n -U user1 -P user1pwd
Importing Data - All table data formats may be imported via the bcp program as shown with the following example:
bcp database1.user1.example in example -E -n -U user1 -P user1pwd
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)
|
||
|
|
|
|
||
