Advantages of ExecuteSQL + Get Clear Error Messages with ExecuteSQLe

John Newhoff November 7, 2024 Comparison, DataTables, FileMaker, FileMaker 2024, MySQL, SQL, Tips Comments (2)

If you’ve been frustrated with ExecuteSQL’s cryptic “?” symbol, ExecuteSQLe’s feedback is a welcome change.

John Newhoff

FileMaker’s ExecuteSQL function has been available for quite some time and is a powerful tool in your developer’s tool kit. While FileMaker’s native relational model is simple and intuitive, the ExecuteSQL function introduces an additional level of flexibility, enabling developers to access data across tables without creating extra table occurrences or relationships. This ability is especially useful in complex databases where managing the complexity of the relationship graph is essential.

With ExecuteSQL, you can access data dynamically, make reporting more efficient, and simplify your graph. But like any powerful tool, it comes with a few quirks that we’ll identify here. We’ll also discuss two custom functions we use at Portage Bay that make queries more resilient against schema changes.

What’s New with ExecuteSQLe in FileMaker 21.1?

The recent release of FileMaker 21.1 represents a significant enhancement to SQL functionality with the new ExecuteSQLe function. One of the biggest limitations has been addressed – error reporting. Unlike ExecuteSQL, ExecuteSQLe provides the return of clear error messages, enabling developers to troubleshoot queries much more effectively. If you’ve been frustrated with ExecuteSQL’s cryptic “?” symbol, ExecuteSQLe’s feedback is a welcome change.

Advantages of Using ExecuteSQL

  • Flexible data querying and simplified graph
  • Enhanced reporting without extra layouts or schema complexity
  • Access to FileMaker metadata
  • Server-side efficiency with PSoS

1. Flexible Data Querying and Simplified Graph

One of the primary benefits of ExecuteSQL is that it allows you to query data from any table in your FileMaker database without requiring a direct relationship in the relationship graph. This is a significant advantage for complex systems where additional table occurrences can clutter the graph and create dependencies. With ExecuteSQL, developers can keep the graph clean and manageable while accessing necessary data dynamically.

2. Enhanced Reporting Without Extra Layouts or Schema Complexity

ExecuteSQL offers a powerful approach to reporting. While traditional FileMaker setups often require dedicated layouts or additional relationships to pull together specific data, ExecuteSQL creates ad-hoc reports with a single SQL query. Gather the data you need without building extra structures. ExecuteSQL works especially well with Virtual List reporting to enable complex reporting with limited schema complexity. In large solutions, cutting down on layout clutter and reducing relationships makes everything faster and easier to manage. 

3. Access to FileMaker MetaData

By using ExecuteSQL, you have access to metadata regarding your FileMaker solution, all of the table names and field names in your solution are available to you with simple SQL queries (See Claris documentation). We wrote about one use of this metadata in a blog post about synching to a MySQL database from FileMaker. FileMaker offers some functions for getting similar data, but again, the SQL queries allow you to access this information without creating any layouts and regardless of context.

Text description of FileMaker system tables, from Claris help documentation, describing format, parameters, and data type returned.

4. Server-Side Efficiency with PSoS

