.com Solutions Inc. - Support - FmPro Migrator

How to generate Perl CGI scripts

Note: Additional information is contained within the <database layout>_instructions.txt file created during the file generation process. The <database layout>_instructions.txt file contains the actual file names created by FmPro Migrator. This document uses an example FileMaker Pro layout named "example", the files created for your database will use the name of the FileMaker database you are converting. A separate set of Perl CGI scripts and instructions will be created for each layout within the FileMaker database.

Overview
FmPro Migrator Enterprise Edition (EE) generates Perl CGI scripts for each migrated FileMaker Pro layout for MySQL and Oracle database migrations. These Perl CGI scripts provide insert, query, update and delete functionality for the migrated data via a web based interface. These scripts allow the web developer to utilize external HTML files for the header and footer sections of the dynamically displayed HTML pages. A webmaster can update these external HTML files without modifying the Perl code used to display the database information.

The generated Perl code is designed to be easily updated due to its organized structure and documentation. Commonly changed parameters such as connection strings, table names and error messages are defined near the start of each script. The generated scripts utilize commonly available open source Perl modules including CGI.pm and DBI/DBD in order to make it practical for a developer to make more extensive changes.

One minor change which can easily be made to the generated scripts involves the display of visible borders for HTML tables. By default, the generated scripts set the table border to "1" which makes the table borders visible. To make the table borders invisible, just change the following line of code to set the border to "0":
print '<table border="1">'; # start table HTML command

Generated CGI Scripts
The following Perl CGI scripts are generated by FmPro Migrator for this layout:
Layout Name: example
Database Table Name: asset_management2

example_submit1.html -
The HTML submit page which POSTs information to the example_submit1.pl CGI script.

example_submit1.pl -
The CGI script which validates data entry and inserts new records into the database table.

example_header1.html -
The external HTML file used for creating the HTML page header for dynamic web pages.

example_footer1.html -
The external HTML file used for creating the HTML page footer for dynamic web pages.

example_query1.html -
The HTML query page which Posts information to the example_query1.pl CGI script.

example_query1.pl -
The CGI script which queries the database table and displays a list of results. Up to 30 results are displayed per page.

example_display_record1.pl -
The CGI script which displays one complete record (including GIF/JPEG images).

example_update1.html -
The HTML query for update page which Posts information to the example_update1.pl CGI script.

example_update1.pl -
The CGI script which queries the database table for update purposes and displays a list of results. Up to 30 results are displayed per page.

example_update_record1.pl -
The CGI script which displays one complete record for update (including GIF/JPEG images). Image/BLOB data update options include Keep, Replace and Remove.

example_update_record_validate1.pl -
The CGI script which validates data entry for record updates and updates the information in the database table.

example_delete1.html -
The HTML query for delete page which Posts information to the example_delete1.html CGI script.

example_delete1.pl -
The CGI script which queries the database table for delete purposes and displays a list of results. Up to 30 results are displayed per page.

example_delete_record_display1.pl -
The CGI script which displays one complete record for deletion. The record is not deleted unless the "Delete Record" button is pressed on this page.

example_delete_one_record1.pl -
The CGI script which accepts the POST information from the example_delete_record_display1.pl script, validates the referrer URL and then deletes the record specified by the example_delete_record_display1.pl script. For security purposes the referrer URL must match "http://10.1.0.22/Scripts/example_delete_record_display1.pl" in order to prevent a web attacker from submitting random delete requests from another website.

example_instructions1.txt -
The customized documentation file for the scripts/files generated for the example layout.

