[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.
Thanks for all of these blog posts, Kevin. Really interesting and useful stuff.
Going deep, Kevin. You’ve reminded me about how well GFN gives us field by reference and avoids the pitfalls of hardcoding.
I worry less about using reserved words. Maybe it’s naive, but I figure most seasoned developers feel little jolts whenever they get too close to reserved words or illegal (or too parochial) characters.
Hi John, I’m glad you found the article useful. Given that the reserved word list includes “Date”, “Time”, “Level”, “Action”, “Interval”, “Language”, etc., I do think you’re being a tad optimistic to think that FileMaker developers will avoid using these as field or table (actually table *occurrence*) names. I have built many systems with fields named “Date” and “Language”. This is why I advocate quoting every field and TO reference, and why I included the Quote function inside GTN and GFN.
Thanks Kevin, a very useful resource
Fair point. Most developers use field naming conventions that will pre-or post-pend such strings with a couple of characters, but I agree that not all do. Belt and suspenders.
Excellent post, thanks much! I was wondering if there’s a handy chart anywhere that shows the functional query types available through native FMP find mode versus ExecuteSQL in 12? One of our solutions is using a PHP/SQL plug-in to perform a complex query that native find mode doesn’t offer, and this plug-in doesn’t run on iOS/FMGo so we are looking to port that process to the new ExecuteSQL.
Hi JR,
Is the query so complex that you can’t simply test it in the Data Viewer? I’m happy to take a look at it, if you want to post it here. Or give me a call, 707-822-6414 if you’d like to discuss.
Regards,
Kevin
This entire series has been very informative, thank you for sharing these techniques! I haven’t had a chance to dive into FM12 for any client projects yet, but when I do, I have a feeling that your analysis of the ExecuteSQL feature is really going to help me hit the ground running.
For emphasis, the other thing that GFN does is give you a warning if, for some reason, someone tries to delete a referenced field. So if the dangers of renaming haven’t convinced you, then that should. ;-)
btw, this is good practice in “regular” FM when using any indirection for the same reason.
I hadn’t thought of that… thanks Jason.
Along the same lines, this morning I helped a colleague troubleshoot some code, and it turned out that he had a minor typo on his Relationships Graph — one of his TOs was misspelled, so his (non-robust) ExecuteSQL query was returning a “?”. I pointed out to him that if he’d used GTN, he could have avoided several hours of hair pulling and teeth gnashing.
Kevin is being overly gracious: I was that colleague. After I saw my typo, I had to agree that indirection is right and I was wrong.
Hi Kevin,
Have you managed to write a working SQL that uses INSERT, DELETE, or UPDATE? I’m trying this right now and it seems I can’t.
Hi Mikhail,
As I wrote the other day, “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.”
Sorry I don’t have better news for you.
Regards,
Kevin
Kevin,
Sorry to be an (almost) newbie, but aren’t you still trading one hardcoded aspect for another? I am referring to the usage of “orders::sales” instead of “sales”. Or is it the fact that, because it is a fully qualified name, FM will ‘see’ that reference and track it, and thus update the reference if someone happens to change the schema to “order::total_sales”?
Hi Justin, it’s a good question, and the waters are a bit muddied by my demo which uses hard-coded examples sitting in text fields… hence my comment in the article that begins “at the risk of stating the obvious…”
If you use GFN in a field definition or a Set Field step, or in the Data Viewer (to give three out of many possible examples), you will see that with GFN you can only enter a valid field to begin with (no possibility of screwing up by entering a typo), and if you later rename a field, GFN will roll with it… i.e., the code will not break and you will see the new field name inside GFN.
Great info, definitely helping me get up to speed with this powerful function.
While I was encouraged to see that the use of the Quote wrapper will allow us account for the disallowed characters (all of my ids start with underscores), doesn’t this negate one of the main advantages of ExecuteSQL which is to allow us to reference fields from non-related tables. As soon as we place the table and field name into the function, Filemaker now needs that table/field to be related to whatever context we are applying it to in order to move forward.
Hi Sander,
As per the “Other Observations” section about half way down the page in part 2…
http://filemakerhacks.com/?p=5013#Other
…your concern appears to manifest specifically with field definitions, and I agree, it’s an annoyance. See if the work around I posted works for you.
Regards,
Kevin
Hi Kevin,
thanks so much for this, and your other, useful, helpful articles. Without wanting to sound ungrateful there is a small typo in your list of SQL reserved words – USER thru USING is repeated! I noticed this while generating a plain text file of the list, which I am happy to supply.
Cheers!
Ben
Thank you and good catch, Ben. It’s now fixed.
Hey Kevin,
Doing some digging into old articles here, but I hope this still comes across your desk.
I have been using GTN/GFN with LOTS of success in most cases… We have, however, noticed a slight bit of a performance hit when using the CFs: if you are looping through something a LOT then the added overhead of the CF can impact the end result. But it isn’t about that that I am writing today…
I have noticed a HUGE performance hit in a some cases:
I was developing a solution for someone and ran across a problem using GTN. In a navigation script, I wanted to check for existence of records in the target table before actually going there. The relationship graph is such that the starting context of the script was a few tables removed from the target table (one step of which was sorted) and doing a regular TO-based check itself caused a performance impact. So I was using a statement that looked like this:
ExecuteSQL ( “SELECT COUNT(*) FROM ” & GTN ( Table::Constant1 ) ; “” ; “” )
I was deeply surprised when this ESQL statement turned out to be causing a very similar sized performance hit. (On the order of 20-30 seconds to complete a call. (This is a local/non-hosted file, running on an SSD.) Simply removing the GTN() portion of the call and using a hardcoded reference dropped the query time to…unnoticeable.
Have you ever run into this before? Is it possible that simply using a fully qualified reference causes FileMaker to actually EVALUATE that reference, too, before simply passing the reference along?
I would be happy to share my file with you; it is highly possible that I did something else wrong. :)
Here’s the text of the CF that I am using…I think it is nearly a direct copy of yours from here (I might have changed the name of the parameter):
—————————————————–
/* ***** Copied from: Kevin Frank, FileMakerHacks.com
GTN [for GetTableName] returns only the name of the relationship, i.e., the table occurrence (TO) name, as opposed to GetFieldName() which returns the relationship + the field name. This is to be used in SQL queries to protect the TO references from breaking if the TO is renamed.
The Quote() function “escapes” problematical characters and SQL reserved words, if any — otherwise, no harm done.
********** */
Let ( [
a = GetFieldName ( afield ) ;
b = Substitute ( a ; “::” ; ¶ )
] ;
Quote ( GetValue ( b ; 1 ) )
) // end let
————————————————-
Thanks,
Justin
Hi Justin,
Using your syntax and your definition of the CF, I just tested GTN on a table with a million records and it returns the record count instantaneously. I pointed it at both a stored and an unstored version of the “constant1” field.
Obviously you’ve uncovered something, but the question is… what? Are you using the most obvious TO, or a convoluted one? E.g., “CUSTOMER::constant1” vs. “parts_lineitems_invoices_CUSTOMER::constant1”?
Happy to take a look at the file if you want to send me a copy.
Regards,
Kevin
Hi Justin,
I’ve taken a quick look at your files and what’s happening is that you’re sitting on a one record TO called Base, and asking the calc engine to reach across a constant equijoin relationship through 2.5 million records in VoterInfo before it can see Households.
If you instead temporarily switch to the Households layout, the request becomes instantaneous. Of course if you’re going to change layouts you can just use Get(TotalRecordCount) instead.
An alternative would be to instead use the FMNID_Table custom function, which is also in the ExecuteSQL Sandbox demo. First you will need to perform a one-time invocation of the CF to determine the internal id of the Households table like so:
FMNID_Table ( "Households" )…which will return 1065097.
Now your script can instead ask this question from the context of Base (or anywhere else) and the result will be instantaneous.
ExecuteSQL ( "SELECT COUNT(*) FROM " & FMNID_Table ( 1065097 ) ; "" ; "" )Hope this helps,
Kevin
Hey Kevin,
Thanks for the detailed reply, and taking the time to evaluate the file. I think it all makes sense now.
It sounds like the root issue is that, because it is an actual fully-qualified reference, FM actually fully evaluates it. Meaning it has to evaluate and look through all the relationships and records to get to the other end. I had always thought that, since it wasn’t being used in a manner that didn’t require it’s value that it wouldn’t have to evaluate the reference, just use the table name reference. In other words, it has to dig into the actual data instead of just using the schema definition.
I like the idea of the FMNID function, but it is a bit obfuscating. I will have to comment it clearly. :)
Thanks again for helping me get a better grasp of the inner workings.
— Justin
Hi Justin, you’re welcome!
thanks for your info on the executesql… I seem to no matter what I am doing get a question mark in my results. I am basically trying to where in one field ‘x’ sum up all the values in field ‘y’ when the value in ‘x’ is present.. Ie… each time the word ‘Commited’ name comes up add all of his values in ‘y’. But following your tutorials.. I am only getting Question Marks!! Any thoughts. (Oh as you’ll see its all through portals!)
ExecuteSQL (
“SELECT SUM (Expenditure::Value)
FROM Expenditure::Value
WHERE Expenditure::PaidStatus = & ‘Committed’ & ”
; “” ; “” )
Try this:
ExecuteSQL (
" SELECT SUM ( \"Value\" )
FROM Expenditure
WHERE PaidStatus = ? "
; "" ; "" ; "Committed" )
Note: “Value” is a reserved SQL word, so I had to wrap it in double quotes (escaped with backslashes).
> “it’s all through portals”
That has no bearing on SQL, which doesn’t know or care about any of the relationships on your graph. The criteria for your query are specified via the WHERE clause.
Thank you. Its still not seeming to work. the result is still ‘?’. I changed the field name so hopefully that eliminated the issue.. but this was what it looked like. I must be missing a trick somewhere!
ExecuteSQL (
” SELECT SUM ( Expenditure::Value_donated )
FROM Expenditure
WHERE Expenditure::PaidStatus = ? ”
; “” ; “” ; “Committed” )
Hi Tim,
Three thoughts:
1) You’re choosing to write “naked” (non-robust) code, but including the “Expenditure::” which SQL does not recognize as valid. That’s why I didn’t include it in my previous example. You can either leave it off entirely, or you can substitute a “.” for the “::” and then it will work, e.g.,
ExecuteSQL (" SELECT SUM ( Expenditure.Value_donated )
FROM Expenditure
WHERE Expenditure.PaidStatus = ? "
; "" ; "" ; "Committed" )
#2. Make sure your quotations marks are straight, not curly.
#3. The whole point of this articule was to code robustly and not have to worry about a number of possible problems, including reserved words (and yes, I too have been known to rename a field to avoid the dreaded reserved word problem), so if you want to follow the robust coding practices suggested in this article you would write it like so:
ExecuteSQL (" SELECT SUM ( " & GFN ( Expenditure::Value_donated ) & ")
FROM Expenditure
WHERE " & GFN ( Expenditure::PaidStatus ) & " = ? "
; "" ; "" ; "Committed" )
Hope this helps,
Kevin
Hi Kevin,
Just been rereading this article. I have some serious doubts about the validity of the ideas underlying it. They have been questioned by others, but not fully enough, to my taste. I’m sorry if I sound rather stern, but that is because I am thinking for myself about how I want to use SQL within my FileMaker projects.
The main question is: why would we want to use ExecuteSQL in the first place?
I think there are three reasons. I am not saying anything new, I just want to make them explicit so we can discuss them.
The first reason.
The native way FileMaker works with related information is through Table Occurrences. TO’s are nice but they have two disadvantages:
1. you have to construct them, each and every time you want to reach related information – even if it is for a script which is used very seldom. This will make the Table Occurrence Graph crowded in the long run, and possibly a mess.
2. a TOG with many TO’s slows the system down.
The second reason.
SQL comes with a lot of nifty built in functions and options, like LIKE or COALESCE or + and so forth. FileMaker does have some of those functions but not all of them.
A third reason.
There is something to SQL which makes it nice to use: if not too complex, an SQL statement is very readable.
More reasons?
I don’t know them. You? SQL in itself is fully transactional, more so than FileMaker (but read Todd Geist on the subject), however, that doesn’t count when using ExecuteSQL which only selects related information.
A disadvantage.
I think that using SQL within FileMaker has one disadvantage: as it is bolted onto the FileMaker Draco engine – deep down FileMaker is not SQL – it will be slower than native FileMaker relations. At least, when those native relations are ‘near’ relations, not too many relation hops away. But it still is very fast.
So.
If ‘unbrittling’ our SQL in FileMaker means that we have to use FileMaker relations, which obliges us to construct TO’s for every field and table we want to reach in a SQL statement, then we fully throw away the first reason.
Furthermore, the resulting SQL is sprinkled with quotes, ampersands and parentheses, and that makes it rather unreadable, so, that does away with reason number three.
Then why?
I think you are very well aware of this, but you are willing to take the burden. Then my conclusion is that you are using SQL for its built in functions. If that is the case, and if we like those so much, then why don’t we ask FileMaker Inc. to make those functions available in native FileMaker? They have the code, it’s in there, somewhere, they know how to program COALESCE, LIKE and the likes, so it should be not a big problem to bring them over to the FileMaker part of FileMaker.
And some functions, like COALESCE, shouldn’t be too difficult to program as a CF.
My conclusion.
For now my conclusion is: I want to use SQL in the first place for the first reason, which is: keep the TOG as simple and clean as possible. So I will work with brittle code, which means that I have to be careful in choosing names and work with naming conventions I can stick to.
That keeps the TOG simple and the SQL readable.
Now, please, explain me why I am wrong.
Kind regards,
Martin Spanjaard
Trias Digitaal
Amsterdam
The Netherlands
Hi Martin,
I don’t think you’re “wrong”. SQL has its place in FileMaker, and my thoughts on how and when to use it are evolving. Under certain conditions a given developer may find the pros of going the FM/SQL route outweigh the cons, but as the saying goes “your mileage may vary”.
Best wishes,
Kevin
I am having a brain freeze.
How do the GFN and GTN custom functions know that the parameter should be interpreted as a field name?
For example, if you are on a record whose id is 101, then GTN (orders::id) should evaluate GTN (101), right?
Hi Sean,
Note that…
GetFieldName ( orders::id ) = orders::id
…and this function is at the heart of the two CFs.
GTN parses out the table name, and GFN parses out the field name — and in both cases the item in question is returned wrapped in double quotes for reasons explained in the article.
But to answer your question directly,
GTN ( orders::id ) = “orders”
Please also note that I have had second thoughts about using these CFs as per this article.
https://filemakerhacks.com/2020/03/01/getfieldname-revisited/
HTH,
Kevin
I *think* I understand.
In my example—GTN (orders::id)—does the custom function have access to both the value of “id” (101), and the fully qualified field name (orders::id)?
So, if you had another Let variable, like c = field + 20, c would be 121?
The CFs exist to make SQL queries more robust, so GTN only sees “orders” and GFN only sees “id”. To get the value inside the field, use ExecuteSQL.