FmPro
Migrator Features and Benefits
FmPro
Migrator Demo Available for download...
Bookmark
This Page
#!/usr/bin/perl
# Script: contact_management_fmpro_to_oracle_xfer_odbc21.pl
# Features:
# This Perl program copies all
# of the records from the FileMaker
# database, then inserts the records
# into the Oracle database.
# EXAMPLE NOTES:
# This program provides an example of reading
# a binary file specified by a filepath for
# insertion into Oracle. This code will require
# customization for the FileMaker database being
# converted.
#
# Requirements:
# Perl DBI module
# Perl DBD::ODBC module [reads data from FileMaker
# and writes data into Oracle]
# Oracle client software and Oracle ODBC driver
# must be installed on the computer running this
# program. Note: The Oracle client software is
# not required if the DataDirect Connect for ODBC 4.1
# ODBC driver is being used.
# 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.
#
# Oracle Requirements:
# Oracle 8.1.6 (or higher) is required
# in order to use bind variables containing
# more than 4000 bytes with LOB columns.
# Oracle 9.2 is required in order to
# store LOBs in locally managed tablespaces
# and/or use the auto-allocate storage parameter.
#
# ODBC DSN Names:
# FileMaker DSN Name: contact_management_fmp_dsn
# Oracle DSN Name: contact_management_ora_dsn
#
# Notes:
# This program handles large text
# fields from FileMaker as CLOB
# columns in the Oracle table.
# FileMaker container fields are
# written to Oracle BLOB columns.
#
# Usage: perl contact_management_fmpro_to_oracle_xfer_odbc21.pl
#
# Used By: run manually by the Oracle DBA
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 2-27-2003 dsimpson Initial Release
# 4-2-2003 dsimpson Maint update - moved rowdata definition.
#
# This output file was created by FmPro Migrator version 1.23 on Thu
Apr 3 12:21:05 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
#
use strict;
use DBI qw(:sql_types);
my $db_connect_string_oracle = 'contact_management_ora_dsn';
my $db_connect_string_fmpro = 'contact_management_fmp_dsn';
my $filemaker_database_name = 'contact_management';
my $oracle_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 CLOB, BLOB
type data read from FileMaker - inscrease 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 $oracle_dbh = DBI->connect ("dbi:ODBC:$db_connect_string_oracle",
"$schema_name", "$schema_password", {RaiseError
=> 1, PrintError => 1, AutoCommit => 0 })
or die "Can't connect to the Oracle $db_connect_string_oracle database:
$DBI::errstr
";
$oracle_dbh->{LongReadLen} = $long_readlength;
$oracle_dbh->{LongTruncOk} = 0;
# ----------- retrieve records from FileMaker
my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");
$fmpro_sth->execute();
# set Oracle data format to match FileMaker
my $oracle_sth = $oracle_dbh->prepare("ALTER SESSION SET NLS_DATE_FORMAT
= 'YYYY-MM-DD HH24:MI:SS'");
$oracle_dbh->{RaiseError} = 1; # don't continue processing if error
is encountered here
$oracle_sth->execute();
while ( @rowdata = $fmpro_sth->fetchrow_array())
{
# ----------- insert data into Oracle
$oracle_sth = $oracle_dbh->prepare("insert into $oracle_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 (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$oracle_sth->bind_param( 1,$rowdata[0],SQL_VARCHAR);
# last_name - varchar2 column
$oracle_sth->bind_param( 2,$rowdata[1],SQL_VARCHAR); # first_name
- varchar2 column
$oracle_sth->bind_param( 3,$rowdata[2],SQL_VARCHAR); # company -
varchar2 column
$oracle_sth->bind_param( 4,$rowdata[3],SQL_VARCHAR); # title - varchar2
column
$oracle_sth->bind_param( 5,$rowdata[4],SQL_VARCHAR); # street_1 -
varchar2 column
$oracle_sth->bind_param( 6,$rowdata[5],SQL_VARCHAR); # city_1 - varchar2
column
$oracle_sth->bind_param( 7,$rowdata[6],SQL_VARCHAR); # state_province_1
- varchar2 column
$oracle_sth->bind_param( 8,$rowdata[7],SQL_VARCHAR); # postal_code_1
- varchar2 column
$oracle_sth->bind_param( 9,$rowdata[8],SQL_VARCHAR); # notes - varchar2
column
$oracle_sth->bind_param( 10,$rowdata[9],SQL_VARCHAR); # phone_1 -
varchar2 column
$oracle_sth->bind_param( 11,$rowdata[10],SQL_DATE); # date_created
- date column
$oracle_sth->bind_param( 12,$rowdata[11],SQL_DATE); # date_modified
- date column
$oracle_sth->bind_param( 13,$rowdata[12],SQL_VARCHAR); # identification_number
- varchar2 column
$oracle_sth->bind_param( 14,$rowdata[13],SQL_LONGVARBINARY); # image_data
- blob column
$oracle_sth->bind_param( 15,$rowdata[14],SQL_VARCHAR); # template_information_global
- varchar2 column
$oracle_sth->bind_param( 16,$rowdata[15],SQL_VARCHAR); # created_by
- varchar2 column
$oracle_sth->bind_param( 17,$rowdata[16],SQL_DOUBLE); # last_layout
- number column
$oracle_sth->bind_param( 18,$rowdata[17],SQL_VARCHAR); # email -
varchar2 column
$oracle_sth->bind_param( 19,$rowdata[18],SQL_VARCHAR); # phone_2
- varchar2 column
$oracle_sth->bind_param( 20,$rowdata[19],SQL_DATE); # current_date
- date column
$oracle_sth->bind_param( 21,$rowdata[20],SQL_VARCHAR); # similar_by
- varchar2 column
$oracle_sth->bind_param( 22,$rowdata[21],SQL_VARCHAR); # similars_key
- varchar2 column
$oracle_sth->bind_param( 23,$rowdata[22],SQL_VARCHAR); # similars_multikey
- varchar2 column
$oracle_sth->bind_param( 24,$rowdata[23],SQL_DOUBLE); # similars_count
- number column
$oracle_sth->bind_param( 25,$rowdata[24],SQL_VARCHAR); # similars_tab_label
- varchar2 column
$oracle_sth->bind_param( 26,$rowdata[25],SQL_VARCHAR); # similar_name_key
- varchar2 column
$oracle_sth->bind_param( 27,$rowdata[26],SQL_VARCHAR); # similar_company_key
- varchar2 column
$oracle_sth->bind_param( 28,$rowdata[27],SQL_VARCHAR); # street_2
- varchar2 column
$oracle_sth->bind_param( 29,$rowdata[28],SQL_VARCHAR); # city_2 -
varchar2 column
$oracle_sth->bind_param( 30,$rowdata[29],SQL_VARCHAR); # state_province_2
- varchar2 column
$oracle_sth->bind_param( 31,$rowdata[30],SQL_VARCHAR); # postal_code_2
- varchar2 column
$oracle_sth->bind_param( 32,$rowdata[31],SQL_VARCHAR); # address_type_1
- varchar2 column
$oracle_sth->bind_param( 33,$rowdata[32],SQL_VARCHAR); # address_type_2
- varchar2 column
$oracle_sth->bind_param( 34,$rowdata[33],SQL_VARCHAR); # scratch
- varchar2 column
$oracle_sth->bind_param( 35,$rowdata[34],SQL_LONGVARBINARY); # thumbnail
- blob column
$oracle_sth->bind_param( 36,$rowdata[35],SQL_DOUBLE); # contact_id
- number column
$oracle_sth->bind_param( 37,$rowdata[36],SQL_DOUBLE); # most_recent_form_layout
- number column
$oracle_sth->bind_param( 38,$rowdata[37],SQL_VARCHAR); # letter_body_text
- varchar2 column
$oracle_sth->bind_param( 39,$rowdata[38],SQL_VARCHAR); # email_address_with_name
- varchar2 column
$oracle_sth->bind_param( 40,$rowdata[39],SQL_LONGVARBINARY); # thumbnail_display
- blob 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
}
$oracle_sth->execute() or warn $oracle_sth->errstr(); # check
for error
$oracle_dbh->commit();
$record_count++;
print "Processed record# $record_count\n";
}
$fmpro_sth->finish();
$oracle_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 Oracle database
$oracle_dbh->disconnect or warn "Can't disconnect from the Oracle
$db_connect_string_oracle database: $DBI::errstr\n";
print "***********************************************\n";
print "Completed inserting FileMaker records into Oracle database.\n";
print "***********************************************\n";
print "$record_count Records processed.\n";
print "***********************************************\n";
exit;
