.com Solutions Inc. - FmPro Migrator

FmPro Migrator - MySQL Files - Title Graphic

#!/usr/bin/perl # Script: contact_management_fmpro_max_fieldsize1.pl # Features: This Perl program reads all of the # data from the FileMaker database # then produces a report showing # the maximum amount of data stored # within each field. This info is # used to provide quidance when sizing # the column widths in the migration # destination database. # # Requirements: # Perl DBI module # Perl DBD::ODBC module reads data from FileMaker # 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 # # Usage: perl contact_management_fmpro_max_fieldsize1.pl # # Used By: run manually by the FileMaker developer # Copyright 2003 by .com Solutions Inc. # # ---------------------- Revision History --------------- # Date By Changes # 5-24-2003 dsimpson Initial Release # # This output file was created by FmPro Migrator version 1.46 on Sat Jul 12 10:14:45 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net #

use strict; use DBI qw(:sql_types);

my $outputfile = 'contact_management_fmpro_max_fieldsize_report.txt'; my $db_connect_string_fmpro = 'contact_management_fmp_dsn'; my $filemaker_database_name = 'contact_management'; my $debug=0; # DBI tracing enable/disable my $long_readlength = 100000; # maximum number of bytes for large text and container field data read from FileMaker - increase this value as needed my $record_count=0; my @rowdata = (); my $number_of_fields=40; my @list_of_fmpro_fields = qw(asset_id model item category cost date_purchased date_created date_modified total_cost serial_number location assigned_to date_placed purchased_from depreciation_life book_value remaining_life total_book_value depreciation total_depreciation picture date_assigned template_information_global created_by information date_check_in date_check_out date_due overdue_ check_out_days hilitelibrary hilitesortedby hilitecategory hiliteassignedto hiliteitem hilitemodel hiliteserialnumber hilitelocation assigned_display sample_calc); my @list_of_fmpro_field_types = qw(number text text text number date/time date/time date/time number text text text date/time text number number number number number number container date/time text text text date/time date/time date/time text number container text container container container container container container number number); my @list_of_destination_field_types = qw(DOUBLE VARCHAR(255) VARCHAR(255) VARCHAR(255) DOUBLE DATE DATE DATE DOUBLE VARCHAR(255) VARCHAR(255) VARCHAR(255) DATE VARCHAR(255) DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE LONGBLOB DATE VARCHAR(255) VARCHAR(255) VARCHAR(255) DATE DATE DATE VARCHAR(255) DOUBLE LONGBLOB VARCHAR(255) LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB DOUBLE DOUBLE); my @list_of_fmpro_field_lengths = qw(0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0); my $spaces_string1 = ''; my $spaces_string2 = ''; my $spaces_string3 = ''; my $spaces_string4 = ''; # ----------------------- create_output_spaces -------------------------- # This sub returns a string which contains the number of space characters specified # as an input parameter # inut parameters are: column width, actual data string