Additional Usage Instructions
Prior to generating CGI scripts some consideration needs to be given for the fields which will be included within each layout. In general, each layout should have one column which is considered to be the primary key column for the database table. The primary key should be a column which contains unique values allowing each row of data to be uniquely identified. The primary key is often configured to be an Auto-Enter Serial number field in FileMaker.
***** WARNING *****
If a primary key column is not utilized on a layout, unexpected data loss may occur. FmPro Migrator will attempt to automatically determine which field of each layout should be used as the primary key column. FmPro Migrator will pick the first layout field defined with Auto-Enter Serial Number attributes on the Fields tab within the CGI section of the application. If none of the fields are configured with the Auto-Enter Serial Number attribute, then the first field of the layout is selected as the primary key column. If a record deletion is performed on a table in which the selected primary key column does not contain unique values, then multiple records may be deleted at the same time.

1) Fill in the database related fields within the FmPro Migrator application and generate migration files for the destination database. As the migration files are created, a file named layouts.txt will also be created within the destination directory. This file contains layout-specific information regarding the names and types of fields included within each FileMaker layout.
2) Click on the CGI tab within the FmPro Migrator application.
3) Click the General tab (if necessary).
4) Click the Refresh Layouts button. The Refresh Layouts button loads the data within the layouts.txt file from the output directory where migration scripts were generated. During the loading of this file, the Layouts and Fields tabs will be populated with information.
Tip: If there is information which you want to remain the same for each Layout and Field, enter this information into the empty Layouts and Fields tabs before loading the layouts.txt file with the Refresh Layouts button. Then the default information you have entered on the Layouts and Fields tabs will be copied to each new Layout and Field screen which is created during the loading of the layouts.txt file.
5) Fill in the fields on the General tab for the web server you will be using.
6) Click on each of the Layout names to make changes as necessary.
7) Click on each of the Field names (within the scrollable Fields field) and specify Auto-Enter, Value List and Data Validation parameters for each field.
8) Select Save from the File menu to save the information you have just entered. As you refine your CGI scripts, you may re-load and update the CGI-specific information at any time. However if changes are made to the underlying FileMaker database fields or layouts this process should be started over from step #1. Otherwise, field and layout changes to the FileMaker database will not be reflected in the Perl CGI scripts which are generated by FmPro Migrator.
9) Click the Generate CGI button to generate Perl CGI scripts. A series of Perl CGI scripts will be generated for each layout within the FileMaker database. The names of these scripts will be prefixed with the name of the FileMaker layout for which they are designed.
10) Transfer the HTML and Perl CGI scripts to your web server for testing.

Web Server Setup Instructions - Windows - MySQL database
In order to execute Perl CGI scripts, your web server may require some configuration. First, Perl will need to be installed on your web server. If your web server is running MacOS X, Linux or UNIX, you will probably find that Perl was installed when the operating system was installed. For web servers running on Windows, you will need to download and install the ActiveState Perl distribution onto your server. Make sure that the ".pl" file extension is associated with the Perl.exe application - this is the default selection during the install. Also make sure that Perl files are permitted to be executed by the IIS web server - this is also a default option. Make note of the location where the Perl executable has been installed. This information is used for the Path to Perl field on the FileMaker tab of the FmPro Migrator application. For Windows web servers, this path will generally be: C:\Perl\bin\Perl.exe

The CGI.pm Perl module is included as a standard component with current Perl distributions so you should not generally need to install it. If a reinstall of the CGI.pm module is required, it may be downloaded from www.cpan.org.

For database access by scripts running on the web server it is necessary to first install database client software and DBI/DBD driver software on the web server.
For Windows servers:

Install the MySQL database client only or client and server software on the web server. It is generally recommended that the MySQL client software should be installed before the DBI/DBD software is installed (though the order of installation is not important on Windows servers if pre-compiled DBD driver software is installed via PPM). Configure the MySQL user account for proper authentication to connect to the MySQL database. This will generally mean enabling access to the database from the IP address of the web server machine. Test the database connection with the mysql utility.
On UNIX servers, the CGI scripts generated by FmPro Migrator utilize the DBI/DBD::mysql driver.

Install the Perl DBI and DBD::mysql modules on the web server using the ActiveState PPM feature. Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-mysql
PPM> quit

