BlogFileMaker

Testing whether a database is available on FileMaker Server

By October 31, 2012 September 6th, 2019 11 Comments

Do you ever need to see if a particular FileMaker database is available? Perhaps you have a FileMaker Go solution that you would like to sync with a master database on a server.  FileMaker’s standard way of doing this is using the open file script step to test for database availability.  This works well if you have a good connection and the database you are trying to connect to is available, but what if that database is not available?  FileMaker Go will attempt to connect to that database for a full minute before throwing an error.  A minute is an awfully long time in a Mobile Application.  There is no equivalent to “go get a cup of coffee” in the mobile world.  Users will not sit and stare at their screen for a minute waiting for an application to do something.  They have stocks to check, Angry Birds to fling, zombies to crush.

There is a faster way.  You can host a very small site and use the fancy new “insert from URL” script step in FileMaker 12 to quickly determine if a database is available.  Don’t worry, you don’t have to be a web programmer to do this.  There’s no fancy framework to learn, and the set up is minimal.

You will want to set up FileMaker Server for custom web publishing, and here’s a handy link to help you do just that: FileMaker – Custom Web Publishing with PHP – PDF

Once the server is set up for custom web publishing and tested that it is working, follow these 6 easy steps to make your database testable via the web:

  1. Create a special privilege set that only custom web publishing will use.  I created a privilege set called web.  You will want to give this privilege set only the privileges that it needs to do it’s job and no more.  So in this case, we just need the solution to have access to a single layout and since we will not be adding or editing any information, we don’t need to give it access to scripts or value lists.  For records, I created custom privileges so there is just read only access to a single field.  For layouts, I created custom privileges with view only access to the special layout I created.
  2. In a text editor, paste in the following (adapted from this post):
<?php
require_once ('FileMaker.php');

$fm = new FileMaker();
$fm->setProperty('database', 'test');
$fm->setProperty('hostspec', 'http://localhost');
$fm->setProperty('username', 'web');
$fm->setProperty('password', 'w3b');

$connected = $fm->listLayouts();
If(FileMaker::isError($connected)){
        echo "out of luck";
} else {
        echo 'connected';
}

?>
  1. In the line of code $fm->setProperty(‘database’, ‘test’); replace ‘test’ with the name of the database that you will be test the availability of.
  2. In the line of code $fm->setProperty(‘hostspec’, ‘http://localhost’); replace the ‘http://localhost’ with the URL of your custom publishing site
  3. In the web root folder create a a new folder for this little php snippet and save the text above as test.php (you may have to have whoever is in charge of your site double check the persmission on the folder and file)

Now that wasn’t so hard was it? Now all we have to do is add an Insert from URL script step to insert the contents of this little page into a field (I used a global field).  It would look something like this:

Insert from URL [Select:FieldName;"www.yoursitename.com/test/test.php"]

If the contents of the field is anything other than “connected”, the database is not available.  If the webserver is up, and the file is unavailable, the field will contain “out of luck”; if the web server is completely down, the field will contain the error message.

So you can set a field with a message for your users like this

Set Field [MessageFieldName, if(FieldName="connected";"The database is available"; "The database is NOT available")]

Please let me know what you think of this method of testing database availability.

James Marshall

James Marshall

James has been building solutions in FileMaker for many years, both as an in-house developer and as a consultant.

11 Comments

  • Avatar Nick White says:

    Perfect! was just looking for a solution to monitor our uptime critical FM database to make sure it’s running and this looks like just the ticket.

  • Mislav Mislav says:

    Great tip. I’ve come across the “attempt to connect to that database for a full minute before throwing an error” nuisance before and wondered if there was a better way. Thanks James.

  • Avatar Adrian McManus says:

    Thank you so much for this! Super easy to implement and works great. I’m already integrating it on our next FMP GO build.

  • Avatar Adrian McManus says:

    Here is a variation I made which lets you pass the db name as a _GET variable in the url string ex. test_connection.php?db=YOURFILE

    That way you can check your connection on any file (provided it is web enabled and has the same username/password)

    setProperty(‘database’, $db);
    $fm->setProperty(‘hostspec’, ‘http://localhost’);
    $fm->setProperty(‘username’, ‘web’);
    $fm->setProperty(‘password’, ‘w3b’);

    $connected = $fm->listLayouts();
    If(FileMaker::isError($connected)){
    echo “error”;
    } else {
    echo ‘connected’;
    }

    ?>

  • Avatar James Marshall says:

    Adrian, thanks so much for your update. I’ll fold this into my bag of tricks!

  • As an alternate to Insert… , and pre FM 12 users, you can set a Web Viewer to the URL of the PHP page.

    -lh

  • Avatar Laurent Ades says:

    uh….. looks like a great tip and am close to making it work… BUT… as i’m quite new to the whole cwp thing i’m stumbling on something here : i’m getting the ‘out of luck’ output – when i should not – and the the FMS logs return :

    “whatever.whatever.net 172.16.1.5:60744 cwp PHP 802 575 “/fmi/xml/fmresultset.xml?-db=sifb_db&-layoutnames””

    Why xml ? Something wrong in my cwp setup ?

  • Avatar James Marshall says:

    Hi Laurent,

    Have you double-checked you privilege set for the account you are using has access to web publishing, at least 1 layout, and at least view privileges for at least a single field on that layout? Does the file show that it is available for php in the FileMaker Server console database tab?

  • Avatar Daniel Farnan says:

    Hi Laurent,

    I ran into this too, although I haven’t checked the server logs to confirm that mine is the exact same issue.

    For me, it was because I didn’t have the destination field on the layout (unlike Set Field, the Insert group of script steps require the field to be present on the layout). Turns out it still works even if the field is not on the part of the layout that gets presented to the user, so my interface did not need changing.

    Hope this helps!

  • Avatar Raul Basurco says:

    Hi, I’ve tried to apply this solution but I couldn’t make this work, I use OS X Catalina on both, the Server and Client machines, please, can some explain where to put the php file?, I think that’s the problem. Thank you so much in advance.

    • Avatar Mike Duncan says:

      The PHP file can go on any web server or the local web root of FMS if you have PHP enabled. If you want to skip setting up php, you can also just ping the web publishing engine directly to get XML back by using your servers URL and appending the following to get a listing of available databases: /fmi/xml/fmresultset.xml?-dbnames
      Or, if using the Data API in 17+ versions of FMS, you can hit this url just to get a response from the server and tell if the Data API is available: https://your_domain/fmi/data/vLatest/productInfo

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant