CF for Returning ExecuteSQL results as JSON

Resources Sample files & code
, ,
1

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

Basic Example
Basic Example1723×1111 120 KB

Richer Example
Richer Example1723×1111 153 KB

Select Single Record
Select Single Record1723×1111 120 KB

12 Likes
2

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
3

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
4

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 ..

5

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.

6

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
7

@steve_ssh , this is a great CF. but can you also do something like Select Distinct....

Thanks,

jason

2 Likes
8

Hi @jasongan ,

Welcome to the Soup! Glad to have you here.

Select Distinct is indeed supported.

In the example file, example #13 illustrates a simple SELECT DISTINCT.

All the best & thank you for the interest/question,

-Steve

select_distinct
select_distinct1184×1045 102 KB

1 Like
9

Hi Steve,
Thanks you very much for your prompt reply. I had your previous demo database
SQL_JSON_Demo_20180331_01.fmp12 and couldn't find solution for Select Distinct. I download your latest demo DB, and refreshed all related custom functions, and "Distinct" function works right away. That is super helpful.

Jason

1 Like
10

Hi Jason,

Thank you for the feedback on this, and I am glad to know that you have the latest copy of the file. Per your observations, there was no easy support for doing a DISTINCT select in the early version(s) of the file. Fortunately, a couple of friends ( Jared Hague and @KyleWilliams ) each individually mentioned to me that they felt it would be a good improvement to add such support, and so it came to be. Kyle additionally provided helpful feedback as to how to add in the feature in a way that would feel intuitive to the consuming dev. I am grateful to both of them that this feature is now supported.

All the best,

-Steve

1 Like
11

In video #1 you discuss escaping characters by substituting them with a reserved string which will not appear in the text.

Don't create your reserved string from characters which can actually be entered into a text field.

There are plenty of characters that are not entered into text fields. When control characters are typed, they aren't stored in the field because they are interpreted and an action occurs.

  • Backspace, Char(8)
  • Cancel, Char(24)
  • Escape, Char(27)
  • Delete, Char(127)

You can safely use any of these control characters as a reserved string in string substitutions knowing that they do not appear in text fields.

3 Likes
12

Thanks @Malcolm

I think that you are correct, and I appreciate you suggesting some characters which you know are safe. Perhaps the next free weekend I have, I will rev the CF and repost to follow your advice.

There was a reason which caused me to avoid such characters in the first place (as I did think to use other code points), but I think it would be fair to categorize my reservations as either superstition-based, or out of date. It has to do with some ill-effects that I observed with how FMP v.11 handled non-printable (not necessarily control chars -- I can not remember for sure) chars which had unfortunately been used in script names of a solution to which I was assigned. As I recall, those chars played havoc with DDR generation. Now, while I think it was a poor idea to test the boundaries of FMP by using such chars in script names, it did make me question how well FMP might handle those chars elsewhere, and this stuck with me. Not necessarily a rational response, but perhaps an understandable one, much in the same way as there is a flavor of frozen yogurt which, to this day, I will never eat, ever since having had a terrible bout with food poisoning associated with that flavor nearly 40 years ago.

The above is not to make excuses, but I thought an explanation for the decision could not hurt. There was also another solution that I once worked on where the original developer considered field names comprised of sequences of invisible characters to be fair game for use as "spacer" fields in the field management UI. That was another experience where the aggravation related to dealing with that particular practice also somewhat played into my decision to stick to visible chars.

But again, I come back to saying that I do believe you are correct. In the same way that I know someone would be correct if they told me that it really is ok to eat raspberry frozen yogurt. I know it's true. It just takes a little effort on my part to get over it.

Thanks, Malcolm.

-Steve

2 Likes
13

Way back when I first started I was shown a method identical to yours. I thought it was inherently unsafe so I improved it by having an array of reserved strings to choose from. I would search the data for the reserved string and only used it if the pattern count was zero. When v10 came along with the Char() function I started using a non-enterable character.

1 Like
14

If anyone uses MBS FileMaker Plugin here, you can use FM.SQL.JSONRecords to get JSON for SQL records.

It will do all the escaping needed and has a few options on how to return containers or times/dates.

With a custom function, it is easy to miss some escaping for the JSON.

1 Like
15

Way back when I first started I was shown a method identical to yours. I thought it was inherently unsafe so I improved it by having an array of reserved strings to choose from. I would search the data for the reserved string and only used it if the pattern count was zero. When v10 came along with the Char() function I started using a non-enterable character.

