Best practices Archives

Restoring FileMaker 12 Backups With Managed Containers

November 28, 2012 by Darren Burgess 17 Comments

In the course of my work on a customer project, I discovered that the links to externally stored container fields with a FileMaker Server 12 backup can break if the backup is not restored properly. After trying a number of different scenarios, I have come up with a list of circumstances where container fields will remain intact and when they won’t.

All examples are using a Windows based FileMaker server and apply equally to both secure and open external container storage. Understanding these situations will help you properly restore FileMaker 12 files that use external container storage.

In case you need to get some basic knowledge about FileMaker Server 12 and the new features related to backup and enhanced container fields, here are some great resources to get you started:

Using Container Fields in FileMaker 12 (log in to FileMaker TechNet to download)
FileMaker Server 12 Configuration Guide (also found at TechNet)

Circumstances where external container data will break (container fields will show files as missing):

Uploading FMP12 backup files with the FileMaker Server admin console.
Opening backup files in FileMaker Pro — ie, not hosted by a server
Opening files in FileMaker Pro that were removed by the FMS admin console

Circumstances where external container data will remain intact:

Copying FMP12 backup files, with the respective container data folder from the RC_Data_FMS folder, to the FileMaker server data directory, then opening the files with the admin console.
Copying backup files, as above to a different FileMaker server
Downloading the files with FMS admin console, then opening the files in FileMaker Pro (e.g., not hosted). In this case, the databases and remote container file directories are downloaded together, so references to externally stored container fields are preserved.
Downloading the files with FMS admin console, then uploading to another server, or the same server, with the admin console. Again, the act of downloading the files brings the container file directory along with it. Uploading these same files will restore the container data on the FileMaker server.

So to conclude, when restoring backups of FileMaker files that use external container storage, copy the files (don’t upload with the Admin Console) to the data directory of your FileMaker server. In addition to the FMP12 files, copy the respective subfolder for your backup file in RC_Data_FMS to the RC_Data_FMS folder in the server data directory. Note that on Mac OS servers, permissions for the FMP12 file and container directories need to be reset when copied to the data directory and not uploaded with the console.

Tips for Debugging FileMaker SQL Queries

October 11, 2012 by Darren Burgess 3 Comments

Our loyal readers will know that we have published several articles extolling the virtues of the new ExecuteSQL function in FileMaker 12. Well, now it’s time to come clean and tell you the truth about this powerful function and its hidden ability to turn your hair prematurely grey, cause you to lose sleep, or perhaps develop an annoying and embarrassing facial tick.

If you intend to embark on the rewarding path of integrating SQL into your FileMaker development, be warned. And be prepared, as one simple punctuation character, innocent in all other contexts among our many human languages, may lead to premature aging.

? Marks The Spot

This character is the lowly question mark (?), and is the only feedback provided by the FileMaker calculation engine when you write malformed SQL statements within the ExecuteSQL function. SQL queries must be syntactically perfect in order for them to work. When you start writing SQL in FileMaker, you are stepping out of the safety of FileMaker’s tightly controlled programming environment that, for example, does not allow you to specify fields, scripts or other objects that don’t exist in the system.

Here is a short list of syntax errors that will cause your SQL query to return a question mark and for you to lose a few minutes off of your projected lifespan:

Using one of the 250-odd SQL reserved words as a table or field name
Using unacceptable characters (like an underscore ‘_’) as the first character of a field name
Using SQL syntax that is not accepted by the FileMaker SQL API (there are many versions of SQL)
Improper quoting of elements in the query
Improper expression of date syntax

Fortunately for you, I have sacrificed some of my naturally colored hair and a few precious minutes off of my lifespan to bring you some useful tips for surviving the FileMaker/SQL learning curve. Most notably, I want to explore how to use the script debugger and data viewer to assist in the debugging of your SQL queries.

SQL for Beginners

First off, this is not going to be an extensive tutorial in using SQL with FileMaker. If you are just getting started, I would recommend the following resources:

