Door 4 FM.SQL.Execute and life in the matrix

Yesterday, we learned about an SQL function from MBS. Just like FileMaker's own function, it returned text that we could then work with. This time, we want to take a look at the FM.SQL.Execute function. Instead of returning text output, this function creates a reference. A reference is a reference to the working memory in which the result of the function is temporarily stored. This allows you to use the result returned by the SQL query with other cool MBS functions, which you will learn about later in this calendar. The advantage of having a reference instead of text is that the text does not have to be reloaded into the working memory every time we want to work with the result. The result with the reference is simply stored in the working memory, which saves us a lot of time when dealing with large amounts of data. This also gives us the option of running through the result (in another door) or selecting a different output type. We will output the result as text as well as a matrix. The FM.SQL.Text function is available for output as text. Let's take a look at such a query on our database. Here, we want to search for all movies made before the year 2000. In the FM.SQL.Execute function, we first specify the database on which we want to perform our operations.If we leave this parameter blank, we do not limit the query to one database. Instead, we keep the option open for multiple databases. In our case, it is clear which one we want, and we specify it. Then the SQL statement follows. The result of this function, our reference, is then stored in our variable $SQLRes. We then use this reference as the first parameter in our next function, FM.SQL.Text. Then we can determine the range of data to be displayed. We have parameters for the first and last row and the first and last column. For now it is sufficient to leave the parameters empty, because then the entire result set will be displayed. Two further parameters then stand for the row separator and field separator.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";"| - |") ]

The result looks like this:

There is also a special feature to note for line and field separators. If you want to have line and field separators that are longer than one character, you cannot use the FM.ExecuteFileSQL function from yesterday's door, because only one character is allowed here. If you enter multiple characters, only the first character will be used and the rest will be ignored.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000"; "| - /";"¶¶" ) ]

If you need multiple characters, you should also select the FM.SQL.Execute function in combination with the FM.SQL.Text function.

As mentioned above, the result can be returned not only as text, but also as a matrix. This matrix can then be used with the matrix functions of the MBS FileMaker Plugin. With the FM.SQL.ToMatrix function, we convert an SQL reference into a matrix reference. This allows us to first determine the result as a matrix reference and then return an HTML to the result, which we can then display in a web viewer.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $Matrix ; Value: MBS("FM.SQL.ToMatrix"; $SQLRes) ]
Set Variable [ $HTML ; Value: MBS("Matrix.HTML"; $Matrix ) ]
Go to Layout [ “WebViewer” (Christmas2025 Copy) ; Animation: None ]
Set Web Viewer [ Object Name: "web" ; URL: "data:text/html;charset=utf-8," & $html ]

There's one thing we shouldn't forget when working with references. When we don't need the reference anymore, we have to release it so we don't overfill our memory. In the SQL area, we have two suitable functions for this: FM.SQL.Release and FM.SQL.ReleaseAll. With the FM.SQL.Release function, we can release a specific reference, which we then specify as a parameter. FM.SQL.ReleaseAll releases all SQL references in memory at the same time. Since you also have to work with references in the matrix, you must ensure that the references are released here as well.

Set Variable [ $r ; Value: MBS("Matrix.ReleaseAll") ]
Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $SQLRes) ]

That brings us to the end for today. See you tomorrow for the fifth door.