Joe’s Amazing Technicolor Weblog

I’m sure I’m not the only person with this situation:

1. FileMaker database sitting behind a firewall (though similar issues would pertain for other internal databases / services)
2. Website hosted elsewhere (i.e. other side of firewall)
3. Need to get data from #2 to #1 reliably and securely

Up until today, I’ve only had one instance of #2 in this situation. I dealt with it by storing data collected on the website (which happened to be written in Rails) in a database on the web server, and then running a periodic PHP script on the FileMaker server that connects to the Rails app via phpactiveresource, pulls in pending data, and inserts it into FileMaker via its PHP api.

That instance was such a roaring success that the requests have been pouring in for more of the same. Some of the new requests will be handled by a site running PHP, so I’ve got a bit of rewiring to do — I can’t see any sense in the getting the data from the PHP app into something the Active Resource client can talk to.

Stepping back and looking at the bigger picture, issues here include:

• the connection from the website to the FileMaker server could be down, so data collected by the website needs to be stored until it can be confirmed to have made it to FileMaker.
• it would be nice for this to happen in a timely fashion
• multiple technologies on the web side (PHP & ruby) are going to be collecting data to be submitted to FileMaker, so it’d be nice if the transfer machinery can be agnostic and just accept JSON or XML or something.

Sounds like a problem for a queue system, huh? So my current plan is to run a beanstalkd instance on the webserver, deposit JSON-endocded data into it from the web sites, and run workers that write to FileMaker using the Ruby FM API. I have no experience with beanstalkd, but a bit of googling suggests that it’s at a nice point in simplicity to configure & run, maturity, light weight, and easy access from PHP & Ruby.

A further benefit of working in beanstalkd is that, based on a quick perusal of the recommended Rails integration, it should be really easy to break Observers out to async code, thus making my rails apps snappier.

Any advice to the contrary is of course welcome. I’ll try to remember to update y’all on how this turns out.

or, Why To Use Dedicated Layouts When Connecting To FileMaker Via PHP

I’d read that it’s a good practice to always use a dedicated layout for any PHP scripts you have that are talking to a FileMaker database. While I’d seen reasons of efficiency and reliability, today I learned another reason that’s true: it can eliminate otherwise hard-to-debug problems.

At first when working on my current FileMaker <-> PHP project, I was attempting to reuse an existing layout that had all the info I needed. While my permissions seemed to be fine for the data file and layout I was attempting to access, actually running the script kept resulting in “Error 100: File is missing” coming back at me as soon as I added any criteria to my search. FileMaker doesn’t bother putting anything useful in its server logs, either, so it wouldn’t have been much fun picking through the layout & figuring what linkage(s) were to blame.

However, by simply creating a dedicated layout, everything started working as planned. A practice I’ll be following in the future.

We last looked at this a few months ago, but have been revisiting it to come up with something a little more robust. Notes:

• there’s a good overview of using PHP with FM at the sixfriedrice blog.
• the API for FileMaker’s PHP interface is available at http://YOURSERVER.URL:16000/docs/PHP%20API%20Documentation/index.html
• I’d missed this last time around, but accounts & permissions are a little funky. fmphp needs to be added to the Extended Privileges of the database you’re trying to get to, and must have the same privilege set as the account you’re connecting as.
• The solution we settled on is a CLI PHP script running hourly, checking for mail to send. Launchd would be the logical way to do the scheduling, but always drives me nuts. Fortunately the server in question has cron set up (so much simpler!)

The code we’re more or less using:

#!/usr/bin/php
<?php
set_include_path(get_include_path() . PATH_SEPARATOR .
  '/Library/FileMaker Server/Web Publishing/publishing-engine/php/lib/php/');
require_once('FileMaker.php');

echo "PHP email-sending-script, running at " .
  date('m/d/Y H:i') . "\n";
$layout = 'Outgoing_Email';
$fm = new FileMaker('Layout Name');
$fm->setProperty('username', 'your filemaker username');
$fm->setProperty('password', 'your filemaker password');

$findCmd =& $fm->newFindCommand($layout);
$findCmd->addFindCriterion('Sent_Flag', '< 1');
$result = $findCmd->execute();
if (FileMaker::isError($result)) {
  if ($result->code == 401) {
    exit("No emails to send.\n");
  } else {
    exit("trouble: " . $result->message . "(" . $result->code . ")");
  }
}

$records = $result->getRecords();
foreach($records as $record) {
  echo "To: " . $record->getField('Recipient') . "\n";
  echo "Subject: " . $record->getField('Subject') . "\n";
  $headers = array(
    "From: filemaker@example.com",
    "MIME-Version: 1.0",
    "Content-type: text/html"
    );

  /*
   FM helpfully encodes < and >...
   */
  $body = preg_replace('/&lt;/', '< ', $record->getField('Body'));
  $body = preg_replace('/&gt;/', '>', $body);
  $rc = mail($record->getField('Recipient'),
       $record->getField('Subject'),
       $body,
       implode("\r\n", $headers)
      );
  if ($rc) {
    $update = $fm->newEditCommand($layout, $record->getRecordId());
    $update->setField('Sent_Date', date('m/d/Y'));
    $update->setField('Sent_Time', date('H:i'));
    $update->setField('Sent_Flag', '1');
    $result = $update->execute();
    if (FileMaker::isError($result)) {
      exit("trouble updating the database after sending email: " .
        $result->message . "(" . $result->code . ")");
    }
    echo "Mailed!\n";
  } else {
    exit("Mail didn't work.\n");
  }
}

Some context — my new gig features a big ‘ol FileMaker installation, which has a number of automated maintenance routines. Some of those routines send emails, through a convoluted process involving FileMaker calling a GUI MUA (e.g. Mail.app). There are a number of practical problems with this, such as the requirement of another computer running, always logged into an account capable of sending the emails.

We’d done a bit of research about strictly server-side alternatives, and found surprisingly little. The best resource was Graham Sprague’s page about sending emails via FileMaker’s XSLT Web Publishing tool. We gave that approach a try, but didn’t get any results, or anything useful from FileMaker’s logs to explain why things weren’t working. I’m not sure what FileMaker version Graham’s example was written for, perhaps something’s changed with version 9?

Rather than dive into FileMaker’s proprietary XSLT system to debug things, it occurred to me that this might be a job for FileMaker’s PHP API. Sure enough, after about 15 minutes of consulting the API Doc, we were sending emails based on the contents of a FileMaker record.

We’re still working on ironing out the details, but here’s the rough proof of concept PHP file. It works with the example email database from Graham’s XSLT sample, with the php permission added to the database. Plenty of missing features such as cc & bcc fields, actually checking for the ‘send’ flag, checking for errors, any kind of authorization or authentication, etc. In other words, you probably don’t want to be running this on a publicly accessible webserver, but at least it presents the basic idea in a simple form.

<?php
require_once('FileMaker.php');
$fm = new FileMaker();
$fm->setProperty('username', 'send_email');
$fm->setProperty('password', 'whatever_the_password_is');
$fm->setProperty('database', 'Email');

$findCmd =& $fm->newFindAllCommand('Utility_Email');
$result = $findCmd->execute();
$records = $result->getRecords();
foreach($records as $record) {
  $headers = array("From: " . $record->getField('from'));

  mail($record->getField('to'),
       $record->getField('subject'),
       $record->getField('message'),
       implode("\r\n", $headers)
      );
}
echo "Emails sent";