Unless you are a fan of Steve Ditko’s the Question, debugging FileMaker 12’s ExecuteSQL function is an art in itself. Even using a Custom Function to display the exact error message in the Data Viewer, as with Andries Heylen’s debug function, you may not always get the full story. As MightyData’s Darren Burgess wrote about SQL debugging nightmares, there are many pitfalls for FileMaker developers exploring the new world of SQL from within the calculation dialog.
The first key to SQL peace of mind is a valid SQL statement. Reading Beverly Voth’s excellent “Missing Manual” on the ExecuteSQL function, or even FileMaker’s help page, provides tips and references for checking your SQL statement. However, even a simple “select * from tablename” can result in a question mark if the tablename is a reserved word. As the FileMaker ODBC Guide indicates, there are quite a few reserved words, and these need to be escaped in your query.
Memorizing these reserved words is no easy task. After running into this issue once, I built a small FileMaker solution that’s fairly easy to integrate into a database file, so I can look for any reserved words in tables and fields. This might be the geek equivalent of stupid pet tricks, though it also has the added feature of exposing how to query FileMaker’s internal tables and fields with SQL.
Checking for reserved words with this method requires just one completely portable script. Using the built-in “FileMaker_Tables” and “FileMaker_Fields” meta tables, the queries pull a list of table occurences and the check these against the list from the ODBC Guide.
"Select TableName from FileMaker_Tables"
Checking whether each table exists in the reserved word list is carried out by a PatternCount function. While I initially had this as a Custom Function, I brought it into the single script to make it more portable.
Once the tables are checked the script moves on to the fields, where it checks these in the base table names. First we get a distinct set of the base tables, and then using a sub-select method inside a loop, go through each table and list the fields. Then each field is checked to see if it’s in the reserved word list.
"Select Distinct BaseTableName from FileMaker_Tables" "select count(FieldName) from FileMaker_Fields where TableName = ( SELECT Distinct BaseTableName FROM FileMaker_Tables WHERE BaseTableName = '" & $baseTableName & "')"
I added a visual progress bar from Tim Cimbura, as a large table or database can take a while to check. The results appear in a simple web viewer. As the solution is unlocked you can change this to a table using the virtual list technique, or any other visual method you prefer. To render the progress bar and results just copy the two web viewers to a layout of your choice, along with the button to trigger the script.
I discovered the issue when I had a table called “transaction” which is a reserved word. There are a few words that we might use in tables or fields without thinking they’ll affect development, but if you start using SQL, checking for any reserved words might help at least eliminate this issue from any queries. The sample file has a couple of deliberate reserved words in tables and fields, and one table without any fields.