FM 12 ExecuteSQL: Dynamic Parameters, pt 1

I have a confession: when I first read about dynamic parameters in the Help entry for ExecuteSQL, my initial reaction was, “Why do they have to make it so darn complicated?”

I have another confession: I am now singing a very different tune. I’ll get to the reason for this in a minute, but first let’s compare two ExecuteSQL statements, which were discussed last month in FM 12 ExecuteSQL, part 1.

Both calcs ask the same question, but the second one employs dynamic parameters, and as I mentioned at the time, you can embed as many “?” characters as you wish within your ExecuteSQL statement, as long as you provide a corresponding number of “arguments” to tie them to.

The official word from FMI is that dynamic parameters can be used “in order to avoid security vulnerabilities through injection attacks”, but there’s another, more utilitarian, reason to use them: They make your life easier.

Actually, let me rephrase that: They make your life A LOT easier, for example…

1. The body of your SQL statement will flow more smoothly (it will be both easier to write, and easier to read)
2. Parameter arguments behave in a FileMaker-like manner, thereby eliminating a number of SQL headaches
3. The FileMaker calc engine won’t let you close the calc definition dialog if there are obvious problems with your parameter arguments

#1 seems to me to be self-evident, and if you’re thinking, “Yeah, but you’ll end up segmenting your SQL statement into chunks anyway to avoid breakage when fields are renamed…” a) you’re absolutely right, and b) that will be the topic of an upcoming article, so I’m going to skip over that concern for the time being, except to say that if you incorporate dynamic parameters into your code, the amount of segmentation will be less than it otherwise would be.

But what do I mean in #2 about arguments behaving in a FileMaker-like manner and avoiding SQL headaches? Namely this: FileMaker’s internal SQL parser is exceedingly picky about certain things that FileMaker is more relaxed about, and by using dynamic parameters, you can enjoy the warm and cuddly treatment you’ve come to expect from your favorite database program.

Of course there’s no law that says you have to use dynamic parameters in your ExecuteSQL statements, but we’re going to compare non-dynamic (a.k.a. “Standard”) and dynamic queries containing 1) text, 2) numbers and 3) dates, and I bet you’ll be convinced by the time we’re through.

All the following examples are based on this simple “reservations” table…

…which is included in today’s demo file, ExecuteSQL Dynamic Parameters, if you’d like to follow along.

Text

Single quotes in SQL are analogous to double quotes in FileMaker. When you reference a text string in the main body of an ExecuteSQL statement, you must wrap the text string in single quotes, but with dynamic parameters you simply treat it like an ordinary FileMaker text string:

With the standard approach, if you omit the single quotes, here’s what you get:

…but if you try to pull a similar stunt with dynamic parameters, FileMaker will do its usual fine job of protecting you from yourself.

Numbers

When you reference a number in the main body of an ExecuteSQL statement, you must NOT, repeat NOT, wrap it in single quotes, but with dynamic parameters you can treat the number exactly as you would in regular FileMaker, which is to say, you can wrap it in double quotes or not, as you see fit.

By the way, here’s something it took me a while to figure out: with the standard approach, what determines whether a string of data is interpreted as a number or as text is the underlying field type, not the data itself. Allow me to rephrase that, because it’s important: it doesn’t matter whether the data looks like a number, what matters is the type of field the data resides in. But you only have to worry about this if you’re using the standard approach… when you use dynamic parameters, the string (not the underlying field type) is what matters.

An example of where this might come up is if you need to query a field that appears to be a number field, based on the visible data. What if it’s really a text field? This screen shot from the demo illustrates the problem:

Once again, we don’t have to worry about this if we use dynamic parameters.

Dates

I’ve saved the best for last. We are so spoiled in the FileMaker world when it comes to things like dates… when you pop into find mode and type ">4/2" (without the quotes) into a date field, what does FileMaker do? First off, it determines (via the regional date format associated with the current file) whether you mean the 2nd of April or the 4th of February, and then it tacks on the current year for good measure. Here’s how a similar query can be implemented via ExecuteSQL.

You may have noticed that so far all of today’s examples have used hard-coded search criteria… that was intentionally done to keep things simple, but let’s end with a date query based on the res_date value in the active record:

We would like to determine the sum of all sales occurring later than the highlighted date, and unfortunately what would strike most FM developers as the “obvious” approach doesn’t work using standard syntax, but works as expected if we go the dynamic route.

The standard approach is going to require a custom function or some Let wizardry, and you can see an example of the former in the demo file.

Well, that’s about enough for today. In an upcoming article, we’ll look at some techniques for making ExecuteSQL statements more robust.