Building Web Services with FileMaker Pro

October 17, 2001

Bill Humphries

Introduction

FileMaker Pro 5.5 added the ability to return query results in XML to the existing web interface. Acting as a web server, however, FMPro does not support transforming the returned XML on the server. FileMaker's documentation describes using Internet Explorer's built-in DOM and XSL tools to transform the returned XML for display, which limits the usefulness of the XML data feature since browsers vary in their implementations of XSLT and DOM.

In this article, I will show how I used two PHP implementations of XML tools, RAX and XML-RPC, to create a web interface to a FileMaker Pro database. I'm using these techniques at Apple, where we have many FileMaker databases we need to put on our intranets. Since FileMaker runs on OS X, for a small workgroup solution you can run all the pieces on a single machine combining Apache with PHP and FileMaker.

At Apple, we use FileMaker Pro in small databases maintained by groups throughout the company. Some users need to browse, add, or edit records; and others need to connect to the databases to do analytic work.

Getting XML out of FMPro

Setting Up

Getting XML data out of FileMaker requires the database be set up for web access. In this article I'm using a simple phone list example with three fields:

1. Name, a text field
2. Phone Number, a text field
3. Location, an enumerated type with possible values: Austin, Cupertino, Sacramento

The database is in phonelist.fp5.

FileMaker and the database have to be set up to serve over the Web. Go to the FileMaker application preferences and select the Plug-Ins tab.

Enable the Web Companion by selecting the line with the option, then checking the box next to it. Then click "Configure...". There are several options here, the only one to worry about in this example is the port number. If you are running FileMaker and OS X's built-in Apache web server on the same computer, you will need to run FileMaker's web server on another port. By convention, FileMaker's web server runs on port 471 [ see FM Documentation ].

After you enter the port number, and click okay, you will be asked to enter your administrator password under OS X to make system changes.

In my experience with setting up the Web Companion on two computers running OS X, you may receive an error message about having insufficient memory to run the Web Server. In that case, quit FileMaker and restart.

Now set up the phonelist database for the Web. Open the file, and select "Sharing..." from the file menu.

Select the Web Companion option and check its box. You can disregard "Set Up Views..."

The database and FileMaker Pro are now set up to view queries using HTTP.

Viewing XML

FileMaker supports two kinds of XML for viewing results. The dso_xml is suited to fetching records for display using record-oriented XML APIs such as RAX. The fmp_xml returns metadata describing the database's layout as well as database records.

To view the record-oriented XML, use the following URL (split here for clarity, enter all as one line):

http://localhost:471/FMPro?-db=phonelist.fp5&

-format=-dso_xml&-find

Reviewing the parameters of the request:

• -db: The database.
• -format: The syntax in which to display results

The '-' character before parameter names and values is significant, not a typo.

FileMaker treats each of the request types as a separate keyword instead of defining a request parameter with the types of request as possible values.

Requesting this URL returns:

<?xml version="1.0"?>

<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">

  <ERRORCODE>0</ERRORCODE>

  <DATABASE>phonelist.fp5</DATABASE>

  <LAYOUT>

  </LAYOUT>

  <ROW MODID="2" RECORDID="1">

    <Name>Jody Tester</Name>

    <Phone>512 111-2222</Phone>

    <Location>Austin</Location>

  </ROW>

  <ROW MODID="0" RECORDID="2">

    <Name>Mary Sysadmin</Name>

    <Phone>512 222-3333</Phone>

    <Location>Austin</Location>

  </ROW>

  <ROW MODID="0" RECORDID="3">

    <Name>Joe Developer</Name>

    <Phone>408 444-5555</Phone>

    <Location>Cupertino</Location>

  </ROW>

  <ROW MODID="0" RECORDID="4">

    <Name>Bob Analyst</Name>

    <Phone>916 666-7777</Phone>

    <Location>Sacramento</Location>

  </ROW>

  <ROW MODID="0" RECORDID="6">

    <Name>Karen Project</Name>

    <Phone>508 666-7777</Phone>

    <Location>Cupertino</Location>

  </ROW>

</FMPDSORESULT>

Note that each FileMaker XML result set contains an ERRORCODE element. If all is well, then the text child of that element is '0'.

To restrict the results by Location, for example, add the field name and the value, to screen for, to the request:

http://localhost:471/FMPro?-db=phonelist.fp5&

-format=-dso_xml&Location=Austin-find