Again, all great thoughts @malcolm, and pretty much mostly all agreed with. The technique of checking for the existence of the reserved string and then selecting one which is known to be safe based on that inspection is sound, and I use it in other code. The conundrum that I ran into with using that technique with this particular use case is that it would seem to require a separate SELECT of the data without any of the additional injection in order to be able to see what the data contains without any potential confusion collision from the injected strings -- at least, to do make such a determination with 100% certainty.

I was not willing to have the code run the SQL query twice, and so I chose to use a single reserved string. That said, I think there's always a possibility that I might not be seeing for determining the existence of the reserved string in the data with just a single SELECT, and if ever such an option becomes apparent, I would happily employ it.

Returning back to the topic of improving that reserved string:

I definitely think there's room for improvement in the choice of the reserved string, and I am looking forward to the idea of trying out using some control characters in it. I do have some small concern about whether such characters could present a problem. Not with respect to the general FMP calculation engine, which I have found to be rock-solid in handling all ranges of chars in all the core string functions. My concern is whether or not including such characters specifically in a query supplied to ExecuteSQL could have any unexpected consequences. Many, many devs have used the FMP string functions in all manner of ways for multiple decades, and so that feels like very solid territory. In contrast, a smaller portion of developers use the ExecuteSQL function, and probably even fewer use ExecuteSQL with SQL string concatenation, and I imagine even fewer have attempted to include control characters in conjunction with ExecuteSQL in this way. So, I'm looking at the control character option as a solid idea for improvement, but one which must be approached with a degree of understanding that it is new ground, and not yet proven. My degree of concern is not super-high, nor is it non-existent.

It's been a while since I have looked at the code in these functions, but, if it's in my usual style, I probably factored out the reserved string into one or two centralized constant(s) that could be easily swapped out by anyone with sufficient understanding of what they were looking for. I think that what I'd like to do is to post two versions of the sample file: one currently as-is, and the other with a reserved string comprised of control characters. And then, include some discussion about the significance of the reserved string, and a pointer to this exchange.

Thanks again, Malcolm, for continuing to share ideas on the shortcomings of the code, but also for suggesting ways to improve upon these.

2 Likes
16

I would suggest the Byte Order Mark (Unicode 65279) in that it checks all the boxes for an invisible character that is really unlikely to ever actually be in a string. I would also have an aversion to using control codes in text, so the BOM is really handy in that it actually occupies a location on the Unicode charts but doesn't actually do anything in real life.

2 Likes
17

My reason for choosing a control character isn't because it is invisible. It's because a control character doesn't get entered into text by ordinary means. When a user hits any of the keys I listed, the key is interpreted as an instruction to perform an action. The action is performed. The symbol isn't stored in the text.

1 Like
18

I really like the idea of the BOM, too. It's the aspect of being unlikely to be in the data that appeals to me -- that's really the key for what is needed here. Thank you @jfletch . I appreciate the excellent suggestion.

I should mention, too, just to clarify:

The reserved string needn't be limited to a single character. I'd have no issues with a string of length 10, or so, and it could be comprised of a variety of types of characters. As long as it doesn't break anything, and is unlikely to appear within user data, it meets the two requirements that I most care about. (If I had my preference, it would actually include some visible characters, as that can help a little bit with troubleshooting.)

19

Note/Update:

I just took a few moments to run a very simple test to see if the characters that Malcolm and jfletch have suggested would successfully pass through an ExecuteSQL query within a concatenated string, and the results of that simple test indicate that they will work just fine.

This is really wonderful news. Based on this, I am thinking that I will create another build with reserved string which incorporates several of these characters. It might not be this weekend, but hopefully within one or two more weekends.

For a brief while I considered adapting the CFs to where the consuming dev would be required to choose and supply their own reserved string, but I don't really care for that idea much because, not only does it make the CFs more cumbersome to use, it also increases the chance for errors in cases where someone does not really understand the concept/requirement of a solid choice of reserved string.

@Malcolm and @jfletch , I am in the habit of including attributions in the comments of the CFs to credit those (such as yourselves) who have supplied input and ideas which have improved the quality of the functions. If you have any strong feelings one way or the other about being mentioned or not being mentioned in the comments, I hope you will let me know, so that I can accordingly mention your name, or refrain from doing so, per any preference you have.

Thanks!

1 Like
20

For your own sanity, the reserved string should be the same length as the target you are going to replace. You never know when you are going to need to interact with the text. If you need to do any processing based on position, you don't want your text expanding and contracting like a slinky.

3 Likes