.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_odbc21.pl
# Features: This Perl program copies all
# of the records from the FileMaker
# database, then inserts the records
# into the MySQL database.
#
# EXAMPLE NOTES:
# This program provides an example of reading
# a binary file specified by a filepath for
# insertion into MySQL. This code will require
# customization for the FileMaker database being
# converted
.
# 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_odbc21.pl
#
# Used By: run manually by the MySQL DBA
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 3-30-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 = ();

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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

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


{
# Note: $rowdata[1] and $rowdata[2] are just example array variables - change these variables as needed
local $/ = undef;
open (INPUTFILE1, "$rowdata[1]") || warn ("Could not open input file $rowdata[1], does it exist?");
binmode(INPUTFILE1);
$rowdata[2] = <INPUTFILE1>;
close (INPUTFILE1);
my $image_size = length($rowdata[2]);
print "$rowdata[1] image size $image_size\n"; # print the image size
#print substr($rowdata[2],0,20); # print the first few bytes of data

}


$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