.com Solutions Inc. - logo  
Products - Header Buttons
.
Repgen bullet item 7b Installgen bullet item 7b FmPro Migratorbullet item 7b CGIScripter
 .
.
.

. 		.

FmPro Migrator - MySQL Files - Title Graphic

FmPro Migrator Features and Benefits

FmPro Migrator Demo Available for download...

Bookmark This Page

email a friend

.

#!/usr/bin/perl
# Script: contact_management_fmpro_to_mysql_xfer_odbc1.pl
# Features: This Perl program copies all
# of the records from the FileMaker
# database, then inserts the records
# into the MySQL database.
#
# Requirements:
# Perl DBI module
# Perl DBD::ODBC module reads data from FileMaker
# Perl DBD::mysql module writes data into MySQL
# MySQL software must be installed on the
# computer running this program.
# FileMaker ODBC driver.
# FileMaker must be running in Multi-User mode
# with the Local and Remote Data Access Companions
# enabled. The ODBC Max Text Length parameter
# needs to be increased from 255 to 65000.
# The name of the FileMaker database must not
# contain spaces or special characters.
#
# Database Connections:
# FileMaker ODBC DSN Name: contact_management_fmp_dsn
# MySQL Connection: database=test:host=g4.dotcomsolutionsinc.net:port=3306
#
# Notes:
# This program handles large text
# fields from FileMaker as Text
# columns in the MySQL table.
# FileMaker container fields are
# written to MySQL LongBLOB columns.
#
# Usage: perl contact_management_fmpro_to_mysql_xfer_odbc1.pl
#
# Used By: run manually by the MySQL DBA
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 4-1-2003 dsimpson Initial Release
#
# This output file was created by FmPro Migrator version 1.23 on Thu Apr 3 12:19:39 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
#

use strict;
use DBI qw(:sql_types);

my $db_connect_string_mysql = 'database=test:host=g4.dotcomsolutionsinc.net:port=3306';
my $db_connect_string_fmpro = 'contact_management_fmp_dsn';
my $filemaker_database_name = 'contact_management';
my $mysql_tablename = 'contact_management';
my $schema_name = 'user1';
my $schema_password = 'user1password';
my $debug=0; # DBI tracing enable/disable
my $long_readlength = 100000; # maximum number of bytes for Text, LongBLOB type data read from FileMaker - increase this value as needed
my $record_count=0;
my @rowdata = ();

# ---------- fixup_container_data -----------
# This sub removes the HTTP 1.0 data which usually
# prefixes FileMaker Pro container field data.

sub fixup_container_data
{
my $rowdata_count = $_[0]; # get rowdata array element number passed into this sub
my $prefix_position=0;

my $temp_string=substr($rowdata[$rowdata_count],0,200);
if (substr($rowdata[$rowdata_count],0,4) eq "HTTP" )
{
# if 1st 4 characters of container field data contains the text "HTTP" then
# this prefix data needs to be removed before insertion into the MySQL table

##print substr($rowdata[$rowdata_count],0,10);# debug*****

$temp_string =~ /Content-length: /g;
# find the position within the data which contains the "Content-length: " text
$prefix_position = pos($temp_string);

# loop until end of numeric value defining content-length is reached
while (substr($temp_string,$prefix_position,1) =~ /\d/)
{
$prefix_position++;
}
$prefix_position += 4;

##print "**pos $prefix_position \n";# debug*****

if ($prefix_position < 200)
{
# truncate the container field prefix info - only if within the 1st 200 bytes
substr($rowdata[$rowdata_count],0,$prefix_position)="";
}

}
}
# ---------- fixup_container_data -----------

my $fmpro_dbh = DBI->connect ("dbi:ODBC:$db_connect_string_fmpro", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 0})
or die "Can't connect to the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n";
$fmpro_dbh->{LongReadLen} = $long_readlength;
$fmpro_dbh->{LongTruncOk} = 0;

if ($debug == 1)
{
# turn on DBI tracing
unlink 'dbitrace.log' if -e 'dbitrace.log';
DBI->trace(2, 'dbitrace.log');
}

my $mysql_dbh = DBI->connect ("dbi:mysql:$db_connect_string_mysql", "$schema_name", "$schema_password", {RaiseError => 1, PrintError => 1, AutoCommit => 0 })
or die "Can't connect to the MySQL $db_connect_string_mysql database: $DBI::errstr\n";
$mysql_dbh->{LongReadLen} = $long_readlength;
$mysql_dbh->{LongTruncOk} = 0;

$mysql_dbh->do("SET OPTION SQL_BIG_TABLES = 1");
my $mysql_sth = '';

# ----------- retrieve records from FileMaker
my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");

$fmpro_sth->execute();

