[Note: Some of the material in this article, and in today’s demo file ExecuteSQL Sandbox, previously appeared in my March 2011 article, Custom Functions for Internal SQL.]
The other day a colleague remarked, “You know, it’s going to be interesting to see if you SQL guys are still so excited about ExecuteSQL when something gets renamed and your code breaks.” He had a good point: code is “brittle” if it works initially, but then subsequently breaks as a result of a seemingly innocuous action.
Here are some things that can cause ExecuteSQL code to break:
- Renaming a table occurrence (TO)
- Renaming a field
- Using a “reserved word” as a field or TO name
- Having a problematic character in a field or TO name
- Executing the code in a foreign country (!)
- Changing a field’s type (e.g., from text to number or vice versa)
…and today we’re going to look at some defensive measures we can employ to prevent problems when these things occur.
Using the “orders” table in the demo file…
…let’s start with a very simple query:
You will note that we have a static text string referencing two fields and one TO; if any of them are renamed, then obviously the code will break.
This might be a good time to consider employing the GetFieldName function, since it can parse out both the TO name and the field name. However, GetFieldName won’t be enough by itself, because the TO or field name might be reserved — more on this below — or contain a problematic character, such as a space or an apostrophe, or a leading underscore (underscores are only problematic when they’re the first character). Fortunately the Quote function can address all of these concerns.
So let’s define a couple custom functions: GTN for the TO name…
…and GFN for the field name. (Apart from the comments, the only difference between them is which value GetValue grabs.)
Incidentally, I once spent an hour debugging a SQL query referencing a field named “group”, before realizing “group” was a reserved word. An hour of my life, gone forever. But it inspired me to add Quote wrappers to GTN and GFN, so something good came out of it.
At this point we have robust custom functions to designate both TO and field names in our ExecuteSQL code, and returning to our original example… here it is in its brittle form:
And here is an improved version:
…which also includes a CF called CurrencyFormat to make the result look pretty.
With regards to the date argument, do you see how vulnerable it is? Yes, it’s a dynamic parameter, and that means we’ve dodged the bullet of having to format it in ISO SQL-92 format (DATE '2012-10-15' — see the “Dates” section of FM 12 ExecuteSQL: Dynamic Parameters, part 1 for an example), but what happens if someone tries to run this code in Australia, or any other place that formats their dates DD-MM-YYYY? It ain’t gonna work, that’s what, but if we instead write it this way, well then, Mate, the code’ll bloody well work both here in the States, and Down Under too!
Before we move on, I have a bit more to say on the subject of table occurrence names. Every developer gets to decide what threat vectors are worth addressing, and it may be that you believe that the likelihood of a particular TO being renamed is vanishingly slight. In that case, you may decide that GTN is unnecessary… after all, it’s weird having to look at GTN(orders::id) in the above example, when all we care about is the “orders” portion. So you might choose to “go naked” where certain table occurrence names are concerned, e.g.:
A contrary viewpoint would be that GTN doesn’t go far enough, i.e., isn’t completely safe, because it will break if the field that it references is deleted. Since I tend to point GTN at the primary key for the table, the chance of that happening is, admittedly, slim, but there is another way.
Last year I wrote an article on the subject of Avoiding Brittleness, extolling the virtues of Fabrice Nordmann’s FM_Name_ID custom function, which can translate almost any FileMaker object name into its corresponding internal id… and vice versa. I ended up constructing some object-specific versions of his CF, and the one that’s relevant to this discussion is “FMNID_Table”.
You will find it included in today’s demo file, and as per this screen shot from the Data Viewer, it works as advertised…
Once we know the internal id, it no longer matters what the TO name is. The internal id will never change, which means we have the option of coding our TO references like this:
…and that is some seriously robust code.
At the risk of stating the obvious: in the demo file, the code is sitting in a text field, so of course it will not magically update itself if you rename a field or a table occurrence. To actually see the “robustness” in action, you will need to copy the code into the Data Viewer or any other manifestation of the FileMaker calculation engine, and then rename one of the referenced objects.
I’ve made several references today to reserved words. Here is the complete list of SQL reserved words for FileMaker 12 (and 11 too for that matter — the list has not changed). You can also grab it from pages 55-57 of the FileMaker 12 ODBC/JDBC Guide, but a) the following fits on a single printed page, and b) a couple typos in the original have been corrected.
Finally, I started out this article with a six-item list of things that could cause brittleness, and I’ve addressed the first five. What about item #6, “Changing a field’s type (e.g., from text to number or vice versa)”? We’ll get to that, along with a few other odds and ends, in part 2.