ExecuteSQL Function – FileMaker 12 Web Series Follow Up Q&A

I was overwhelmed by the positive response to my 12 Days of FileMaker 12 – ExecuteSQL webinar. We received a lot of great questions during the session. I’ve included answers below for all of the questions we weren’t able to address during the webinar. If you still have a question, feel free to contact us. Thanks to everyone who participated live or via the recording. If you missed it, you can still catch the recording at FM Academy or download the example file. Q: Is it possible to use the SQL CASE function? A: Yes, here’s an example of the syntax: SELECT first_name, CASE WHEN s=’M’ THEN ‘Boy’ WHEN s=’F’ THEN ‘Girl’ ELSE ‘Unknown’ END FROM contacts Q: Can you sort the data? A: Yes, the “ORDER BY” clause can be used to specify the sort order for the records. The example below would return all of the records from the contacts table sorted by last_name and then first_name: SELECT * FROM contacts ORDER BY last_name, first_name Q: If FileMaker only supports SELECT, what is the actual vulnerability if a user attempts to use DROP, as in your charming cartoon example? A: While it isn’t possible to DROP a table using ExecuteSQL, the function is still vulernable to certain SQL injection attacks if the developer chooses to include user-supplied text in a calculated SQL query. I covered a few examples of this in the webinar. Q: …how about using single quotes to get globals? A: Global fields can be used in a query. Q: If a field name does not normally require double quotes around it, will putting double quotes around it alter the way ExecuteSQL functions? A: It’s perfectly fine to include double quotes around any table or field name. Just remember to escape the double quotes if you’re specifying them in a calculation. Q: Is there an example of how to parse the data and create records from it? A: Not exactly, but the example file includes a simple script that parses the data from an ExecuteSQL function and creates find requests. The script is called “Find contacts ( id list )”. Q: ExecuteSQL allows me to do many of the same types of things I can do in FMP without ExecuteSQL. Why would I choose to use ExecuteSQL instead? A: The ExecuteSQL function is just another tool. It doesn’t let us build anything that isn’t possible without it. However, it does make some things much more convenient. For example, gathering data from different contexts for a chart or web viewer used to require either a script or additional utility relationships. The ExecuteSQL function is a convenient way to access data from any context. Q: Example 15: If searching for created (date) = ’31/12/2010′ is it using system formats or does the date need to always be ‘yy/mm/dd’? A: As far as I know, the only valid syntaxes for specifying a date constant are “DATE ‘2012-12-25′” and “’12/25/2012′”. Q: Can you select data from an external FMP file, e.g. if you’re using the separation model? Does a Table Occurrence need to exist in the current file (even though it’s not related)? Q: Does this work with external files, or local tables only? A: Yes. The SQL query references Table Occurrence names rather than the base table names. Any valid Table Occurrence in the current file can be used in the query, even if it points to an external FileMaker table or an ESS table. Q: Is ‘curdate’ the FMP terminal current date or the server current date (if they happen to be different)? A: The SQL functions are evaluated by the FileMaker client. Q: How much of the query processing is client side or server side? Q: How is the performance of SQL native command, it’s faster than SQL plugins? Q: How fast is ExecuteSQL on very large data sets? For example we have a file with 29 million records which has only one indexed field and the rest are unstored calculations. How fast will it be compared to a standard FMP ‘find’ for both the indexed and the unstored fields? A: Unfortunately, most of the work is done on the client side. I have not found the ExecuteSQL function to be the solution for any performance-related problems. Q: In what sort order is data returned? A: Unless an ORDER BY clause is specified, the results would be in record creation order. Q: How would a user perform a multi-criteria find. ie. all customer in Georgia that purchase product X in 2010? A: The user would likely need to join three tables (customer, order, order_item) together and then use a WHERE clause like this: WHERE customer.state = ‘GA’ AND YEAR(“order”.”date”) = 2010 AND order_item.”id” = “X” Q: To get unique values, what’s the best way (more performance) doing custom functions or SQL? A: I have not benchmarked the various options for generating a list of unique values. Q: Is there any reason to have just the ‘select’ command, and not ‘update’ or other SQL commands? A: I don’t believe I’ve seen an official explanation. My speculation is that it’s to avoid a deadlock where two or more actions are each waiting for the other to finish, and thus neither ever does. Q: How do paragraphs of text get returned via the ExecuteSQL function? How do you handle parsing out large text blocks? A: Paragraphs of text in FileMaker are usually divided with a carriage return, which is Char(13). This is not modified by the ExecuteSQL function. If your data could contain carriage returns, you probably won’t want to use a carriage return as the field or row separator in the ExecuteSQL function. Choose separators that won’t appear in your data. Some of the non-printing ASCII characters like Char(31) and Char(30) can be good choices. You can also use multiple characters for field and row separators. Q: How well done is SQL support? Can we do unions and sets instructions like in Oracle SQL? A: The ExecuteSQL function supports UNION and UNION ALL. It does not support other set operators such as INTERSECT or EXCEPT. Q: Does ExecuteSQL honor the FileMaker security for account field access? A: Yes. Q: What textbook is good for learning basic SQL? A: An excellent free SQL tutorial can be found at http://www.w3schools.com/sql Q: Use of the query is like a regular SQL statement… But the FM SQL statement is written differently. How can I apply your query statements to FM SQL statements? A: All of the examples I showed in the webinar (and many more) are available in this file: ExecuteSQL.fmp12. The file is unlocked and you can see exactly how the SQL queries are used in the ExecuteSQL function. Greg Lane is Vice President of Application Development & FileMaker Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key is an accomplished team of technology consultants who solve business problems. We specialize in the rapid development of custom applications, integrating Macs and PCs in the professional workplace, and providing personalized training. Despite our end-to-end technical skills, we are consultant first and technologist second. We know that you don’t just need technology. You need to know that the technology you choose to deploy will provide the results you desire. Skeleton Key is a Platinum Level FileMaker Business Alliance company, an Authorized FileMaker Trainer, a member of the Apple Consultants Network and a Microsoft Registered Partner.