CF for Returning ExecuteSQL results as JSON

TLDR:

This post shares a small set of custom functions which make it easy to return results from ExecuteSQL as well-formed JSON.


Some Details:

  • The functions require v.16 of FMP or later
  • The functions are designed with performance in mind, and do not involve iterating over every row in the SQL result set.
  • The functions use only the basic FileMaker calculation toolset, without any use of JavaScript, WebViewers, Plugins, etc. (not that I have anything against any of those).
  • The sample file comes with a set of examples for both basic and advanced usage
  • The sample file also contains a set of test cases which I have used over the years to ensure quality.
  • The examples are crafted both to illustrate common usage, as well as to encourage readable code structure.

Some Background:

Several years ago, both for development use, and also as a hobby, I wrote a variety of custom functions that were related to using JSON within FileMaker. Of all of these functions, the ones that I still use to this day, even on a weekly basis, are the ones shared here in this post, which allow for returning ExecuteSQL results as JSON.

I have not attempted to aggressively share these functions, because, under the hood, the code is somewhat involved, and I have wanted to respect the general sentiment that I think most of us have, which is that we prefer to only utilize code which we can easily grok and understand. Moreover, with the recent addition of the Execute Data API script step, FileMaker devs now have another easy way to retrieve table data as JSON, and with that, I imagined there would be a decreased demand for something like these functions.

Nonetheless, I periodically receive requests for these functions, either from YouTube viewers, or members of the Claris-based forum, or even some who have contacted me here at The Soup. Thus, this morning I decided to share the functions here at The Soup, having just recently noticed that the Resources section of this site might be an appropriate place to share.

These functions are now 3-4 years old, and have benefited from the suggestions, input, and occasional bug reports from friends and colleagues, as well as my own use. I hope others may enjoy them. They are free to use and distribute. I have done my best to keep them at a tip-top high level of quality, but even so, they come with no guarantees.

The basic ideas behind how these functions work/behave are documented in a couple of YouTube videos:

Please feel free to reach out to me about them, should you have any questions, etc.


Finally:

Below are a few screenshots of the sample file, followed by the sample file, itself.

Thanks for reading, and best regards.

-Steve


11 Likes

Thanks for sharing Steve - brilliant stuff!

And much more handy then the FM19 script step ExecuteFMDataAPI (which cannot be explored in the Data Viewer ..)

1 Like

Thanks @FileKraft . To be fair, I think Execute Data API is probably faster, but this has always been fast enough for my needs, as I don't select huge amounts of data. What I really find convenient about it is the freedom that ExecuteSQL affords from needing a layout.

1 Like

apparently the ExecuteFMDataAPI script step @steve_ssh is not yet matured and reports show not always reliable would be my last choice except if significantly faster. Maybe it has been fixed that it can happen that it won't return the expected found set of implied query?

Also for ExecuteSQL function have there been any performance improvements lately since FMP19? Would be great if the FIRST predicate would lead to faster results and stop as soon as query returns specified first rows ..

Interesting. Have you found that FETCH FIRST hasn't provided a performance gain over the same query without using FETCH FIRST? That would be a shame -- it's something that I have not ever thought to look into, despite the fact that I do use it (FETCH FIRST) from time to time.

AFAIK fetch first was never optimized to stop query after first set received. It would great if that has been changed recently. Needs to be confirmed or tested.
(Couldn't find the discussion in the Claris Community yet)

1 Like