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.

18 thoughts on “FM 12 ExecuteSQL: Dynamic Parameters, pt 1

  1. Tom Ropers

    The date handling itself makes dynamic parameters an excellent choice. I had to develop a solution that essentially acted as a front end for a MySQL backend, and ended up having to do all sorts of work-arounds when handling the dates. If I had FM12 back then, I would have shaved at least a day off of my development and troubleshooting time. Thanks for the write-up!

    Reply
    1. Kevin Frank Post author

      Thanks Eden. I just downloaded the latest version. Incidentally, apart from the obvious practical value of SQL Builder, I really like the design aesthetic.

      Reply
  2. Juz

    Very informative, i really hadn’t grasped the benefits yet myself.
    Nice work and beautifully illustrated (since my 30day FM12 trial has run out, the screenshots are invaluable!)

    Cheers!

    Reply
  3. Steve Nadeau

    Hi,

    thank you for this post.

    I was wondering: is this dynamic parameters only working withing the WHERE or HAVING clauses ? I’m trying to implement this solution in the following quert: “SELECT ? FROM ?”, and it’s not working…

    Have I missed something in the documentation ?

    Cheers !

    Reply
    1. Kevin Frank Post author

      Hi Steve,

      I don’t claim to know the answer, but here’s why I *think* it’s failing. You can only use parameters to specify “criteria” (e.g., “San Francisco”), but not “sources” (e.g., the City field in the Location table).

      FWIW,
      Kevin

      Reply
  4. Daniel Shanahan

    Beware of reserved words when using ExecuteSQL. I had been in the habit of using the field name “date” in a table when there was only one date field and didn’t need a descriptor or modifier (i.e. not a fromDate, toDate, etc.).

    Since “date” is a reserved word in SQL, my queries returned a “?”. Changing the date slightly (e.g. dateReceived ) makes all the difference.

    (Kevin – sorry if this is redundant; you may have already covered reserved words. I didn’t see it in this thread, so thought I’d mention it. Thanks again for these posts – I refer to them frequently!)

    Reply
  5. Jeremiah Hammond

    Definitely agree with the other commenters here: excellent post Kevin!

    I would wholeheartedly embrace parameterized queries but one aspect of them is holding me back: you can’t name the parameters! Not a huge deal if there’s only one parameter, but once you have more than 1, you have to mentally keep track of which question mark goes with which parameter. That, to me, is a glaring flaw and results in statements with poor readability.

    Like all good complaints, I turned this into a positive and submitted a feature request for named parameters. Hopefully mine is not the first of its kind.

    You know what else I should do? Submit a request to rename the “arguments” argument to “parameters”. I think that would make more sense, especially to those who have experience in other SQL environments.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s