FileMaker ExecuteSQL – A Speedy Tool in the Developers Toolkit

Document created by jameshea on Aug 29, 2018
Version 1Show Document
  • View in full screen mode

The ExecuteSQL function can have many uses and a good developer can call on them to keep their development time down, code quality up and streamline the relationship graph.

 

 

FileMaker ExecuteSQL – A Speed Tool in the Developers Toolkit

 

By James Hea

Lead Developer, FileMaker Platform

FileMaker 16 Certified

Direct Impact Solutions

 

Are you using FileMaker’s ExecuteSQL function? Not to be mixed up with the Execute SQL script step, the ExecuteSQL function allows you to select data from your FileMaker application for creating reports, auto-entering data, getting data about your system, and more. There are some good reasons to use ExecuteSQL and there are gotchas if you don’t use it correctly. Used correctly it can greatly reduce the complexity of the relationship graph, speed up development time and allow you to easily get at information without creating layouts, complex scripts or adding relationships.

 

A word of caution, however, ExecuteSQL does have some constraints. It is case sensitive when searching for data. It also is not entirely friendly with field or table names that have spaces, start with a number or an underscore or has any high ascii characters such as #$^&. Many, but not all, of these limitations can be overcome by ‘Quoting’ the field or table names. Taking this into account,  ExecuteSQL can be a critical component of your rapid application toolkit.

 

SQL History

So where did SQL come from and why is it in FileMaker? Good questions. SQL stands for ‘Structured Query Language’.  It was originally called SEQUEL for ‘Structured English Query Language’. It was invented by IBM and then modified to work with Relational Database Management Systems by Relational Software Inc. In June, 1979, Oracle released the first commercial version. It has been adopted by almost all major database platforms since then. FileMaker introduced it into version 12. According to FileMaker Help, ExecuteSQL uses only the Select statement to ‘avoid security vulnerabilities through injection attacks’ while at the same time provides those with SQL backgrounds, a powerful way to access data without complicating the relationship graph or adding additional layouts.

 

Select *

ExecuteSQL uses the SQL Select statement to pull data from the table occurences in your relationship graph. The data can be pulled through a variable or a calculation directly. The Select statement is very simple. In fact, I learned my first SQL statement in a bar on a napkin.

 

“SELECT * FROM Table”

 

The Select statement is like Find Mode in FileMaker. We are selecting data based on some criteria from within a table we are searching. The “Select *” (star) statement above is like performing a ‘Show All’ in FileMaker when you’re on a layout with all the fields of the table on it. The “Select *” means find all rows (records) and find all columns (fields) from a particular table. I remember running this the first time. Thankfully it wasn’t on a big table with lots of records and fields!

 

Select [column]

More than likely, you’ll want to call specific data, rather than all of it! Say you’re on a Contacts table. You’ll probably run a query like:

 

“SELECT Name_First, Name_Last FROM Contact”

 

In this example, I am selecting information from two columns (fields), but still from all the records in the Contact table. In the screenshot below, I’ve  performed the ExecuteSQL in the Data Viewer. You can see the results in the Result frame.

 

James_1.png

 

Data Viewer is Your Friend

Performing the ExecuteSQL function in your Data Viewer will provide instant feedback about the validity of your SQL query. You can modify any of your criteria – such as picking different columns, or later on, modifying your search criteria. If you misspell something, or forget something in your query, the Data Viewer will let you know and you can keep trying until you get it right.  If you’ve used Oracles PL/SQL  Tool or Microsoft SQL Server Console, you’ve used similar tools to test your queries.

 

About the ExecuteSQL function

The ExecuteSQL function has three standard arguments or parameters. The first is the actual SQL query which is written in quotes. The second is the column delimiter (the default is a comma). The third is the row delimiter (the default is the carriage return).  Additional parameters can be added if you structure your query to look for variables.

 

About Quoting

The ExecuteSQL statement must be enclosed in  double quotation marks. To reference field or table names that do not conform to the standard (i.e. have spaces, high ascii characters, etc.) you will need to insert in the statement, the Quote function to allow FileMaker to process the query. For instance, Quote("Member ID").

 

When called through a calculation, you can decide whether to use a local variable ($) for use in a script or a global variable ($$) perhaps for use in another script or a virtual list. Virtual Lists, by the way, are tables and records that use data that is stored in a variable as opposed to data that is stored in the tables themselves. This makes them very powerful for reusing. For example, you could use a Select statement to create a report based on let’s say 5 fields in one table. But you could just as easily use it to create a report based on 5 fields from another table.

 

SELECT [column] FROM [table] WHERE

This is the SQL query you will use most often. Basically it is a find command where all your search criteria is after the WHERE clause. For example, WHERE firstName = ‘John’ would be where you are searching a Contact table for the first name of someone named John. You may want to get more information, perhaps their last name, address, phone number, etc, but your search criteria is their first name is ‘John’. Obviously you can create much more sophisticated search criteria as well.

 

“SELECT Name_First FROM Contact WHERE Name_First LIKE ?”

 

James_2.png

 

In the above query, we are using the additional parameter indicated by the question mark (?). This parameter means that you can add an additional argument to the function which in the above case is a literal string ‘John’. However, if I entered a lowercase ‘john’. I would not get any results. See below.

 