while ( @rowdata = $fmpro_sth->fetchrow_array())
{

# ----------- insert data into MySQL
$mysql_sth = $mysql_dbh->prepare("insert into $mysql_tablename (last_name, first_name, company, title, street_1, city_1, state_province_1, postal_code_1, notes, phone_1, date_created, date_modified, identification_number, image_data, template_information_global, created_by, last_layout, email, phone_2, current_date_, similar_by, similars_key, similars_multikey, similars_count, similars_tab_label, similar_name_key, similar_company_key, street_2, city_2, state_province_2, postal_code_2, address_type_1, address_type_2, scratch, thumbnail, contact_id, most_recent_form_layout, letter_body_text, email_address_with_name, thumbnail_display) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

# remove start of container field data added by FileMaker Pro
&fixup_container_data(13);
&fixup_container_data(34);
&fixup_container_data(39);

$mysql_sth->bind_param( 1,$rowdata[0],SQL_VARCHAR); # last_name - varchar column
$mysql_sth->bind_param( 2,$rowdata[1],SQL_VARCHAR); # first_name - varchar column
$mysql_sth->bind_param( 3,$rowdata[2],SQL_VARCHAR); # company - varchar column
$mysql_sth->bind_param( 4,$rowdata[3],SQL_VARCHAR); # title - varchar column
$mysql_sth->bind_param( 5,$rowdata[4],SQL_VARCHAR); # street_1 - varchar column
$mysql_sth->bind_param( 6,$rowdata[5],SQL_VARCHAR); # city_1 - varchar column
$mysql_sth->bind_param( 7,$rowdata[6],SQL_VARCHAR); # state_province_1 - varchar column
$mysql_sth->bind_param( 8,$rowdata[7],SQL_VARCHAR); # postal_code_1 - varchar column
$mysql_sth->bind_param( 9,$rowdata[8],SQL_VARCHAR); # notes - varchar column
$mysql_sth->bind_param( 10,$rowdata[9],SQL_VARCHAR); # phone_1 - varchar column
$mysql_sth->bind_param( 11,$rowdata[10],SQL_DATE); # date_created - date column
$mysql_sth->bind_param( 12,$rowdata[11],SQL_DATE); # date_modified - date column
$mysql_sth->bind_param( 13,$rowdata[12],SQL_VARCHAR); # identification_number - varchar column
$mysql_sth->bind_param( 14,$rowdata[13],SQL_LONGVARBINARY); # image_data - blob column
$mysql_sth->bind_param( 15,$rowdata[14],SQL_VARCHAR); # template_information_global - varchar column
$mysql_sth->bind_param( 16,$rowdata[15],SQL_VARCHAR); # created_by - varchar column
$mysql_sth->bind_param( 17,$rowdata[16],SQL_DOUBLE); # last_layout - double column
$mysql_sth->bind_param( 18,$rowdata[17],SQL_VARCHAR); # email - varchar column
$mysql_sth->bind_param( 19,$rowdata[18],SQL_VARCHAR); # phone_2 - varchar column
$mysql_sth->bind_param( 20,$rowdata[19],SQL_DATE); # current_date_ - date column
$mysql_sth->bind_param( 21,$rowdata[20],SQL_VARCHAR); # similar_by - varchar column
$mysql_sth->bind_param( 22,$rowdata[21],SQL_VARCHAR); # similars_key - varchar column
$mysql_sth->bind_param( 23,$rowdata[22],SQL_VARCHAR); # similars_multikey - varchar column
$mysql_sth->bind_param( 24,$rowdata[23],SQL_DOUBLE); # similars_count - double column
$mysql_sth->bind_param( 25,$rowdata[24],SQL_VARCHAR); # similars_tab_label - varchar column
$mysql_sth->bind_param( 26,$rowdata[25],SQL_VARCHAR); # similar_name_key - varchar column
$mysql_sth->bind_param( 27,$rowdata[26],SQL_VARCHAR); # similar_company_key - varchar column
$mysql_sth->bind_param( 28,$rowdata[27],SQL_VARCHAR); # street_2 - varchar column
$mysql_sth->bind_param( 29,$rowdata[28],SQL_VARCHAR); # city_2 - varchar column
$mysql_sth->bind_param( 30,$rowdata[29],SQL_VARCHAR); # state_province_2 - varchar column
$mysql_sth->bind_param( 31,$rowdata[30],SQL_VARCHAR); # postal_code_2 - varchar column
$mysql_sth->bind_param( 32,$rowdata[31],SQL_VARCHAR); # address_type_1 - varchar column
$mysql_sth->bind_param( 33,$rowdata[32],SQL_VARCHAR); # address_type_2 - varchar column
$mysql_sth->bind_param( 34,$rowdata[33],SQL_VARCHAR); # scratch - varchar column
$mysql_sth->bind_param( 35,$rowdata[34],SQL_LONGVARBINARY); # thumbnail - blob column
$mysql_sth->bind_param( 36,$rowdata[35],SQL_DOUBLE); # contact_id - double column
$mysql_sth->bind_param( 37,$rowdata[36],SQL_DOUBLE); # most_recent_form_layout - double column
$mysql_sth->bind_param( 38,$rowdata[37],SQL_VARCHAR); # letter_body_text - varchar column
$mysql_sth->bind_param( 39,$rowdata[38],SQL_VARCHAR); # email_address_with_name - varchar column
$mysql_sth->bind_param( 40,$rowdata[39],SQL_LONGVARBINARY); # thumbnail_display - blob column

$mysql_sth->execute() or warn $mysql_sth->errstr(); # check for error

$record_count++;
print "Processed record# $record_count\n";
}

$fmpro_sth->finish();
$mysql_sth->finish();

# disconnect from FileMaker database
$fmpro_dbh->disconnect or warn "Can't disconnect from the FileMaker $db_connect_string_fmpro database: $DBI::errstr\n";

# disconnect from MySQL database
$mysql_dbh->disconnect or warn "Can't disconnect from the MySQL $db_connect_string_mysql database: $DBI::errstr\n";

print "***********************************************\n";
print "Completed inserting FileMaker records into MySQL database.\n";
print "***********************************************\n";
print "$record_count Records processed.\n";
print "***********************************************\n";

exit;



.

hline

 . .

.

 . .
 

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices
.
.   .
.
Home Products Services Downloads Order Support Contact