MySQL LONG/BLOB data notes:
Each Perl CGI script generated by FmPro Migrator which processes LONG/BLOB data is designed to limit the amount of data to the value for the Max Submit Size on the General tab of FmPro Migrator. The Max Submit Size should be configured to handle the maximum amount of data which will be processed by the script in one submission. Therefore if you have a script which needs to handle 10,000 bytes of text plus the uploading of five 100,000 byte images in the same submission, then the Max Submit Size should be set for 510,000 bytes.

The MySQL database also includes a max-allowed-packet variable which limits the maximum amount of data which can be written into a database row at one time. If large images can't be inserted into the database, check for errors in the web server error.log file like the following:
Got a packet bigger than 'max_allowed_packet' at scriptname line#

This MySQL variable may be increased by entering the updated value into the mysqld configuration file (my.ini on Windows):
--max-allowed-packet=8M

Once the database software is installed, copy the generated scripts to the /cgi-bin directory if you are using the Apache web server on Windows. Otherwise, copy the scripts to the IIS directory pointed to by the /Scripts alias. Copy the HTML files into the documents directory. If the URLs within the scripts are different from your web server structure, you may either make the changes in FmPro Migrator and regenerate the scripts, or update the scripts manually.

The Perl scripts will be executed by the web server software whenever they are accessed because they are associated with the Perl executable by virtue of the fact that each script has a "PL" extension.

Web Server Setup Instructions - UNIX - MySQL database
In order to execute Perl CGI scripts, your web server may require some configuration. First, Perl will need to be installed on your web server. If your web server is running MacOS X, Linux or UNIX, you will probably find that Perl was installed when the operating system was installed. For web servers running on Windows, you will need to download and install the ActiveState Perl distribution onto your server. Make sure that the "PL" file extension is associated with the Perl.exe application - this is the default selection during the install. Also make sure that Perl files are permitted to be executed by the IIS web server - this is also a default option. Make note of the location where the Perl executable has been installed. This information is used for the Path to Perl field on the FileMaker tab of the FmPro Migrator application. For Windows web servers, this path will generally be: C:\Perl\bin\Perl.exe

For database access by scripts running on the web server it is necessary to first install database client software and DBI/DBD driver software on the web server.
For UNIX/Linux/MacOS X servers running the Apache web server software:

Install the MySQL database client only or client and server software on the web server. The MySQL software must be installed before the DBI/DBD software is installed. Configure the MySQL user account for proper authentication to connect to the MySQL database. This will generally mean enabling access to the database from the IP address of the web server machine. Test the database connection with the mysql utility.
On UNIX servers, the CGI scripts generated by FmPro Migrator utilize the DBI/DBD::mysql driver. Download and install the DBI/DBD::mysql modules according to the readme file included with the software.

This MySQL variable may be increased by entering the updated value into the mysqld configuration file (/etc/my.conf on UNIX/Linux):
--max-allowed-packet=8M

Make each script executable by the user who owns the web server software:
chmod +x scriptname

The Perl scripts will be executed by the web server software whenever they are accessed because they are associated with the Perl executable by virtue of the fact that each script contains the line: #!/usr/bin/perl

Web Server Setup Instructions - Windows - Oracle database
In order to execute Perl CGI scripts, your web server may require some configuration. First, Perl will need to be installed on your web server. If your web server is running MacOS X, Linux or UNIX, you will probably find that Perl was installed when the operating system was installed. For web servers running on Windows, you will need to download and install the ActiveState Perl distribution onto your server. Make sure that the "PL" file extension is associated with the Perl.exe application - this is the default selection during the install. Also make sure that Perl files are permitted to be executed by the IIS web server - this is also a default option. Make note of the location where the Perl executable has been installed. This information is used for the Path to Perl field on the FileMaker tab of the FmPro Migrator application. For Windows web servers, this path will generally be: C:\Perl\bin\Perl.exe