James_3.png

 

To avoid this problem, where the search criteria must match the case of the data, we modify the SQL statement slightly to ensure that no matter how we enter our data we always get a result.

 

“SELECT Name_First FROM Contact WHERE Upper(Name_First) LIKE ?”

 

James_4.png

 

NOTE: We use the Upper function to make the criteria and the variable in the same case. The result is in the actual case of the data. (Upper vs Proper).

 

Quick Wins with ExecuteSQL

There are some places you can use ExecuteSQL to get some quick wins – such as auto-entering data from a settings table.

 

“SELECT defaultProvince FROM SETTINGS”

 

In this example, you don’t even need the ‘WHERE’ clause since the SETTINGS table has only one record. A super quick way to get the default value in your field without creating a relationship to the SETTINGS table.

 

James_5.png

 

Two Examples – When to Use and When Not to Use ExecuteSQL

In one of our database solutions, I decided to replace a set of relationships with a filtered portal (which wasn’t working too well) with a SQL query. The SQL statement searched through 1.5 million records and returned the results and comparisons in less than 2 seconds. A subsequent query returned the results instantaneously and more importantly - accurately. The results were displayed in a virtual list.

 

About Images

You can do the same with setting logos, images, etc. into global fields from a graphics table without creating relationships but you will need to have a calculation to encode/decode the base64 data. Using the ExecuteSQL function, Select the base64 value in your SQL query. Insert the value into field referenced by a base64 decode calculation to display the image.

 

However, in another situation, in the same solution, an ExecuteSQL function was used in the start up script which was searching through a table of only 17 thousand records. It was taking at least 30 seconds to resolve, which on start up can feel like an eternity! I replaced that ExecuteSQL function with a standard FileMaker Find which produced the result in just a couple of seconds. Let’s take a look at the differences and why one would be better than the other in certain situations.

 

Superfast

I liked this solution because I was able to write it quite quickly and it really executed the SQL query quickly. However, I did have to add a Virtual List table to store and display my results on a layout that made sense to the user.

 

As you can see in the table below, there are over 1.5 million records.

 

James_6.png

 

The requirement was to find which productions Member A  and Member B of in a union organization database for the movie industry worked on together. It starts with a simple query:

 

“SELECT Production_Title, PeriodEnding, ProductionType, ProductionYear,

DistrictCouncil FROM RemittancesItems WHERE MemberID =” & ~memberA

 

Where ~memberA is a variable that contains the MemberID of the person I wish to compare.

 

I can store this result in a variable called $$MemberA. In the same script a similar query for Member B is performed to compare the results.

 

“SELECT Production_Title, PeriodEnding, ProductionType, ProductionYear,

DistrictCouncil FROM RemittancesItems WHERE MemberID =” & ~memberB

 

I can store this result in a variable called $$MemberB. Then if I set a variable called $$Compare, I use the formula:

 

FilterValues( $$MemberA ; $$MemberB)

 

And I get the list of productions that they’ve worked on together. The script to perform these queries runs initially in about a second and half. The subsequent queries run in less than a second.

 

So this was a great place to use this approach and ExecuteSQL served us very well.

 

Superslow

There was another place that ExecuteSQL was being used but it was super slow. (OK, maybe 30 seconds is livable but we removed the function from the start up since users were complaining.) In this case, the culprit may have been an ‘unindexed’ field in a MySQL datasource which is being queried by ExecuteSQL. Similarly, a SQL Query of an unstored calculation would also be slower.

 

I reduced the original query – which may have taken even longer – to a simple

 

“SELECT ID FROM Web_Log”

 

This still took 27 seconds to run with the assumption that the ID is fully indexed. When I reverted this to a Find function, the search only took 2 seconds.  See the Find script below:

 

James_7.png

 

Test, test, test

The only way to be certain that one approach is better than the other is to test, test, test. By simplifying your ExecuteSQL queries, you can identify bottlenecks. It’s interesting that the issue I had was with an External SQL Source (ESS) which I would have assumed was optimized for performance. On the other hand, using ExecuteSQL for some reports, data lookups and setting variables on start up seemed to find a sweet spot in terms of time to develop and performance. Keeping the relationship graph simple and lean is always desirable and developing solutions more quickly and robustly, is certainly a worthy goal. ExecuteSQL is a very handy tool to have in your toolkit. Get to know it.

 

Indirection

ExecuteSQL queries are written generally as literal strings, meaning the name of the fields and tables are within quotes of the Select statement and not identified by the calculation engine as the actual fields and tables. So when you change the name of a field or a table, it may result in the function failing. To avoid this issue, there are custom functions that are available to ‘wrap’ your fields and table names around. These functions will resolve the correct name to ensure that the ExecuteSQL function works, even if the name of a field or table has been changed.

 

----------------------------------------------

 

About James Hea

James is one of the Lead Developers for the FileMaker Platform at Direct Impact Solutions in Canada, an FBA Platinum Partner, FBA Trainer and FBA Reseller. He has worked with the FileMaker Platform since version 2.0 and with ODBC and SQL since 1999. He lives in Toronto with his wife Lynne and near his two daughters and two grandchildren. He has been a speaker at DevCon in 2015, 2017 and coming up in 2018.

13 people found this helpful

Attachments

    Outcomes