Unlocking Hidden Error Messages in the ExecuteSQL Function

By Daniel Wood, 7 January 2015

EDIT - Simpsons did it!

Well not quite the Simpsons, but maybe you get the cultural reference. Turns out this technique has been referred to previously on the custom function site fmfunctions.com here. Thank you to readers for pointing it out, and Andries Heylen for submitting the function!  That is not to say the rest of the article is not interesting, so please carry on reading :)


A Simple ExecuteSQL Query as Illustration

Below we have a very simple query using ExecuteSQl. This query is asking for the field Tasman_URL from a table occurrence named zdev_Settings.  This table only has a single record, so we do not require a WHERE clause in the query. 

As you can see, when we use the data viewer to evaluate the query, we are given the resulting field value as output, all pretty standard stuff.

Next, lets break the query. To do this we are going to change the field name to Tasman2_URL.

Because this field does not exist, we have a syntax error in the query. Anytime there is an error in our query, FileMaker returns a solitary question mark as output. Oh how useful this is! The question mark tells us something is broken, but most of the time knowing what is broken is hard to track down, particularly in very large queries involving things like joins, where clauses, ordering, aliases etc.


Here's where things get interesting...

So recently I have been developing an internal solution and making more use of ExecuteSQL during development. While testing queries in the data viewer, I happened to enter a particular query in a Let statement. The idea was that if "?" was returned, I wanted the result to be nothing instead of the question mark, eg:

IF ( Query = "?" ; "" ; Query )

When I put this into the data viewer and pressed 'Evaluate' what I saw was quite interesting:

Where did that come from!! The evaluated result of the calculation - which was meant to be nothing - instead was showing as a nice descriptive error message telling me exactly what was wrong in my query.

When I went back into the data viewer calculation, the message was still showing. However as soon as I hit "Evaluate" it disappeared and the expected blank result was shown.


So when does the message appear?

After (not so) extensive testing, I have been able to only reproduce showing the error message in the data viewer, after the "Monitor" button is pressed. Once you go back into the calculation the message remains, but using the "Evaluate" button will cause it to disappear.

The error message does not appear to actually be a result of the calculation, as using the Let statement in scripts and other calculations never yields the message as a result, it appears confined just to the data viewer, which is actually quite a nice place for it to stay.

You can actually get the messages to appear by simply returning blank:

 To make good use of it in practice I have created a simple custom function called "SQL" which simply takes the query as input, and outputs blank if the query yields a question mark (otherwise the query result is outputted).


What types of error messages can it return?

Again I have not gone into huge testing here (hopefully the community reading this can do that ;) but I've found a few messages it can return so far. After searching round the internet these may be standard SQL error messages, though not 100% sure on that.  Below are three examples of errors produced so far:

The first one results from an incorrectly named table occurrence. The second is an error in the query syntax - in this case I incorrectly spelt the keyword 'FROM'.

The last example is of a data type mismatch. In this example the field 'type' is a text field, but we are comparing it to a number.


Further testing and discovery

I did some searching round the internet but was unable to find any references to this coming up in the community before. If anyone has come across this peculiarity before I'd be keen to hear it. Also it would be good to know if there is an easier way to extract these messages, and whether they can be obtained by  means other than the data viewer. I look forward to hearing peoples results!


Example File

Sorry, I didn't think there was much worth in producing a demo file for this given it is so simply reproduced and FileMaker Pro Advanced is required for the data viewer.

Something to say? Post a comment...


  • Daniel Wood 31/07/2018 10:40am (3 months ago)

    hi Gazali,

    I'm not sure if you are getting executeSQL function in FileMaker confused with SQL in regards to ESS/ODBC integration? The ExecuteSQL function is purely used to query table occurrences in FileMaker (which can be FM/ODBC based). I may need more context from you in relation to your situation in order to help further.

  • Gazali 23/07/2018 2:40am (3 months ago)

    I created select statements with union into a table in FM.
    When I run the script , I was asked to create odbc.
    I dont have problem with sql. But how do we apply it in filemaker to view results

  • Jason Lisburn 19/11/2016 2:24pm (2 years ago)

    Nice, thanks very much. My case was such that I had some valid 'null' values (some subsections were untitled and therefore no record is kept) and wanted the blank value returned.

  • Eric Chiquet 31/03/2015 12:20am (4 years ago)

    Very interesting. I had some question marks when I was using ExecuteSQL function in one of my project. it was not clear enough to know where to find how to solve it. If I can find other error stings I will let you know.

    Eric Chiquet

  • Nick Lightbody 26/01/2015 5:42am (4 years ago)

    The SQL error codes are the complete list that FileMaker uses - I am happy to have chat sometime about how to acquire this sort of data.
    Cheers, Nick

  • beverly 25/01/2015 4:58am (4 years ago)

    Steve, if you follow my link to the DoSQL2 wiki, they list several errors found by plug-in developers. I don't know where they got them or where Nick got the "list".

  • Steve Allen 24/01/2015 10:33am (4 years ago)


    Where did you locate the list of error strings? How can I get the full list?

    Steve Allen
    Richard Carlton Consulting

  • DJ 09/01/2015 6:52pm (4 years ago)

    I'm going to follow the comments...

  • Nick Lightbody 09/01/2015 12:11pm (4 years ago)

    Maybe it is just experimental - not QAed - not approved - just under development - waiting for enough market pressure for it to be finished and announced?

    Bev - thanks for the clarification.

    Cheers, Nick

  • Daniel Wood 09/01/2015 9:45am (4 years ago)

    I wonder also why this type of thing is not documented with FM or an actual feature not hidden away. Imagine a new parameter to executeSQL for debugging, such that when used it returns the error message (although FM would never have that type of 'flag' parameter to a function) - perhaps just using EvaluationError and returning the message would be useful. Or even better yet, the calc engine should be able to recognize the first parameter to the function is always going to be an SQL query, so it would be nice if it could auto-format it for you, eg adding in double quotes or single quotes where required, highlighting errors in red etc (one day maybe!)

1 2 3

RSS feed for comments on this page | RSS feed for all comments

Categories(show all)


No Tags