Kevin Frank has a number of blog posts and custom function resources at: www.filemakerhacks.com
Anders Monsen introduces some SQL ideas in this post.
W3Schools.com has a decent SQL beginners tutorial available.
FileMaker’s ODBC/JDBC Guide has useful reference information, including a list of those dreaded SQL reserved words.

How to Debug SQL in FileMaker

Efficient debugging of SQL queries is best accomplished by building the query within a Let function. Here is an example calculation whose job is to determine if the record about to be created in the script “Create Conversion” is the first record of a certain type to be created after a particular date in the Person’s record called the InquiryResetDate:

Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;
  SQL = "
    SELECT COUNT ( id )
    FROM Conversions
    WHERE type = " & type & "
    AND id_Person = " & pID & "
    AND creationDate >= " & resetDate ] ;

// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET

In this case, the SQL query is relying on data values from various local script variables and fields, so we start our Let function by pushing these various values into Let variables. Any of these values can drive unexpected results in the SQL query if they are not properly set up by the script and calculation, so pushing the values into Let variables improves our debugging chances later. (Note that Q() is a custom function courtesy of Kevin Frank that wraps a value in single quotes):

Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;

Next, the SQL query itself is set to a Let variable by combining SQL statements with other defined Let variables to construct an SQL query. Note that careful, readable formatting of the query allows for simplified debugging later:

SQL = "
  SELECT COUNT ( id )
  FROM Conversions
  WHERE type = " & type & "
  AND id_Person = " & pID & "
  AND creationDate >= " & resetDate

Finally, in the calculation section of the Let function, we return a calculation and include, commented out, each of the elements that make up the Let function:

// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET

Debugging in Action

And now the fun can begin! If my ‘Create Conversion’ script is not working, and I suspect a faulty SQL query, I run the script with debugger on. When the debugger runs the step in question, the value for variable $isFirstAfterResetDate will return a “?”:

I then open the script, grab the calculation from the step, and drop it into the data viewer. Since the script is currently running, I am assured that all of the conditions of layout context and field/variable values that may be used in the calculation are in force. I then use the Let function to return the values of the various elements in the calculation by uncommenting them in the data viewer.

Here is the original calculation, returning the result the script is expecting:

We can return one of the Let variables to make sure a valid and properly quoted value exists for that variable:

And here we return the SQL query itself, to ensure that it is properly formed. (Of course the usefulness of this assumes that I know what properly formed SQL statement looks like!):

Once you have started creating SQL queries in FileMaker that actually work, save them in a library. I use Notational Velocity to maintain my library of valid, working SQL queries. Having this as a resource is extremely valuable when it comes time to create a new query of particular type or style that already exists. It is perhaps the most valuable method for debugging SQL – which is simply using proven calculations – and makes it much more likely that your SQL queries will not be buggy and lead to that dreaded, hair-greying, insomnia-producing and lifespan shortening punctuation mark. I wish you the best of luck in the hard work of learning SQL. Post a comment if you have any questions!

Monitoring the Web Publishing Engine in FileMaker Server 11

May 31, 2012 by Anders Monsen 2 Comments

FileMaker Server 11 includes several components to publish databases to client machines. The Database Server is perhaps the most obvious component, as this hosts the databases that are shared with clients.

Any type of web publishing requires the Web Publishing Engine (WPE) to be active and running. Custom web publishing such as PHP communicates to the Database Server via the WPE. But what happens when a web page is no longer is responsive? Which process failed? Is it a page error, a Database Server problem, or an issue with the WPE? As you can imagine, manually checking the state of the server becomes tedious.

The ability to build an automatic monitoring method becomes critical for anyone managing web pages that communicate with FileMaker Server, to rapidly identify problems and minimize downtime.

The Admin Console is a GUI-based tool to manage databases, backup and script schedules, clients, statistics, and the current state of the database and WPE processes. However, as it is a GUI-based system, it needs eyes-on management. In order to automate the process we need to turn to some system-based tools.

