Unlocking Hidden Error Messages in the ExecuteSQL Function

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.