ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

FM 12 ExecuteSQL, part 1

Kevin Frank

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:

…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.

Published by Kevin Frank

16 thoughts on “FM 12 ExecuteSQL, part 1”

  1. Excellent. Just what I needed to get going.

    Perhaps a few more different and simpler examples in the FileMaker help file would allow us all to grasp it. (BTW, why is so much computer documentation so eager to look like grep syntax. Are the authors affraid of verbs and plain English?)

    Why don’t FMI make their online-help user driven like php.net’s wiki-documentation:

    http://php.net/manual/en/function.mysql-query.php

    Then we can all add examples as we go along.

    Well, thanx for taking time to educating us.

    Reply

  3. Tho’ still an SQL novice, I’m really excited with this new functionality. The ExecuteSQL() function is faster than the previous plugins.

    I started playing around with SQL last year and used an SQL_Sandbox table (entirely inspired by your’s) to track my queries and how long it took for each to run. I added this to a solution with quite a lot of data.

    Running one of the more complex queries – locally – would take 1 min on the first run and 54 secs on the second run (returning 72k records, sorted, v11 and SQL Runner). Using FM v12, the same query takes 28 secs on the first run and 11 secs on the second run.

    Across a network, v11 would take 2 mins on the first run and 54 secs on the second. (no typos). I don’t have a v12 server yet so I can’t add that comparison.

    There’s one thing that I’ve added to my SQL Sandbox which might be helpful for others… conditional formatting and some extra scripting to ‘step in’ for when it happens to be a slow calc – the results field is bold, solid orange plus I’ve added a ‘Script Running’ note.

    I did this because I was tripping over my own impatience when ‘nothing seemed to be happening’. This interface device helps prevent me from engaging in ‘dumb user shennanigans’.

    Thank you for your continuing insights,

    Regards, Mardi Kennedy

    Reply

  4. Oh sigh. On further testing, most queries take roughly the same amount of time between v11 and v12. (Yes, v12 is a smidge slower, mostly.)

    Mardi

    Reply

  5. I was able to get the SQL statement in the FM12 Help Example, even with the differing field types, by using cast:

    … on E.EmpID = cast(S.EmpID as decimal)

    Reply

    1. Thanks Geoff. I’ve been preaching a mantra of “field types for join predicates must match”, but as you point out, you can use Cast to correct a mismatch.

      Reply

  6. I am so glad that FM now allows the use of SQL. But I am totally lost as far as figuring out where in the application one goes to write the SQL! You have explained that ExecuteSQL is different from Execute SQL, which one invokes under scripts. Can you explain where/how one undertakes to write the SQL for queries against local FM tables? Any help would be greatly appreciated since I have spent the last 3 hours trying to find this out.

    Reply

    1. Hi Leslie,

      There isn’t a SQL expression builder per se in FileMaker. Instead you can use the Specify Calculation dialog which can be accessed from many places, including the Set Field and Set Variable script steps, “Edit Expression” in the Data Viewer, to define either a calculated field, or an auto-enter calculation expression for a non-calculated field… etc.

      That’s assuming you want to hand-code your SQL statements. If you’re looking for a utility to help you create SQL statements, you could try SeedCode’s SQL Explorer or Eden Morris’s. SQL Builder.

      Hope this helps,
      Kevin

      Reply

  7. Kevin,

    Thanks so much for the info and recommendations! I did download SQL Explorer, but the tutorial I watch started from a page within FM which I didn’t know how to find and it didn’t seem to explain that one invoked the Execute SQL function as a calculation. So now I know that at least. I’m just not sure which access point to use. I experimented with following the directions in my FM Missing Manual book, but that just showed me how to create a calculation for a single field. I’m just looking for an “old fashioned” blank white screen where I can start typing things such as “select a.field1, b.field2 from tableX=a and tableXX=b where….” It’s been a long time since I wrote SQL since I’ve been using Access. But you get the idea.

    I so appreciate your help!!! We newbies need all the help we can get!

    Leslie

    Reply

    2. Hi! I’ll chime in here. You *can* use a plain text field and then create a script or calculation to Evaluate() the entered text. This is what I did in many of my demos on http://filemakerhacks.com/?p=6406
      Be sure to download the PDF, as it has more information than in the article!

      Reply

  8. Hi Kevin,

    Aaaaahhhhhhh! This might explain a lot! I don’t have FM advanced. I’m off to download an “SQL Sandbox” demo. Thanks so much!!!!

    Leslie

    Reply

  9. Hi Kevin,

    Thanks so much for your feedback! My problem is trying to figure out how to build table/database level queries instead of queries that affect only one field. Seldom do I need to create calculated fields. But I do need to write queries that full data from multiple fields within a table or from multiple tables. And I typically only want results and not a report showing every record that matches my criteria. I’ve inferred from a number of sources I’ve researched that the advanced version of FM has a data view screen which allows the user to build SQL queries totally independently of individual fields. Do you know if that is true?

    Again, many thanks!

    Leslie

    Reply

  10. This was exactly what i was looking for. A way to get text and show that on a layout without needing to create a relationship every time. Thank You !

    Reply

  11. Hi Kevin – I’m just starting to dive in with ExecuteSQL. I’ve got it working with some simple queries to fetch a found set of records. I was trying to use it to return a list of records from 2 tables (MessagesIn and MessagesOut) and then sort the combined result by a timestamp so I can view a list of all messages in and out in date order.

    Is it possible to use ExcecuteSQL to search on more than 1 table and sort the combined results by a timestamp field? I’m struggling to get this working and not sure if it’s possible.

    thanks,
    Steve

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.