Augustina's Technological Blog

Technology, Perl, Linux, and a Woman's perspective on the FOSS community

FileMaker and ODBC

leave a comment »

One of the projects I’m working on right now is replicating a FileMaker Pro 6.0 database (hosted on FileMaker Server 5.5) with a MySQL database. MySQL is more robust and more efficient as well it handles connectivity a lot better. Even through ODBC Filemaker is still a pig.

The best information and code samples I’ve found for interacting with a Filemaker database (via ODBC) with Perl is fmpro migrator. In particular there are code samples that have been extremely helpful for me in getting my databases talking to each other. One such example is here.

For older versions of FileMaker, like what I’m using, you’ll want to set up the DSN on Windows. FileMaker ships with its own ODBC driver and unless you want to pay more money for ODBC connectivity (there are some nice options out there), set theirs up on Windows.

To set up your DSN I recommend this walk through.

If you want to know specifically what SQL statements work with FileMaker’s ODBC, this document is extremely helpful (it’s a pdf):
FileMaker ODBC and JDBC Developers Guide

You’ll need Perl’s DBI and DBD::ODBC modules in order to connect.

Here are samples of simple programs I used to connect:

odbctest.pl

#!/usr/bin/perl

use DBI;

my $db_connect_string_fm = 'fmp_dsn';

my $fm_db_name = 'ODBCTest';

my @rowdata = ();

my $fm_dbh = DBI->connect("dbi:ODBC:$db_connect_string_fm", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1})

or die "Can't connect to the Filemaker $db_connect_string_fm database: $DBI::errstr\n";

my $fm_sth = $fm_dbh->prepare("select ID, Name from $fm_db_name where ID>42600");

$fm_sth->execute();

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

my ($id, $name) = @rowdata;

print "id:$id name:$name\n";

}

$fm_sth->finish();

$fm_dbh->disconnect or warn "Can't disconnect from Filemaker $db_connect_string_fm database: $DBI::errstr\n";

here’s an example of updating (be sure you don’t have AutoCommit set to 0):

odbcdatetest.pl

#!/usr/bin/perl

#test mod date comparision

use DBI qw(:sql_types);

my $today = "\'3/27/2007\'";

my $db_connect_string_fm = 'fmp_dsn';

my $fm_db_name = 'ODBCTest';

my @rowdata = ();

my $fm_dbh = DBI->connect("dbi:ODBC:$db_connect_string_fm", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1})

or die "Can't connect to the Filemaker $db_connect_string_fm database: $DBI::errstr\n"

unless $dbh;

my $fm_sth = $fm_dbh->do("update $fm_db_name set Last_Export=$today");

my $fm_sth2 = $fm_dbh->prepare("select ID, Last_Export from $fm_db_name where ID>42600");

$fm_sth2->execute();

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

my ($id, $date) = @rowdata;

print "id:$id last_export:$date\n";

}

$fm_sth2->finish();

$fm_dbh->disconnect or warn "Can't disconnect from Filemaker $db_connect_string_fm database: $DBI::errstr\n";

Regarding field types, FileMaker itself will let you insert values into fields that don’t correspond with a fields defined type. Make sure your fields are defined correctly. If you try to use a WHERE statement on a text field and you pass in a number, the ODBC connection will complain (even if the data in the database doesn’t follow this rule). The same is true when using a SELECT statement – if the data in FileMaker is defined as a number but the actual data is text, ODBC will treat it as a number and you won’t get the results you expect.

When passing text values through SQL, the values must be surrounded by single quotes, for example, ‘text’. To manage this in perl, use an escape character, \’text\’. Dates and times need to be surrounded by curly braces: {1/1/2007}.

Advertisements

Written by missaugustina

March 27, 2007 at 6:06 pm

Posted in Perl, Programming

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: