Fun and Games with FileMaker’s internal SQL

Recently I’ve spent some time looking at FileMaker’s internal SQL support. In this article I discuss how we can use FileMaker scripting to add and delete tables from a FileMaker database.

A number of plug-ins provide the ability to issue SQL commands against your FileMaker databases from within a FileMaker script. I’ve been using the 2empowerFM SQL Runner plugin from dracoventions. It’s a free plugin that you can download  here

I’ve built a simple sample file that has the ability to alter it’s own structure. You can download the sample file FileMaker SQL DDL.fp7. Have a play around with it and everything should be fairly self evident.

So how does it all work? well it’s not that complex really. The basic SQL syntax to add a table is as follows: CREATE TABLE table_name table_element_list We just need some way of running that command from a FileMaker script and that’s where the plugin comes into play.

Lets look at the following simple example: CREATE TABLE TEST_Table(C1 VARCHAR NOT NULL ,C2 DECIMAL  )
This will create a table in FileMaker called TEST_Table with fields C1 and C2 which are text and number data types. To run the SQL command we pass it as a parameter to the epSQLExecute external function like so: epSQLExecute( CREATE TABLE TEST_Table(C1 VARCHAR NOT NULL ,C2 DECIMAL  ) ;”waitForIdle=’Yes’ “) and hey presto, when you open up Manage Databases you’ve got a new table sitting there.

You’re probably wondering about that waitForIdle=’Yes’ part. The waitForIdle parameter must be set to Yes or FileMaker will freeze. This parameter should only be necessary when you issueSQL that alters the database structure in some way. In my sample file I’ve wrapped the epSQLExecute function in a custom function for situations involving DDL (Data Definition Language) so the developer doesn’t need to rekey it every time.

So now we’ve added a table, how can we delete one? The command DROP TABLE TEST_Table will then delete that database table from the database structure. So it’s simply a matter of executing epSQLExecute( DROP TABLE TEST_Table  ) ;”waitForIdle=’Yes’ “)

A couple of further things to note: You’ll want to take a look at FileMaker’s documentation on the topic. FileMaker’s support for SQL is documented here . If you’re already using another plugin to execute SQL in FileMaker you can easily edit my sample file to work with the different syntax for the other plugins. Adapt the code in the custom functions to work with your chosen plugin.

In future blog postings I’ll be extending this file a bit more and looking at some of the other very useful ways that we can use FileMaker’s internal SQL.

Ian Jempson

Ian has an impressive resumé amongst FileMaker developers and is an internationally recognised authority on all things FileMaker and is frequently invited to speak at industry events. In his 15 years of experience he has developed award-winning custom database systems as well as software products that are in daily use by tens of thousands across industries as diverse as investment banking, recruitment, events management, advertising and education In his role as Development Manager, Ian handles our increasing project management requirements. Certified in FileMaker versions 8 through 12, Ian is also well versed in other database platforms which drives him to constantly push the boundaries of what is possible with FileMaker Pro.

More Posts - Website

1 thought on “Fun and Games with FileMaker’s internal SQL”

  1. The 2empowerFM SQL Runner plugin is great BUT I have found that it does not work with any ESS tables (I have several tables connected to Oracle views). It times out with a 101 error and while solutions are out there on the web for this error, they do not apply for SQL-plugins.

Leave a Reply