ExecuteSQL is especially powerful when combined with Perform Script on Server (PSoS, an approach we often implement here at Portage Bay to improve performance. As we’ve covered, running ExecuteSQL via PSoS can reduce client-side load and improve responsiveness. As always, care should be taken when pushing scripts to the server. Any bugs or overuse of server resources won’t just affect one user, it will affect all.

Text description of Perform Script on Server function, from Claris help documentation, describing format, parameters, and data type returned.

Key Considerations and Cautions When Using ExecuteSQL

  • ExecuteSQL offers a lot of power, but it also has nuances. Here are a few things to be mindful of:
  • Error handling with the new ExecuteSQLe function
  • Static references to tables and fields
  • Performance issues with complex queries or queries on large tables
  • FileMaker’s unique SQL implementation

1. Error Handling with the New ExecuteSQLe Function

The new ExecuteSQLe function resolves the error handling problems experienced with the old ExecuteSQL function. I doubt that the old function will be used any longer, other than for backward compatibility. For reference, and for people new to SQL in FileMaker, FileMaker’s old  ExecuteSQL function lacks detailed error reporting.

If an error occurs in the query, it simply returns a question mark (“?”), making it difficult to troubleshoot. Unlike standard FileMaker scripts, which provide clear error messages, ExecuteSQL requires more detective work when something goes awry. 

Whether using the new ExecuteSQLe or the older function, testing your queries with a tool like RazorSQL for making direct queries to the FileMaker Server ODBC engine can really simplify and help you test and fine-tune your SQL queries outside the FileMaker calculation dialog.

Text description of ExecuteSQLe function, from Claris help documentation, describing format, parameters, and data type returned.

2. Static References to Tables and Fields

ExecuteSQL requires text references to table and field names, which means that if any table or field is renamed, the query will break. This static referencing can be a headache when schema changes are necessary, as each query must be updated manually.

We use two custom functions at Portage Bay – GFN (for fields) and GTN (for tables) – to create SQL-safe dynamic referencing. Which helps to reduce maintenance time and query fragility. See below for details on these custom functions.

3. Performance Issues with Complex Queries or Queries on Large Tables

ExecuteSQL queries are efficient for many needs, but care must be taken. FileMaker pulls the data over to the client to perform the query, so queries on large tables can be a significant performance problem. Likewise, more complex queries involving multiple joins can slow down performance.

When considering ExecuteSQL it is important to think about the size of the dataset being queried and to consider server-side processing with PSoS, as noted earlier.

Graphic made to look like a posted street sign, which says slow turtle crossing, with the word turtle in red. The edges look like slightly rusted metal. The color image in the middle is of a tortoise walking forward.

4. FileMaker’s Unique SQL Implementation

ExecuteSQL in FileMaker doesn’t support the entire SQL standard, and some SQL functions used in other systems may not be available. The most important limitation to be aware of is that ExectueSQL only supports the SQL Select statement. You cannot use the Insert, Update, or Delete statements with ExecuteSQL. See the Claris help documentation for more details.

Multiple third-party plugins are available which give you access to Insert/Update/Delete functionality. Understanding FileMaker’s SQL implementation and limitations will save valuable time and potential frustration.

Using RazorSQL or another SQL testing tool to validate queries beforehand can help avoid syntax and compatibility issues before deploying them in FileMaker. 

“You can write complex SQL queries in other SQL implementations that will likely not work in FileMaker”

Custom Functions for SQL-Safe Dynamic Referencing: GFN and GTN

Representation of an SQL function.
You can write complex SQL queries in other SQL implementations that will likely not work in FileMaker

Two Custom Functions We Rely on to Make Queries More Resilient

To avoid issues with static referencing, we rely on two simple custom functions at Portage Bay: GFN (GetFieldName) and GTN (GetTableName).

These functions help maintain query integrity when tables or fields are renamed, reducing maintenance time and making your ExecuteSQL queries more resilient. Here are the definitions, from the original 2012 FileMaker Hacks blog post where we found them.

By using GFN and GTN, your ExecuteSQL queries will automatically adjust to schema changes, saving time and reducing the risk of broken queries.

Custom Function: GFN

Accepts Parameter: field

/*    GFN [for GetFieldName] returns only the name of the field, without the relationship portion, as opposed to GetFieldName() which also returns the relationship. This is to be used in SQL queries to protect the field references from breaking if the field is renamed.

The Quote() function "escapes" problematic characters and SQL reserved words, if any -- otherwise, no harm done.   */

Let ( [ 
a = GetFieldName ( field ) ; 
b = Substitute ( a ; "::" ; ¶ ) 
] ; 

   Quote ( GetValue ( b ; 2 ) )

)   //   end let
```

Custom Function: GTN

Accepts Parameter: field

/*   GTN [for GetTableName] returns only the name of the relationship, i.e., the table occurrence (TO) name, as opposed to GetFieldName() which returns the relationship + the field name. This is to be used in SQL queries to protect the TO references from breaking if the TO is renamed.

The Quote() function "escapes" problematic characters and SQL reserved words, if any -- otherwise, no harm done.   */

Let ( [ 
a = GetFieldName ( field ) ; 
b = Substitute ( a ; "::" ; ¶ ) 
] ; 

   Quote ( GetValue ( b ; 1 ) )

)   //   end let
```

Final Thoughts

ExecuteSQL is a powerful tool for FileMaker developers, offering enhanced data flexibility, simplified reporting, and reduced dependency on the relationship graph. However, it is essential to stay mindful of its limitations, such as limited error handling and static references. Custom functions like GFN and GTN can make ExecuteSQL queries more resilient, and using PSoS can offload processing to the server for more responsive performance.

Now with FileMaker 21.1’s new ExecuteSQLe function, you have even more options for efficient SQL-driven solutions. ExecuteSQLe improves error handling, making it a compelling option for developers looking to build robust, data-centric solutions in FileMaker. 

Please feel free to contact us with any questions you have or to share your experience with custom functions. We’re always eager to discuss, assist, and learn more!

FAQ

How do I use the ExecuteSQL function in FileMaker?

To use ExecuteSQL, use the syntax:

ExecuteSQL ( “SELECT column FROM table WHERE condition” ; separator ; row separator )

It returns data from a table based on the SQL query you provide. You can use it in calculations, scripts, or custom functions.

What does ExecuteSQL return if no records are found?

If no records are found, ExecuteSQL returns an empty result (“”), not an error. If the query returns multiple rows and you’re using a single result, it will return only the first row.

Can I use ExecuteSQL for multi-table queries?

Yes, ExecuteSQL supports multi-table queries using JOIN operations. For example:

ExecuteSQL ( “SELECT table1.column FROM table1 JOIN table2 ON table1.id = table2.id WHERE table2.condition” )

Ensure proper SQL join syntax for correct results.

Can ExecuteSQL be used to update data in FileMaker?

No, ExecuteSQL is a read-only function. It can retrieve data, but cannot modify, update, or delete records. For data manipulation, you must use FileMaker’s native scripting commands like Set Field, Replace Field Contents, or ExecuteSQL in conjunction with other FileMaker script steps.

How do I handle special characters in ExecuteSQL queries?

To handle special characters, ensure you properly escape single quotes by doubling them (e.g., ‘O”Reilly’). For other special characters, you might need to use specific encoding or consider using FileMaker’s Quote() function for fields that contain special characters.

What is the performance impact of using ExecuteSQL?

ExecuteSQL performance can be impacted by complex queries, large datasets, or inefficient joins. To optimize performance, ensure indexes on fields involved in joins or where conditions, reduce unnecessary complexity in queries and minimize the number of calls to ExecuteSQL in scripts or calculations.

How do I return multiple values with ExecuteSQL?

To return multiple values, ExecuteSQL will return all matching rows, separated by a specified row separator. For example:

ExecuteSQL ( “SELECT name FROM users WHERE active = 1” ; “,” ; ¶ )

This will return a list of names, separated by commas, with each name on a new line.

Can I use ExecuteSQL in a calculation field?

Yes, ExecuteSQL can be used in calculation fields, but keep in mind that its performance can be slower in complex calculations. It’s best suited for retrieving single values or lists from related tables, not for frequent or large queries in calculation fields.

Can ExecuteSQL be used for summary data (e.g., COUNT, SUM)?

Yes, ExecuteSQL can handle aggregate functions such as COUNT(), SUM(), AVG(), etc. For example:

ExecuteSQL ( “SELECT COUNT(*) FROM table WHERE condition” )

This allows you to retrieve summary data directly without needing to use FileMaker’s summary fields.

What happens if I write an invalid SQL query in ExecuteSQL?

If the query is invalid, ExecuteSQL returns an empty result (“”). However, it does not generate an error message. You should validate your SQL queries and test them in FileMaker’s Data Viewer or a more SQL-friendly tool to ensure correct syntax.

About the Author

John Newhoff’s business background and years of database design, computer, and network configuration, and troubleshooting experience allow him to see beyond the immediate problem to the long-term solution. John is the Senior Partner of Portage Bay, our lead 4th Dimension developer, and creator of sophisticated cross-platform solutions.

2 thoughts on “Advantages of ExecuteSQL + Get Clear Error Messages with ExecuteSQLe”

Leave a Reply

Your email address will not be published. Required fields are marked *