FileMaker expects the request type as the last parameter in GET requests. If it is not at the end of the URL, FileMaker Pro will return an HTML/JavaScript error.

This URL returns:

<?xml version="1.0"?>

<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">

  <ERRORCODE>0</ERRORCODE>

  <DATABASE>phonelist.fp5</DATABASE>

  <LAYOUT>

  </LAYOUT>

  <ROW MODID="2" RECORDID="1">

    <Name>Jody Tester</Name>

    <Phone>512 111-2222</Phone>

    <Location>Austin</Location>

  </ROW>

  <ROW MODID="0" RECORDID="2">

    <Name>Mary Sysadmin</Name>

    <Phone>512 222-3333</Phone>

    <Location>Austin</Location>

  </ROW>

</FMPDSORESULT>

You can find more documentation on the XML-HTTP interface to FileMaker Pro at FileMaker's web site

Now that we're receiving XML from FileMaker, we can format it for display in a browser.

Using PRAX to Display Query Results

PRAX is a PHP implementation of RAX, a record-oriented API for processing XML. For the row-record style results from the dso_xml syntax, it provides a simple way to format the results.

PRAX has a straightforward API: to import XML, create a RAX object and read a file or URL.

include("prax.inc");

$rax = new RAX();

$rax->openfile($url);

Then specify an element to use as record delimiter, and parse the document.

$rax->record_delim = "ROW";

$rax->parse();

Then to write out the records as an HTML table:

if( $rec = $rax->readRecord())

  {

  $row = 1;

  $names   = $rec->getFieldNames();

  print "<table cellpadding=\"5\" 

    cellspacing=\"0\">\n\t<tr>\n";

  while ($name = array_shift($names))

  {

    print "<th class=\"header\">".$name."</th>\n";

  }

  print "\t</tr>\n\t";

  print "<tr>\n";

  while ( $rec ) 

  {

  

    $class = oddOrEven($row);

    

    print "<tr>";

    $values = $rec->getFields();

    while ($value = array_shift($values))

    {

      print "<td class=\"".$class."\">". 

        $value."</td>\n";

    }

    print "</tr>";

    $rec = $rax->readRecord();

    $row++;

  }

  print "</table>";

  }

  else

  {

    print "<p>There were no results returned.

Either an error occurred on the FMPro Server, or no records 

matched your query.</p>";

}

This results in HTML rendered like the following table:

Making requests with GET

If you request the script through HTTP GET, you will want to look at the request's query string (split over several lines here for readability),

<a href="list.php?http://localhost:471/FMPro?

-db=phonelist.fp5&-format=-dso_xml&-find">

Show phone list</a>

and assign it to the FileMaker Pro URL you want to request within your script.

The complete PHP source is in list.php.

Error Handling

The script in list.php tests to see if PRAX can return a record. But it does not distinguish between a case where there are no records matching a query and the case of an error occurring on the server due to, say, a malformed query URL.

Using the dso_xml syntax, FileMaker Pro returns "401" in the ERRORCODE element, for example, when there are no records matching a query.

Using XML-RPC to Display Query Results

XML-RPC provides another way to connect to the FileMaker Pro database. Since XML-RPC clients exist for many platforms (see xmlrpc.org for a list of implementations) you can connect FileMaker Pro databases to web server clients running on Linux and Unix.

XML-RPC servers can provide several services (in PHP, one script would define each service), so it can provide a convenient container for editing, displaying, and creating records in the FileMaker Pro database.

Moving to an RPC framework also provides infrastructure for handling and reporting errors.

So next we'll build an XML-RPC client and server using the classes from the XML-RPC project on SourceForge.

XML-RPC Server

The following code fragment is part of an XML-RPC server with the supporting the method FMPro.allRecords. It takes the name of a database and returns a Base64 encoded string with the response XML.

function allrecords($m)

{

  global $xmlrpcerruser;

  $err = "";

  

  // decode parameters

  $dbName = xmlrpc_decode($m->getParam(0));

  

  $url = "http://localhost:471/FMPro?-db=".

    $dbName."&-format=-dso_xml&-find";

  

  // now try to read from it

  // prepend @ to function to suppress errors to stdout

  $buffer = "";

  $fp = @fopen($url,"r");

  

  xmlrpc_debugmsg("Got $fp from trying to open $url.");

  

  // read response

  if ($fp)

  {

    while(!feof($fp))

    {

      $next = fread($fp,4096);

      xmlrpc_debugmsg("Got $next from fread on $fp.");      

      $buffer = $buffer.$next;

    }

    fclose($fp);

  }

  else

  {

    $err = "Unable to connect or read from $url.";

  }

  

  // Return response or error.

  if ($err)

  {

    return new xmlrpcresp(0, $xmlrpcerruser, $err);

  }

  else

  {

    xmlrpc_debugmsg("Got from FMPro Server: $buffer");

    $base64String = base64_encode($buffer);

    return new xmlrpcresp(new xmlrpcval( 

      $base64String,$xmlrpcString));

  }

}

