#!/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 # # This output file was created by FmPro Migrator version 1.01 on Fri Feb 28 09:41:04 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 = 'user1pwd'; 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 $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; # ----------- retreive 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 ( my @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 $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;