For database access by scripts running on the web server it is necessary to first install database client software and DBI/DBD driver software on the web server.
For Windows servers:

Install Oracle database client software on the web server. It is generally recommended that the Oracle client software should be installed before the DBI/DBD software is installed (though the order of installation is not important on Windows servers if pre-compiled DBD driver software is installed). Configure the Oracle client software to enable connections to the database thru the tnsnames.ora file. Test the database connection with the tnsping.exe utility to test the service name entered into the tnsnames.ora file. As an additional test, use sqlplus to connect to the database with the service name and database account/password which will be used by the CGI scripts.
On Windows servers, the CGI scripts generated by FmPro Migrator utilize the DBI/DBD::ODBC driver instead of the DBD::Oracle driver. This is done in order to workaround a limitation with the DBD::Oracle driver installed by the ActiveState PPM feature. On Windows systems, most Perl modules are easily installed by using the ActiveState PPM feature included with the ActiveState Perl distribution. However the version of the DBD::Oracle module which is installed by PPM appears to have been compiled for Oracle 7 instead of Oracle 8 - which causes ORA-00932 inconsistent datatypes errors when using CLOB/BLOB columns. The extended text of the error message states: Can't select LOB fields using DBD::Oracle built for Oracle 7. Thus the Windows web server Perl CGI scripts generated for Oracle databases utilize the DBD::ODBC driver rather than require every Windows webmaster to manually compile DBD::Oracle for Oracle 8.

Install the Perl DBI and DBD::ODBC modules on the web server using the ActiveState PPM feature. Install commands follow:
PPM
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit

Create the asset_management2_ora_dsn entry in the ODBC Control Panel. Make sure that the Enable LOBs checkbox is checked on the Oracle tab of the ODBC Driver Configuration window. Click the Test Connection button to verify that you can connect to the database from the Driver Configuration window.

If the Apache web server is being used on Windows, the ScriptAlias /Scripts/ should be aliased to /cgi-bin/ directory in the httpd.conf file.

Web Server Setup Instructions - UNIX - Oracle database
In order to execute Perl CGI scripts, your web server may require some configuration. First, Perl will need to be installed on your web server. If your web server is running MacOS X, Linux or UNIX, you will probably find that Perl was installed when the operating system was installed. For web servers running on Windows, you will need to download and install the ActiveState Perl distribution onto your server. Make sure that the "PL" file extension is associated with the Perl.exe application - this is the default selection during the install. Also make sure that Perl files are permitted to be executed by the IIS web server - this is also a default option. Make note of the location where the Perl executable has been installed. This information is used for the Path to Perl field on the FileMaker tab of the FmPro Migrator application. For Windows web servers, this path will generally be: C:\Perl\bin\Perl.exe

Install Oracle database client software on the web server. The Oracle client software must be installed before the DBI/DBD software is installed. Configure the Oracle client software to enable connections to the database thru the tnsnames.ora file. Test the database connection with the tnsping utility to test the service name entered into the tnsnames.ora file. As an additional test, use sqlplus to connect to the database with the service name and database account/password which will be used by the CGI scripts.
On UNIX servers, the CGI scripts generated by FmPro Migrator utilize the DBI/DBD::Oracle driver. Download and install the DBI/DBD::Oracle modules according to the readme file included with the software.

Once the database software has been installed, copy the generated scripts to the Apache web server configured /cgi-bin directory. Copy the HTML files into the documents directory. If the URLs within the scripts are different from your web server structure, you may either make the changes in FmPro Migrator and regenerate the scripts, or update the scripts manually.

Make each script executable by the user who owns the web server software:
chmod +x scriptname

Notes for MacOS X web servers:
Once a production release of Oracle 9i client software is available for MacOS X, follow the standard instructions listed above. Until a production software release is available, it will be necessary to use UNIX/Linux-based web servers to run these scripts.