Turning to the Command Line

One oft-used method is to create a PHP page that pings the Database Server. If the ping – a PHP request – returns a valid result, then all is well. If the ping returns an error, the administrator intervenes. However, when setting this up, I ran into issues with PHP sendmail failures, and I wanted a process that minimized manual intervention.

To fully automate the process I used one set of tools to check the state of the WPE, and another to automatically switch on the WPE if it wasn’t running. Although FileMaker Server comes with a command line interface – fmsadmin – there is no documented process to manage the WPE in FileMaker Server 11.

When FileMaker, Inc. released Server 12 on April 4, 2012, new options were added to start and stop processes; now you can stop and start the WPE process via fmsadmin. However, if you’re running FileMaker Server 11, you need to take advantage of some undocumented processes. (Here the usual caveat applies: test to make sure this works under your circumstances).

In the Mac OS X environment you can use Unix commands to check running processes. One such command “nc” lets you scan for listening daemons to check if a process is running on a specific port, without sending data. By scanning a WPE port you then can see if this is active for a given IP address.

nc -zw 3 127.0.0.1 16008

This command will return nothing if the process is inactive. If the process is running, the command returns a text string with a success message.

Once we’re able to run such a command in the Terminal, the next step is to set this up in a shell script. If we first check for the WPE and find that it isn’t running, we next verify that the Database Server is running. Once we know this process is active, we then run a sequence of fmsadmin commands. These I gleaned from a website (thanks to Andrew Duncan from Databuzz) that provides a preference pane to control FileMaker Server 11. The key command is:

fmsadmin start|stop adminserver|wpc|cwp

When I tried this out in the Terminal, I found that it only would work for me when I ran them on separate lines, and in a certain sequence.

fmsadmin start wpc fmsadmin start cwp

After all this is combined into a shell script, you can run this as a cron job on Mac OS X. I simply saved the code below into a wpe.sh file, made this executable and set up the cron to check the process. The #lines are comments that tell me what happens inside each branch, and the if statements check to see whether there is a value returned from the query or not.

#!/bin/bash #wpe running up_wpe=`nc -zw 3 127.0.0.1 16008`
if [ -z "$up_wpe" ]; then #echo "wpe is not running"
  up_fms=`nc -zw 3 127.0.0.1 5003`
if [ -n "$up_fms" ]; then #echo "DBS running"
  fmsadmin start wpc fmsadmin start cwp fi fi

This probably is an un-orthodox method to check the WPE process and start it up, but it appears to work. With FileMaker Server 12 you still need to step outside FileMaker Server’s built-in tools to check the process, but you’re able to run the documented “fmsadmin start wpe” command. Possibly there are other ways to monitor the WPE process, and refinements to this method. We’d love to hear any that have worked for you.

12 Days of FileMaker 12: Converting and Upgrading

May 14, 2012 by Kirk Bowman Leave a Comment

You want to start taking advantage of all the great new features in FileMaker 12, but you don’t know where to start? In this session from the 12 Days of FileMaker 12, we explore deploying FileMaker even if your environment has an older version of FileMaker.

Converting and Upgrading to FileMaker 12

Topics in this video include:

Testing your existing solution with FileMaker 12
Converting your existing solution to FileMaker 12
Backing up your data and settings from the current version of FileMaker
Uninstalling the earlier version of FileMaker Pro and FileMaker Server
Deploying FileMaker Server and connect it to your existing web server
Managing existing plug-ins with FileMaker 12

About Tim Neudecker

Tim Neudecker is a leading software engineer who has over 20 years’ experience as a professional FileMaker developer. He spent over ten years as an in-house developer for companies such as Macy’s/Bloomingdales and AGA. In 2003, he joined KyoLogic as co-founder and CTO.

Tim has presented four times to the FileMaker Developer Conference on topics including security, system performance and developer tools. He has published articles in FileMaker Advisor on system integration and performance tuning. In 2003, he was honored with a FileMaker Excellence Award for his contributions to the developer community.

