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

. 		.

FmPro Migrator - Oracle 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_oracle_xfer_odbc1.pl
# Features: This Perl program copies all
# of the records from the FileMaker
# database, then inserts the records
# into the Oracle database.
#
# 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.
# 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_odbc1.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-1-2003 dsimpson Added removal of container field
# HTTP prefix data to support
# conversion of JPEG data for Macintosh
# and Windows hosted databases.
# 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 - 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 Oracle table

$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;

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

&fixup_container_data(13);
&fixup_container_data(34);
&fixup_container_data(39);

$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

$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;



.

hline

 . .

.

 . .
 

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

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