Our loyal readers will know that we have published several articles extolling the virtues of the new ExecuteSQL function in FileMaker 12. Well, now it’s time to come clean and tell you the truth about this powerful function and its hidden ability to turn your hair prematurely grey, cause you to lose sleep, or perhaps develop an annoying and embarrassing facial tick.
If you intend to embark on the rewarding path of integrating SQL into your FileMaker development, be warned. And be prepared, as one simple punctuation character, innocent in all other contexts among our many human languages, may lead to premature aging.
? Marks The Spot
This character is the lowly question mark (?), and is the only feedback provided by the FileMaker calculation engine when you write malformed SQL statements within the ExecuteSQL function. SQL queries must be syntactically perfect in order for them to work. When you start writing SQL in FileMaker, you are stepping out of the safety of FileMaker’s tightly controlled programming environment that, for example, does not allow you to specify fields, scripts or other objects that don’t exist in the system.
Here is a short list of syntax errors that will cause your SQL query to return a question mark and for you to lose a few minutes off of your projected lifespan:
- Using one of the 250-odd SQL reserved words as a table or field name
- Using unacceptable characters (like an underscore ‘_’) as the first character of a field name
- Using SQL syntax that is not accepted by the FileMaker SQL API (there are many versions of SQL)
- Improper quoting of elements in the query
- Improper expression of date syntax
Fortunately for you, I have sacrificed some of my naturally colored hair and a few precious minutes off of my lifespan to bring you some useful tips for surviving the FileMaker/SQL learning curve. Most notably, I want to explore how to use the script debugger and data viewer to assist in the debugging of your SQL queries.
SQL for Beginners
First off, this is not going to be an extensive tutorial in using SQL with FileMaker. If you are just getting started, I would recommend the following resources:
- Kevin Frank has a number of blog posts and custom function resources at: www.filemakerhacks.com
- Anders Monsen introduces some SQL ideas in this post.
- W3Schools.com has a decent SQL beginners tutorial available.
- FileMaker’s ODBC/JDBC Guide has useful reference information, including a list of those dreaded SQL reserved words.
How to Debug SQL in FileMaker
Let ( // variables [ type = Q( $type ) ; resetDate = Q( PeopleLayout » People::inquiryResetDate ) ; pID = Q( PeopleLayout » People::id ) ; SQL = " SELECT COUNT ( id ) FROM Conversions WHERE type = " & type & " AND id_Person = " & pID & " AND creationDate >= " & resetDate ] ; // calculation Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 ) //DEBUGGING //type //resetDate //pID //SQL //ExecuteSQL ( SQL ; "" ; "" ) ) // end LET
Let ( // variables [ type = Q( $type ) ; resetDate = Q( PeopleLayout » People::inquiryResetDate ) ; pID = Q( PeopleLayout » People::id ) ;
SQL = " SELECT COUNT ( id ) FROM Conversions WHERE type = " & type & " AND id_Person = " & pID & " AND creationDate >= " & resetDate
// calculation Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 ) //DEBUGGING //type //resetDate //pID //SQL //ExecuteSQL ( SQL ; "" ; "" ) ) // end LET
Debugging in Action
And now the fun can begin! If my ‘Create Conversion’ script is not working, and I suspect a faulty SQL query, I run the script with debugger on. When the debugger runs the step in question, the value for variable $isFirstAfterResetDate will return a “?”:
I then open the script, grab the calculation from the step, and drop it into the data viewer. Since the script is currently running, I am assured that all of the conditions of layout context and field/variable values that may be used in the calculation are in force. I then use the Let function to return the values of the various elements in the calculation by uncommenting them in the data viewer.
Here is the original calculation, returning the result the script is expecting:
We can return one of the Let variables to make sure a valid and properly quoted value exists for that variable:
And here we return the SQL query itself, to ensure that it is properly formed. (Of course the usefulness of this assumes that I know what properly formed SQL statement looks like!):
Once you have started creating SQL queries in FileMaker that actually work, save them in a library. I use Notational Velocity to maintain my library of valid, working SQL queries. Having this as a resource is extremely valuable when it comes time to create a new query of particular type or style that already exists. It is perhaps the most valuable method for debugging SQL – which is simply using proven calculations – and makes it much more likely that your SQL queries will not be buggy and lead to that dreaded, hair-greying, insomnia-producing and lifespan shortening punctuation mark. I wish you the best of luck in the hard work of learning SQL. Post a comment if you have any questions!
Jonathan Fletcher says
You can also use the excellent Developer Assistant from DracoVentions to evaluate the pieces-parts of the calculation without having to take it to the Data Viewer, or even switching up the results portion of the calc. Way simpler.
Darren Burgess says
great tip, Jonathan. Thanks for sharing it here. I have not used that tool yet, but have downloaded a trial
Simon Marshall says
Darren thanks for 2 things firstly a great tip to speed up debugging my ExecuteSQL lines. But more importantly showing me what a powerfull tool the data viewer is.. up till now ive only used it to monitor variables as a script progresses.
Didnt realise you could put in an expression and test it at run time in the right context.
Was hoping this article was related but maybe your expertise can help. I’m accessing an old FileMaker DB with JDBC. I can’t select certain fields because their names include question marks. (Ex: Job complete?). Surrounding with quotes “Job Name” helps with spaces but still fails on “Job Complete?”. Any way to escape this? Thanks.
Kirk Bowman says
Drew – Check out the “odbc_jdbc_guide.pdf” in the documentation folder. The location and exact name will depend on the version of FileMaker you are using.
Of course, the easy option is to rename the fields to exclude spaces and other punctuation characters. Generally, you want to use A-Z, a-z, 0-9 and the underscore.