New Year’s Database Resolution: Backup! – Part 3

March 2, 2012 by Kirk Bowman Leave a Comment

To properly backup your FileMaker databases, the first step is to setup backup schedules in FileMaker Server. The next step is to use best practices with the schedules to make sure your backups are safe. Now you need to enable email notification to keep in you informed whether the backup schedules are running successfully.

To enable email notification, first configure the SMTP settings in FileMaker Server. In the Admin Console, go to Configuration > General Settings > Email Notification. Often, you can use the same settings in your email software (Mac Mail, Outlook, etc.). Below is a description of the most common settings.

From Address – Enter the email address from which you want the messages to be sent.
SMTP Server Address – Enter the domain name or IP address of your SMTP mail server.
SMTP Authentication – Most email servers require authentication (user and password) to send email. Check the checkbox and use the default setting of “Plain Text”.
Use Secure Sockets Layer (SSL) – Gmail and several other email hosts require SSL. Checking this checkbox will automatically change the port number.
User – Enter the username to login to the SMTP server. This is usually the same as the From Address.
Password – The password to login to the SMTP server. Ask your system administrator if you do not know the password.

Once you have entered the SMTP settings, click the Test SMTP Settings button. FileMaker Server will prompt you to enter the email address to send a “test email”. FMS will then display a dialog to inform you whether the email was sent successfully. The test email should look something like this.

Next, you should enable Notification Settings for the server. This sends email messages when there are general errors (not related to a backup schedule) with FileMaker Server.

Enable Email Notifications – Check the checkbox to send notifications. (This can be the same email address as the From Address above.)
Send Email Notifications To – Enter the email address to which to send error messages.
Security Level – Use the default setting of “Errors only”. The “Warnings or errors” setting will overwhelm your inbox with unnecessary email.

Now, you are ready to setup email notifications for your backup schedules. If you followed the recommendations in the first article, you will use notification for the Daily and Weekly schedules. This will notify you each day if you backups are running successfully without overwhelming your inbox.

In the Admin Console, go to Administration > Schedules and right-click on the Daily schedule. Choose “Edit a schedule” from the contextual menu. Click the Next button for the first four screens in the schedule wizard. On the fifth page, simply check “Send email notification” and enter the “To” address to send notifications for the backup schedule. (FMS will default to the email address from the Notification Settings above.) Click the Finish button.

The schedule will send an email notification whenever the backup executes. The email will describe whether the backup was successful or failed. To test the schedule notification, right click on the schedule and choose “Run a schedule now”. The backup will be performed and then FMS will send the email notification.

Email notification is your first line of defense to ensure your backup schedules are working properly. Setting up the notifications is not enough. You must have the discipline to read the email each day to make there are no errors with your backups. The worst time to find out your backups are not working is when you need a backup copy to recover some data.

FileMaker Server-Side Script Sequences

February 22, 2012 by Anders Monsen 1 Comment

FileMaker solutions often employ what’s know as robots–computers that run FileMaker scripts so that users do not need to execute scripts manually. The release of FileMaker Server 9 debuted server-side scripts, which can reduce the need for robots and take advantage of Server schedules. In this post I’m going to take a brief look at this scripting option.

In FileMaker Server, when you select “Run Script” there are three kinds of scripts available:

FileMaker script
System-level script
Script sequence

As with any instance of limited resources, decisions must be weighed before proceeding. FileMaker Server has a limit of 50 schedules. System-level scripts can be run as cron events (Mac OS X) and system events (Windows), so these scripts might not be the best use of FileMaker Server’s schedule resources.

Running FileMaker scripts can off-load client processes onto the server, thus eliminating the use of robot machines. Script sequences allow a mix of both worlds, since you can script both FileMaker and the OS to run repetitive tasks.

Getting Started

Setting up schedules takes place in the Admin Console, which has a series of easy steps to create a schedule. Once you have selected “script sequence” for the type, you’ll see a list of databases along with a place to enter an account to access the database.