XML-RPC Client

The counterpart client script defines a function to call the allRecords method on the server.

function getAllRecords($dbName)

{

  $client = new xmlrpc_client("/FMPro/FMProServer.php",

    "localhost",80);

  $client->setDebug(1);

  

  $msg = new xmlrpcmsg("FMPro.allRecords");

  $msg->addParam(new xmlrpcval($dbName, $xmlrpcString));

  // Dump the wire message sent

  print "<p>Sending message:</p>";

  print "<pre>". htmlentities($msg->serialize())

    . "</pre>";

  

  $result = $client->send($msg,30);

  

  if ($result)

  {

    print "<p>Got a response.</p>";

    if ($result->value())

    {

      print "<p>Got a valid response.</p>";

      // The result is a Base64 encoded string

      $encodedResult = xmlrpc_decode($result->value(),

                         $xmlrpcString);

      $decodedResult = base64_decode($encodedResult);

      // Dump the response sent

      print "<p>Got response:</p>";

      print "<pre>" . htmlentities($decodedResult).

        "</pre>";

      return $decodedResult;

    }

    else

    {

      print "<p>A fault occured.</p>";

      print "<p>".

          $base64Result->faultCode().

          ", explanation: ".

          $base64Result->faultString().

          "</p>";

    }

  }

  else

  {

    print "<p>A Low Level Error Occured.</p>";

    print "<p>Error # " . $client->errno . ": ".

        $client->errstr . "</p>";

    exit(0);

  }

}  

To get around problems with passing XML strings in XML-RPC, the server encodes the response in Base64 before sending it. The client decodes the incoming string and returns it.

The scripts for the XML-RPC server are

• FMProServer.php
• FMProClient.php
• xmlrpcList.php

xmlrpcList.php demonstrates the service. Debugging is turned on so you can see the stages of the request.

Extending the Client and Server

If it's not critical for the application to deliver the most current version of the phone list, then the getAllRecords function could cache the decoded XML in a file, and only call the XML-RPC server after some expiration value has elapsed.

The XML server could be extended with a method to list the open databases on the FileMaker Pro server.

To request a list of the open databases on the server, request the URL:

http://localhost:471/FMPro?-format=-dso_xml&-dbnames

Adding and Editing Records and XSLT

The fp_xml format contains metadata describing the database, along with results. FileMaker Pro returns this format after adding or editing records.

For example, after adding a new record to the database, FileMaker Pro returns:

<?xml version="1.0"?>

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

  <ERRORCODE>0</ERRORCODE>

  <PRODUCT BUILD="04/20/2001" 

    NAME="FileMaker Pro Web Companion" VERSION="5.5v1"/>

  <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="simple"

    NAME="phonelist.fp5" RECORDS="6" TIMEFORMAT="h:mm:ss a"/>

  <METADATA>

    <FIELD EMPTYOK="NO" MAXREPEAT="1"

      NAME="Name" TYPE="TEXT"/>

    <FIELD EMPTYOK="NO" MAXREPEAT="1"

      NAME="Phone" TYPE="TEXT"/>

    <FIELD EMPTYOK="NO" MAXREPEAT="1"

      NAME="Location" TYPE="TEXT"/>

  </METADATA>

  <RESULTSET FOUND="1">

    <ROW MODID="0" RECORDID="7">

      <COL>

        <DATA>Todd Clerical</DATA>

      </COL>

      <COL>

        <DATA>408 999-0000</DATA>

      </COL>

      <COL>

        <DATA>Cupertino</DATA>

      </COL>

    </ROW>

  </RESULTSET>

</FMPXMLRESULT>

This return set is not suited for formatting with PRAX. XSLT, or an XML parser, is better suited here. PHP can provide support for both of these techniques; however XSLT and DOM are not part of the standard PHP build. The virtue of XML-RPC is interoperability, and you could write a client in Java or Visual Basic which exploits the relative ease of integrating XSLT and DOM in those languages.