ExecuteSQL Function Notes

by Brian Ginn
My first look and hope for the future.
Presented 5/22/2012 Kentuckiana FileMaker Users Group

How can I learn how FileMaker uses SQL?
Can you use them on a layout?
Which platforms does it work?

ExecuteSQL is new. Execute SQL is old.
You may not have known it but SQL is possibly the very reason we started using FileMaker. Why? All the big players use SQL and the backend to client/server apps and now web apps. So maybe you, like me, chose a very easy to use database FileMaker.

You see SQL statements are text only. They, in there most powerful native form, create tables, delete tables, update tables and fields and join tables. It is very elaborate. There are experts who all they do is SQL dev. This is Oracle, SQL Server, MySQL, PostgreSQL and more. You can learn a lot from the plentiful examples on the web. This all comes from Edgar F. Codd who is the father of modern databases.

FileMaker babies us with graphical table creation ( create statement in SQL command ) and linking with drawing of line ( join SQL statement ).

So why then has FileMaker brought us SQL? FileMaker did it almost ten years ago as I recall with plugins or an optional install ( help me remember ). Even others made plugins just for SQL within FileMaker. FileMaker still has the “Execute SQL” statement for this purpose.  But now they’ve introduced “ExecuteSQL” with no space. Confusing I know.

The ExecuteSQL statement is still text only just as traditional SQL. You get to it in the calc engine within FileMaker. A big change is that it doesn’t allow changing data so no create or modify statements. That mainly leaves the SELECT statement. And it is different from SQL in that it’s a function within FileMaker which means it calls ExecuteSQL ( params ). That parameter area is vastly different than traditional SQL. ( See below for the exact grammar. )

So let’s grow and augment our quick GUI FileMaker with some text SQL! Why am I excited? Because we’ve all faced the time when we needed some quick data on a layout but that called for a brand new join table right? No more. You can show simple data and even join tables without the need for the link! Just join them in the ExecuteSQL function and finished! The only requirement is the Table Occurrence ( TO ) must be present so just put a raw TO on your layout to begin with. Relationship independent! Fewer TO’s!

Can you use them on a layout?
I was a little confused at first by just how to use SQL within FileMaker but then it came to me; you can just use this function in exactly the same way as all other FileMaker functions.

About that FileMaker calc engine… If you look at classic SQL and then look at examples of FileMaker SQL you’ll see it is a lot different. Why? Because it is text within a function call it must be escaped just as any other text you’d put between quotes. Same rules apply.
Here’s the function as defined by FileMaker:
ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments…})

It’s not FileMaker
If you change something something so simple in FileMaker like a field name, your SQL will not work and it won’t warn you either! RESERVE WORDS ( Listed at the end of the ODBC JDBC Help file ) are also a big new worry.  If you reference Container Fields via SQL you can only extract the name of the file unless it contains text! Text is fully accessible! Type mismatch cannot work in SQL. SQL is type match dependent meaning a text field cannot be used against a number field. This is real troublesome.

Which platforms can I use this?
FileMaker Pro 12 client, Advanced and Server. Most surprising to me is even FileMaker Go 12 for iPad and FileMaker Go 12 for iPhone both work!

Simple Example
Setup: Open and populate the “Contacts” starter solution.
Using FileMaker Pro Advanced 12, open the Data Viewer and add a new “Watch” expression

Static queries
5/11/2012 – My first SQL statement in FMP12 from the starter “Contact” after populating with a few records.
ExecuteSQL ( “SELECT * FROM Contacts” ; “¶” ; “” )
[ Results were every single field contents for every field in the table. Too long to show here. And the paragraph symbol was a mistake. ]

My 2nd Query:
ExecuteSQL ( “SELECT \”Last\” FROM Contacts” ; “”  ; “” )

My 3rd Query:
ExecuteSQL ( “SELECT \”First\” FROM Contacts” ; “”  ; “,” ) William,Mary,Elvis,Brian,Taylor,Edgar

My 4th Query:
ExecuteSQL ( “SELECT \”First\” FROM Contacts” ; “”  ; “ ” )
William Mary Elvis Brian Taylor Edgar

My 5th:
ExecuteSQL ( “SELECT \”First\” ,\”Last\” FROM Contacts” ; ” ”  ; “,” )
William Gates,Mary Sue,Elvis Presley,Brian Williams,Taylor Swift,Edgar Codd

ExecuteSQL ( “SELECT \”First\” ,\”Last\” FROM Contacts” ; ” ”  ; “, ” )
William Gates, Mary Sue, Elvis Presley, Brian Williams, Taylor Swift, Edgar Codd

ExecuteSQL ( “SELECT \”First\” ,\”Last\” FROM Contacts” ; ” ”  ; “¶” )
William Gates
Mary Sue
Elvis Presley
Brian Williams
Taylor Swift
Edgar Codd

Can I put these results on a layout?
Yes. Use a variable with the calc then use a merge field on the layout. All the “FileMaker” ways to use a calc result work.

“WHERE” is case sensitive.
ExecuteSQL ( “SELECT \”First\” ,\”Last\” FROM Contacts WHERE \”Last\” = ‘Gates’” ; ” ”  ; “, ” )

Dynamic Parameters
ExecuteSQL ( “SELECT ? , ? FROM Contacts” ; ” ”  ; “, ” ; Contacts::First ; Contacts::Last )
Taylor Swift, Taylor Swift, Taylor Swift, Taylor Swift, Taylor Swift, Taylor Swift, Taylor Swift, Taylor Swift

How can I learn how FileMaker Pro uses SQL?
FileMaker Pro Help ( in-app )
FileMaker Documentation: Help, ODBC and JDBC guide
SeedCode’s SQL Explorer: http://www.seedcode.com/sqlexplorer
Eden Morris’s SQL Builder: http://fmforums.com/forum/topic/83765-solutions-sql-builder/
Kevin Frank’s blog: http://www.filemakerhacks.com/
Greg Lane http://www.filemakeracademy.com/index.php/12-days-of-filemaker-12-executesql/
Brian’s public bookmarks http://bit.ly/fmp12sql

Leave a Comment

You must be logged in to post a comment.