Since a database is usually accessed by FileMaker Pro, special consideration is necessary to run scripts server-side. A file with an OnOpen script needs to take Server into consideration. One option is to use Get ( ApplicationVersion ) and exit the opening script if the result is “Server”.

After the database and account are entered, you select the script that the server will run. (Adding an option to filter for scripts like the Manage Scripts dialog in FileMaker Pro might be a nice future enhancement for FMS.) As with FileMaker Pro, you also can specify an optional parameter.

Setting Sequence Permissions

The next step is to select the sequence options, which is where it gets interesting. Here you can select system-level scripts to run before and/or after your FileMaker script, along with any system-level parameters (see below). Importantly, you also select the account to run the system-level scripts. On Mac OS X this is important because it must match the permissions on the system-level script inside the Scripts folder for FileMaker Server.

Since the default account is fmserver, you should set the system-level script username to fmserver and the group name to fmsadmin. The drop-down will let you select any script in the Scripts folder, but permission issues can cause errors in the execution of the schedule. An enhancement in FMS 11 allows you to set a time limit on the schedule, which can be helpful when a script sequence may run too long and collide with subsequent schedules. In the example below the FileMaker script runs after the shell script. Other examples might have a shell script run first, followed by a FileMaker script, then another shell script. This could involve some method to load and transform data, then export and move the data via the OS. This opens many additional possibilities for FileMaker integration.

Conclusion

Unfortunately, large aspects of server-side scripts remain in a black box environment. Error messages aren’t always clear. The script may fail and the Schedule status shows a FileMaker error. Checking the Log Viewer usually provides more clues, such as Error 100 for file not found, or another FileMaker error. Sometimes the status may be OK but the shell script portion fails, which requires troubleshooting outside FileMaker. Agnes Riley of Zero Blue wrote a post in 2010 covering some of the caveats. Despite these cautions, server-side scripting has tremendous benefits and power.

A subsequent post will look more closely at running FileMaker-only server-side scripts. Does your solution use sever-side scripts? What are some of the best practices and problems you encountered?

New Year’s Database Resolution: Backup! – Part 2

January 6, 2012 by Kirk Bowman 1 Comment

In part one of this series, I explained the changes I would make to the default backup schedules installed with FileMaker Server—the “what”. Now I want to take a moment and explain the “why” and clarify some points about my recommendations.

23 Hourly Backups – The Hourly schedule is set to keep 23 hourly backups at a time. Why not 24? Because the most recent Daily backup is the 24^th.
6 Daily Backups – For the same reason, the Daily schedule is set to keep 6 daily backups. The most recent Weekly backup is the 7^th. These steps conserve the disk space for two backups.
Verification – Why verify? Because the worst thing to happen to a database is to lose data. Verification checks the integrity of the backup and can send an email if the database is damaged. In most cases, if the backup is damaged it is likely that the live database is too. This is your early warning system.
Hourly Verification – If your database is small to medium (500MB or less per file), you can probably verify each hourly backup without much impact on performance, depending on your hardware. If it does create an intolerable slowdown, disable it for Hourly only and leave it on for Daily and Weekly.
Cloning – Why clone? Because if your database is damaged, depending on the degree of damage, you may need to recover the database. Having a pristine clone (structure without the data) makes it easier to restore a healthy database because you can usually rule out damage to the structure with a good clone.
Drive Size – The default location for backups created by schedules is the FileMaker Server/Data/Backups folder on the boot drive. It is common for boot drives (or partitions) to be fairly small (40GB-ish), especially on Windows or virtual servers. Storing 33 backups (23 + 6 + 4) can fill up the boot drive quickly. Make sure you do the math to ensure you have ample drive space.
Backup Location – A better idea is to store the database backups on a separate drive or partition, usually with much more drive space. On a Windows server this could be the C drive for the OS and FileMaker Server, and the D drive for the databases and backups. (Using a third E drive for the backups is even better). This also helps increase the speed of the backup process.
Archiving Backups – Making backups of the database to the local hard drive is not enough. You need to use backup software (outside FileMaker Server) to make copies of the backups to another location. Some options include tape drive, external hard drive, network volume, or even offsite. In a larger organization, this will probably be the responsibility of the IT group.
DO NOT Backup the Live Database – The database hosted by FileMaker Server is available 24×7, meaning the file is always open. Backing up an open file is a no-no, especially with databases because it can corrupt the live database and the backup. Configure your backup software to exclude FileMaker Server/Data/Databases (home of live databases). In addition, do not:
- Use Time Machine to backup the live databases
- Use “snapshot” technology on Windows to backup the live databases
- Enable virus scanning of the live databases
- Enable file sharing on the FileMaker Server/Data/Databases folder