sub create_output_spaces($$) {

my ($column_width,$input_data_string)=@_; my $finished_string=''; my $number_of_spaces=0; my $input_data_string_length=0;

$input_data_string_length = length($input_data_string); $number_of_spaces = $column_width - $input_data_string_length;

if ($number_of_spaces > 0) { # cycle thru number of spaces to create for (my $count=0;$count < $number_of_spaces+1; $count++) { $finished_string = $finished_string . ' '; } } return $finished_string; } # ----------------------- create_output_spaces -----------------------

# ---------- get_column_size ----------- # This sub checks the size of data in the column # which has been passed in, and notes whether # it exceeds the highest value yet retrieved # from the database.

sub get_column_size { my $rowdata_count = $_[0]; # get rowdata array element number passed into this sub my $prefix_position=0; my $temp_length = length($rowdata[$rowdata_count]); if ($temp_length > $list_of_fmpro_field_lengths[$rowdata_count]) { # new length is longer than any previous length found - update summary info $list_of_fmpro_field_lengths[$rowdata_count] = $temp_length; } } # ---------- get_column_size -----------

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'); } # ----------- retrieve records from FileMaker my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");

$fmpro_sth->execute();

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

# check amount of data in each column &get_column_size(0); &get_column_size(1); &get_column_size(2); &get_column_size(3); &get_column_size(4); &get_column_size(5); &get_column_size(6); &get_column_size(7); &get_column_size(8); &get_column_size(9); &get_column_size(10); &get_column_size(11); &get_column_size(12); &get_column_size(13); &get_column_size(14); &get_column_size(15); &get_column_size(16); &get_column_size(17); &get_column_size(18); &get_column_size(19); &get_column_size(20); &get_column_size(21); &get_column_size(22); &get_column_size(23); &get_column_size(24); &get_column_size(25); &get_column_size(26); &get_column_size(27); &get_column_size(28); &get_column_size(29); &get_column_size(30); &get_column_size(31); &get_column_size(32); &get_column_size(33); &get_column_size(34); &get_column_size(35); &get_column_size(36); &get_column_size(37); &get_column_size(38); &get_column_size(39);

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

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

# open output file, overwrite old file open (FILE1,">$outputfile") || die ("Could not open output file $outputfile for writing.");

print FILE1 " contact_management.fp5 Field Length Summary Report\n"; print FILE1 "\n"; print FILE1 "Copyright 2003 by .com Solutions Inc.\n"; print FILE1 "\n"; print FILE1 "----------------------- Field Length Summary Report --------------------------\n"; print FILE1 "Renamed Field Name Size FileMaker Type Destination Type\n\n"; print "***********************************************\n"; print "Completed analyzing $record_count FileMaker records.\n"; print "***********************************************\n"; print " Field Length Summary Report\n"; print "***********************************************\n"; print "Number of Fields: $number_of_fields\n"; print "\n"; for (my $count=0;$count < $number_of_fields; $count++) { # columns 1 and 2 $spaces_string1 = create_output_spaces(63,$list_of_fmpro_fields[$count]); # columns 2 and 3 $spaces_string2 = create_output_spaces(10,$list_of_fmpro_field_lengths[$count]);

# columns 3 and 4 $spaces_string3 = create_output_spaces(20,$list_of_fmpro_field_types[$count]);

# columns 4 and 5 $spaces_string4 = create_output_spaces(20,$list_of_destination_field_types[$count]);

print "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]\n"; print FILE1 "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]$spaces_string2$list_of_fmpro_field_types[$count]$spaces_string3$list_of_destination_field_types[$count]$spaces_string4\n";

} # close the output file close (FILE1);

exit;

			FmPro Migrator Features and Benefits FmPro Migrator Demo Available for download... Bookmark This Page #!/usr/bin/perl # Script: contact_management_fmpro_max_fieldsize1.pl # Features: This Perl program reads all of the # data from the FileMaker database # then produces a report showing # the maximum amount of data stored # within each field. This info is # used to provide quidance when sizing # the column widths in the migration # destination database. # # Requirements: # Perl DBI module # Perl DBD::ODBC module reads data from FileMaker # 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 # # Usage: perl contact_management_fmpro_max_fieldsize1.pl # # Used By: run manually by the FileMaker developer # Copyright 2003 by .com Solutions Inc. # # ---------------------- Revision History --------------- # Date By Changes # 5-24-2003 dsimpson Initial Release # # This output file was created by FmPro Migrator version 1.46 on Sat Jul 12 10:14:45 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net # `use strict; use DBI qw(:sql_types);` my $outputfile = 'contact_management_fmpro_max_fieldsize_report.txt'; my $db_connect_string_fmpro = 'contact_management_fmp_dsn'; my $filemaker_database_name = 'contact_management'; my $debug=0; # DBI tracing enable/disable my $long_readlength = 100000; # maximum number of bytes for large text and container field data read from FileMaker - increase this value as needed my $record_count=0; my @rowdata = (); my $number_of_fields=40; my @list_of_fmpro_fields = qw(asset_id model item category cost date_purchased date_created date_modified total_cost serial_number location assigned_to date_placed purchased_from depreciation_life book_value remaining_life total_book_value depreciation total_depreciation picture date_assigned template_information_global created_by information date_check_in date_check_out date_due overdue_ check_out_days hilitelibrary hilitesortedby hilitecategory hiliteassignedto hiliteitem hilitemodel hiliteserialnumber hilitelocation assigned_display sample_calc); my @list_of_fmpro_field_types = qw(number text text text number date/time date/time date/time number text text text date/time text number number number number number number container date/time text text text date/time date/time date/time text number container text container container container container container container number number); my @list_of_destination_field_types = qw(DOUBLE VARCHAR(255) VARCHAR(255) VARCHAR(255) DOUBLE DATE DATE DATE DOUBLE VARCHAR(255) VARCHAR(255) VARCHAR(255) DATE VARCHAR(255) DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE DOUBLE LONGBLOB DATE VARCHAR(255) VARCHAR(255) VARCHAR(255) DATE DATE DATE VARCHAR(255) DOUBLE LONGBLOB VARCHAR(255) LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB LONGBLOB DOUBLE DOUBLE); my @list_of_fmpro_field_lengths = qw(0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0); my $spaces_string1 = ''; my $spaces_string2 = ''; my $spaces_string3 = ''; my $spaces_string4 = ''; # ----------------------- create_output_spaces -------------------------- # This sub returns a string which contains the number of space characters specified # as an input parameter # inut parameters are: column width, actual data string `sub create_output_spaces($$) {` `my ($column_width,$input_data_string)=@_; my $finished_string=''; my $number_of_spaces=0; my $input_data_string_length=0;` `$input_data_string_length = length($input_data_string); $number_of_spaces = $column_width - $input_data_string_length;` `if ($number_of_spaces > 0) { # cycle thru number of spaces to create for (my $count=0;$count < $number_of_spaces+1; $count++) { $finished_string = $finished_string . ' '; } } return $finished_string; } # ----------------------- create_output_spaces -----------------------` `# ---------- get_column_size ----------- # This sub checks the size of data in the column # which has been passed in, and notes whether # it exceeds the highest value yet retrieved # from the database.` `sub get_column_size { my $rowdata_count = $_[0]; # get rowdata array element number passed into this sub my $prefix_position=0; my $temp_length = length($rowdata[$rowdata_count]); if ($temp_length > $list_of_fmpro_field_lengths[$rowdata_count]) { # new length is longer than any previous length found - update summary info $list_of_fmpro_field_lengths[$rowdata_count] = $temp_length; } } # ---------- get_column_size -----------` `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'); } # ----------- retrieve records from FileMaker my $fmpro_sth = $fmpro_dbh->prepare("select * from $filemaker_database_name");` `$fmpro_sth->execute();` `while ( @rowdata = $fmpro_sth->fetchrow_array()) {` # check amount of data in each column &get_column_size(0); &get_column_size(1); &get_column_size(2); &get_column_size(3); &get_column_size(4); &get_column_size(5); &get_column_size(6); &get_column_size(7); &get_column_size(8); &get_column_size(9); &get_column_size(10); &get_column_size(11); &get_column_size(12); &get_column_size(13); &get_column_size(14); &get_column_size(15); &get_column_size(16); &get_column_size(17); &get_column_size(18); &get_column_size(19); &get_column_size(20); &get_column_size(21); &get_column_size(22); &get_column_size(23); &get_column_size(24); &get_column_size(25); &get_column_size(26); &get_column_size(27); &get_column_size(28); &get_column_size(29); &get_column_size(30); &get_column_size(31); &get_column_size(32); &get_column_size(33); &get_column_size(34); &get_column_size(35); &get_column_size(36); &get_column_size(37); &get_column_size(38); &get_column_size(39); `$record_count++; print "Processed record# $record_count\n"; }` `$fmpro_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";` `# open output file, overwrite old file open (FILE1,">$outputfile") \|\| die ("Could not open output file $outputfile for writing.");` print FILE1 " contact_management.fp5 Field Length Summary Report\n"; print FILE1 "\n"; print FILE1 "Copyright 2003 by .com Solutions Inc.\n"; print FILE1 "\n"; print FILE1 "----------------------- Field Length Summary Report --------------------------\n"; print FILE1 "Renamed Field Name Size FileMaker Type Destination Type\n\n"; print "*********************************************\n"; print "Completed analyzing $record_count FileMaker records.\n"; print "*******************************************\n"; print " Field Length Summary Report\n"; print "*********************************************\n"; print "Number of Fields: $number_of_fields\n"; print "\n"; for (my $count=0;$count < $number_of_fields; $count++) { # columns 1 and 2 $spaces_string1 = create_output_spaces(63,$list_of_fmpro_fields[$count]); # columns 2 and 3 $spaces_string2 = create_output_spaces(10,$list_of_fmpro_field_lengths[$count]); `# columns 3 and 4 $spaces_string3 = create_output_spaces(20,$list_of_fmpro_field_types[$count]);` `# columns 4 and 5 $spaces_string4 = create_output_spaces(20,$list_of_destination_field_types[$count]);` `print "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]\n"; print FILE1 "$list_of_fmpro_fields[$count]$spaces_string1$list_of_fmpro_field_lengths[$count]$spaces_string2$list_of_fmpro_field_types[$count]$spaces_string3$list_of_destination_field_types[$count]$spaces_string4\n";` `} # close the output file close (FILE1);` `exit;`

			Home \| Products \| Services \| Downloads \| Order \| Support \| Contact Legal Notices