Door 3 FM.ExecuteFileSQL

Today is the third day. Yesterday, we learned what we can do with FileMaker's internal functions. Today, we want to look at an MBS function that is very similar in structure: the FM.ExecuteFileSQL function.

Let's take a look at our query for all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman. We remember that this was the query:

SELECT Name, Launch 
FROM Movie 
WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'

We now want to execute this query in the MBS function FM.ExecuteFileSQL.

This function requires various parameters. First, we enter the file name, which this function should access. In our case, this is the same file in which we are executing the script, so we can specify Get(FileName) here. Then comes the SQL statement. These are the parameters that must always be specified for the MBS function.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE 
   Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'" ) ]

Optionally, we can also specify the field separators and line separators in this function. Prepared statements are also possible here, and at the end of the parameter chain, you specify the values to be inserted into the query instead of the question marks, separated by semicolons.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE 
   Director=? OR Launch=? AND NOT Director=?";"-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]

But if the function does the same thing as the FileMaker function, why do I need it at all and not just use the existing one? If you only want to run a few SELECT FROM WHERE SQL queries, the FileMaker function is perfectly sufficient. However, the MBS function can not only query values from the database, but also use SQL expressions that change the data structure.

For example, we can insert new records into the database, update them or delete a specific record. We will learn about this and much more in the coming days.

It is also possible to delete an entire table, meaning not just all entries but the entire structure. To do this, use

DROP TABLE TableName

Always exercise extreme caution when performing deletion operations before dismantling your database. With DROP Table, only the table is deleted, not the associated layouts.

I hope to see you again tomorrow to continue exploring the possibilities of SQL.