As you can see, having good backups of your FileMaker database requires several considerations. Hopefully, these tips will make your backup plan more safe and reliable. In coming articles, I will discuss email notification and offsite storage for your databases.

New Year’s Database Resolution: Backup! – Part 1

January 4, 2012 by Kirk Bowman 5 Comments

So, it is a brand new year and everyone is making resolutions for their personal and professional lives. Along with going to the gym and learning to fill-in-the-blank better, I want to challenge you to make a goal to backup your database better. (Hopefully, you are already backing up some.)

The easiest way to backup your FileMaker database is to use FileMaker Server. So, if you are not using FileMaker Server, why not? If you are using a hosting provider, then I will assume they are backing up the database for you. (Do you know how often they backup your database?) Otherwise, I assume you are hosting the database on your own server—with FileMaker Server.

FileMaker Server includes “Schedules”, the ability to automatically backup the live database at a regular interval. The database server comes with three schedules already created: Daily, Hourly and Weekly. However, only the Daily schedule is enabled by default. I am going to recommend some changes to the default schedules to improve your database backups.

To view schedules, login to the FileMaker Server Admin Console. Open a web browser directly on the server and type this URL http://localhost:16000. This should display a web page to launch the Admin Console. Of course, you will need the username and password for the Admin Console. Once you login, click on Schedules under Administration in the navigation tree.

To edit a schedule, right-click on it in the list of schedules and select Edit a Schedule. This launches a wizard to walk you through the various options for the schedule. These are the changes I recommend for the default schedules, which will backup the database once per hour for 24 hours, one per day for 7 days and once per week for 4 weeks.

Hourly Schedule

Select the Backup Folder and Options
- Maximum number of backup to keep: 23
  Note: The daily schedule will take care of the 24th hour.
- Verify backup integrity: Checked (by default)
  Note: For large databases you may need to uncheck this option due to the time required to verify the database each hour.
- Clone the backup file: Checked
Select the Schedule Details
- Frequency: Daily
- Run every: 1 Hours
- Start time: 12:00 AM
- End time: 10:00 PM
Schedule Summary
- Enable this schedule: Checked

Daily Schedule

Select the Backup Folder and Options
- Maximum number of backup to keep: 6
  Note: The weekly schedule will take care of the 7th day.
- Verify backup integrity: Checked (by default)
- Clone the backup file: Checked
Select the Schedule Details
- Frequency: Weekly
- Select the days of the week: All days except Sunday
- Once per day, Start time: 11:00pm
Schedule Summary
- Enable this schedule: Checked (by default)

Weekly Schedule

Select the Backup Folder and Options
- Maximum number of backup to keep: 4
- Verify backup integrity: Checked (by default)
- Clone the backup file: Checked
Select the Schedule Details
- Frequency: Weekly
- Select the days of the week: Sunday
- Once per day, Start time: 11:00pm
Schedule Summary
- Enable this schedule: Checked

This is a great start for your New Year’s Database Resolution. In the next few articles, I will cover other ways to improve on this basic setup including email notification, offsite backup, and drive space. Together, we will help you get your database in good shape to start 2012.

Monitoring the FileMaker Server xDBC Process

November 9, 2011 by Anders Monsen 17 Comments

