FileMaker 12 offers many intriguing new features, including new windowing capabilities, significant charting and container field improvements, and a brand new design surface. But to my way of thinking, all of these pale in comparison to the exciting possibilities offered by the new ExecuteSQL function.
What’s so great about ExecuteSQL? In a nutshell, it allows us to natively (without a plug-in) perform queries against FileMaker tables using SQL (structured query language). This means that we can now write code to answer questions that previously required adding new table occurrences and special-purpose fields to serve as relational predicates.
Incidentally, if you’re curious about SQL in general, here are some good starting points:
And of course you’ll want to grab a copy of the FileMaker 12 ODBC/JDBC Guide, which offers tantalizing glimpses of what FileMaker is capable of SQL-wise under the hood. Much of the information in Chapter 7 is directly relevant to the ExecuteSQL function.
Next, before we proceed, let’s make sure two things are clear:
1) We’re talking about a single word function called ExecuteSQL, not the venerable (two word) “Execute SQL” script step which is for external ODBC sources, and…
2) Although the function is named ExecuteSQL, in FM 12 a more accurate name for it might be ExecuteSQLSelect, because currently it only supports “SELECT” statements. If you need more SQL power you can achieve it with a SQL plug-in such as Dracoventions SQL Runner, or MyFMButler doSQL, both of which are FM 12 compatible.
I’ve written a number of previous articles on the subject of tapping into FileMaker’s internal SQL engine (a.k.a. “FQL”), using a SQL plug-in:
- Filtered Relations, part 3
- FileMaker’s Internal SQL Engine, part 1
- FileMaker’s Internal SQL Engine, part 2
- Custom Functions for Internal SQL
- FileMaker 11 Internal SQL Changes, part 1
- FileMaker 11 Internal SQL Changes, part 2
…and if you bear in mind that ExecuteSQL only supports “SELECT”, and that this capability is now native (i.e., does not require a plug-in), you’ll find that much of what was written in those articles is still relevant. I don’t intend to endlessly plagiarize my earlier writings, but I do think this from February 2011 is worth repeating:
SQL can appear daunting when you first encounter it, but with trial and error you get the hang of it, and pretty soon don’t want to live without it. For a FileMaker developer, one of the amazing things about SQL is that it can perform complex relational operations that are completely independent from the Relationships Graph. SQL doesn’t care about the Relationships Graph at all, except that when you point it at a “table”, you must specify a table occurrence name, not a table name.
The main thing to keep in mind — and I’m certainly conscious of it as I write these words — is that it’s okay to take tentative baby steps with ExecuteSQL. That’s why we’re going to keep things very simple today.
A basic SQL SELECT statement takes this form:
SELECT <some information>
FROM <one or more tables>
WHERE <one or more logical tests are true>
For example, given a table of invoices, this statement…
SELECT pk_invoice
FROM invoices
WHERE total >= 1500
…will return all the primary keys in the table for invoices where the total is >= $1500. Here’s how it could be expressed in FileMaker 12:
ExecuteSQL (
"SELECT pk_invoice
FROM invoices
WHERE total >= 1500"
; "" ; "" )
Really, it doesn’t get much simpler than this, so this is a fitting example for our first demo, ExecuteSQL demo 1.
Note that ExecuteSQL takes four parameters, and the first three are mandatory:
The first parameter is the SELECT statement, the second and third are the characters you wish to use as column and row separators, and we’ll get to the fourth one — which is optional — in example 2, below. Although the second and third parameters are mandatory, you can specify "" (a pair of double quotes) to accept the default separators, which are a comma between fields, and a hard return between rows.
Incidentally, since in the first demo, I was only asking for one field per record, and wanted to see a comma-separated list of values, I left the field separator parameter alone, but specified that the row separator should be “, ” (comma space) as per the highlight below:
Next, let’s take a look at a slightly more complicated (though still very simple) example…
SELECT count ( * ) // count table rows
FROM invoices
WHERE total >= 1000 and total <= 2000
…and you can see two versions of this in our second demo, ExecuteSQL demo 2.
The first field, c_count_basic, is just a variation on the approach used in the first demo, but what’s going on in c_count_dynamic? Well, if you’ve read the online Help page for ExecuteSQL (and who hasn’t?), these highlighted portions may have caught your eye.
Did you notice that the “arguments” parameter is plural? You can embed as many “?” characters as you wish within your SQL statement, as long as you provide a corresponding number of “arguments” to tie them to.
And this, also from the online help, is why you might care to go to the trouble.
SQL injection attacks are beyond the scope of today’s article, but here are some links if you’d like to learn more:
And finally, we’re going to take a look at the ExecuteSQL example from FileMaker’s online Help page. This was actually the first ExecuteSQL demo I built, because I was having trouble coming to grips with this “dynamic parameter” business. Demo file: FM12 ExecuteSQL Help Example
So, just a few points to make:
1. Unfortunately, there’s a minor discrepancy in the code as written on the Help page… the name of the table (and corresponding occurrence) is “Salaries”, but the code below refers to “Salary” (singular). This has been corrected in the demo file.
2. Once I’d compensated for the above problem I figured I was home free, but for an embarrassingly long time I could not get the code to work. Finally I discovered the problem… and it’s just the sort of thing that FileMaker Pro shields us from, but that won’t fly out in the “real world” of SQL:
EmpID in the Employees table was defined as a number field, but I’d mistakenly defined EmpID in Salaries as a text field. As I say, FileMaker will let you get away with it assuming the data matches…
…but that pesky SQL “join” operator takes a stricter view of such things. (I mention this on the assumption that if I made this mistake, sooner or later someone else will make it too.)
3. And finally, in case you aren’t familiar with this one, here’s an oldie-but-goodie formatting trick for percentages that have been multiplied by 100 to appear as standard numbers rather than decimals:
Well, that wraps it up for today. In upcoming postings we’ll have some more fun with ExecuteSQL. I don’t know about you, but I can hardly wait.