Sending data to FileMaker from external sources poses interesting options and challenges.

One of our development projects required enabling ODB/JDBC and sending data via JDBC (Java Database Connectivity). The sending application used FileMaker’s client driver, which allowed it to send SQL insert statements to the FileMaker database via FileMaker Server. This required Server Advanced, and enabling ODBC/JDBC in the server settings:

This process worked seamlessly, as long as FileMaker’s “fmxdbc_listener” process remained active. But, in our non-perfect world, sometimes this process failed.

Even though the configuration setting remained checked in the Admin Console, the process simply stopped, and our database no longer would accept incoming connections from the Java app. Unless someone happened to watch the process, hours or days could pass before anyone noticed this problem. We needed a method to monitor the fmxdbc_listener process. (Since the servers run on Mac OS X, this solution is not cross-platform.)

Activity Monitor showed all FileMaker Server processes, when filtered by “fm”:

While the Activity Monitor provided a good user interface to see all system processes, we needed to automate and script the scenario.

The command line script was something that could be automated. Using “ps” we could view different processes. Targeting one specific process required adding “grep”, such with this command:

ps aux | grep '[f]mxdbc_listener'

This generated an output when the process ran:

fmserver   624   0.0  2.3   213008  97120   ??  S     9:49AM   0:00.27 ./fmxdbc_listener

If the process went offline, the Terminal showed an empty process. This could then be written as a shell script, or in our case, a PHP command line script with the option to send an email.

#!/usr/bin/php -q

<?php
  error_reporting(0);

  $fmx = exec("ps aux | grep '[f]mxdbc_listener'");

  if(empty($fmx)) {
    $to      = 'admin_group@server_name.com';
    $subject = 'XDBC Listener unavailable';
    $message = 'Reset process';
    $headers = 'From: admin@xdbc_server.com' . "\r\n" .
      'Reply-To: admin@xdbc_server.com' . "\r\n" .
      'X-Mailer: PHP/' . phpversion();

    $mail = mail($to, $subject, $message, $headers);
  }
?>

When we moved the code to the server it failed to generate an email. Since the server resided inside a firewall, we switched to an internal FileMaker process instead. Using the PHP API, we triggered a FileMaker script in a monitoring/logging database, which sent the email via SMTP, a process we already used on other databases on that server and knew would work.

#!/usr/bin/php -q

<?php
  error_reporting(0);

  require_once('FileMaker.php');
  define('FM_HOST', 'IP_Address');
  define('FM_FILE', 'xdbcLog.fp7');
  define('FM_USER', 'web');
  define('FM_PASS', 'web');
	
  $fm = new FileMaker( FM_FILE, FM_HOST);
  $fm->SetProperty( 'username', FM_USER );
  $fm->SetProperty( 'password', FM_PASS );
  $fmLayout = 'Log';
  $scriptName = 'Send_eMail';
	
  $fmx = exec("ps aux | grep '[f]mxdbc_listener'");
	
  if(empty($fmx)) {
    $script = $fm->newPerformScriptCommand( $fmLayout, $scriptName);
    $result = $script->execute();
  }
?>

To automate this process we enabled it as a cron job, checking the server every few minutes. To reactivate the process, you could simply uncheck the ODBC configuration, save your action, check it again, and save once more. So, although human intervention is required to restart the process, setting up this system script send an immediate alert when the process is detected as offline. Whether this is foolproof method for process-monitoring remains to be seen. Why the process fails is another question. If other FileMaker developers have encountered similar issues, I would be interested in hearing how you have dealt with this issue, both on Mac and Windows servers.

The Importance of Consistency

September 13, 2011 by Martha Zink 4 Comments

Fields. Layouts. Calculations. Relationships. All of these are invaluable pieces of FileMaker and we, as developers, implement our own flare, excitement, and style to each of them. And as a lover of the Liberal Arts I believe that we should always thrive to be excited about what we do and how we do it. But do you know what makes it a better experience? Consistency. I’ve learned this lesson the hard way.

Flashback to the first database I ever built – I was so busy trying to understand and get things done that I forgot the magic of consistency.
Flash forward to 3 years after I built that database – I now had a sticky mess in front of me. Every time I had to change a script or find a relationship, I struggled with what I called that field or how I named that script.
Cut to now – While I still falter in consistency from time to time, I have learned the art of sticking to a style.

Here are some places where consistency can really help:

Fields

Choose a strict naming convention and stick to it! This is probably the predominant place for variation.

Are you going to use spaces?
Are you going to use underscores?
How do you name your primary keys?
How do you name your foreign keys?

With a recent development project where I needed to use SQL, I learned to avoid using an underscore at the beginning of fields (mainly for primary and foreign keys). Here’s an example of how I’d name fields:

NameFirst
NameLast
AddressStreet
AddressCity
AddressState
AddressZip
DateBirth

So what do you notice about the field names above?

All fields that should be “grouped” begin with the same prefix. Most people wouldn’t say “name first” but from an alphabetical perspective, it helps to keep first name and last name together.
Camel case makes the field names easier to read
Above, I chose to avoid spaces but you could use underscores (e.g., “Name_First” and “Address_City” )

Layouts

Using consistency in layout names can eventually make for flexible scripts. For example, pick a suffix for layouts that are form view versus list view:

Contact – Main
Contact – List
Project – Main
Project – List

By being consistent, I could write a script that uses the Go To Layout step but instead of hard coding the layout name, I can use a calculation. Imagine that you are on the “Contact – Main” layout and you want to go to the list view. And you wanted a similar button for the “Project – Main” layout that took you to the corresponding list view. You could write two scripts, or write one flexible script!

Here’s the calculation that would be used for the Go To Layout script step:

Substitute ( Get( LayoutName ) ; "Main" ; "List" )

A calculation like that would work for either the Contact layouts or the Project layouts. Talk about flexible and easy!

Calculations

For calculations, I’ll focus on naming variables (whether in a script or in a Let function). The main goal for these is to be predictable. It’s never fun to hunt through a script to find what that variable name is. And as we know, since we have to always type in variable names (not select them like for fields), predictability means efficiency. Here are some examples of consistent variables I use:

$count – Usually used in a looping script to tell me what iteration I’m on
$total – If my looping script is based off of some stopping number, $total defines that number or stopping point
$sp – Get( ScriptParameter ) in a script
v – In a Let function, if I’m working with a field, I usually name a variable “v” for value so that I don’t have to keep referencing the long name of the field (or some other calculation)
theList – Naming a variable “list” is not a good idea as there is a calculation that uses that word. Add a prefix to all “FileMaker words” – the, a, your initials, etc.

I’m by no means preaching the need to use my naming convention. I’m just alluding to the fact that I’ve “memorized” these values, which means that I won’t be surprised when I open a script and see a $count variable, for example. The purpose becomes very obvious to me (or at least obvious enough with some context clues).

Relationships

This, for me, is a place where consistency makes for MUCH faster development. If your naming convention is inconsistent, you spend more time searching for the name from a dropdown list, like in a Go To Related Record script step, than you do actually developing the database. Here’s my naming convention (which is not an uncommon one):

Name your tables in either singular or plural form but stay consistent
Name your relationships based on the path
Use all caps for the Table Occurrence’s table name
Use a double underscore in special cases (self joins, for example)

Here are some examples:

PROJECT
project_PROJECT__sameCustomer (a self join to show projects with the same customer)
project_TASK (a relationship from Project to Task)
project_INVOICE (a relationship from Project to Invoice)
project_invoice_LINEITEM (a relationship from Project to Invoice to Line Item)
project_invoice_lineitem_PRODUCT (a relationship from Project to Invoice to Line Item to Product)

The point to take home is not my methods, but the idea of having a method. The real problem comes when you open up a solution you started 6 months, 12 months, 24 months later and nothing “makes sense” because there’s no method to the madness.

What’s your method?