FM.SQL.Execute
--------------

Execute the SQL Statement against a FileMaker database.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [FM](component_FM.md) [FMSQL](component_FMSQL.md) | [5.1](newinversion51.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "FM.SQL.Execute"; FileName; SQL Statement { ; Params... } ) 

**MBS**( **"FM.SQL.Execute";** /\* Execute the SQL Statement against a FileMaker database. \*/  
**$FileName**; /\* The target database name. Can be empty to not limit query to one database.e.g. "" \*/   
**$SQL Statement**; /\* SQL Statement as a Text string \*/   
**$Params...**) /\* Optional; Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.e.g. 123 \*/ 

### Parameters

| Parameter | Description | Example | Flags |
|---|---|---|---|
| FileName | The target database name. Can be empty to not limit query to one database. | "" |  |
| SQL Statement | SQL Statement as a Text string |  |  |
| Params... | Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. | 123 | Optional |

### Result

Returns reference number or error.

### Description

Execute the SQL Statement against a FileMaker database.   
Returns a SQL Reference, so you can use functions like [FM.SQL.RowCount](FMSQLRowCount.md), [FM.SQL.FieldCount](FMSQLFieldCount.md), [FM.SQL.Field](FMSQLField.md) and [FM.SQL.FieldType](FMSQLFieldType.md) to query result.   
  
Using FM.SQL.Execute avoids converting all result values to text like in [FM.ExecuteSQL](FMExecuteSQL.md) or [FM.ExecuteFileSQL](FMExecuteFileSQL.md) functions. Here you get result set stored in memory and back a reference number, so you can query individual values later. Like getting back container value and using it without just getting the file name as text.  
  
Don't forget to use [FM.SQL.Release](FMSQLRelease.md) to release the result from memory when you are done with it.  
  
This function requires FileMaker 11 or newer. Please use [FM.ExecuteSQL](FMExecuteSQL.md) on FileMaker 10 or older.  
  
This function takes variable number of parameters. Pass as much parameters as needed separated by the semicolon in FileMaker.  
Please repeat Params parameter as often as you need.  
### Examples

Query only first row:

 MBS ( "FM.SQL.Execute"; ""; "SELECT \* FROM ProductImages FETCH FIRST 1 ROWS ONLY")  
SELECT records

 MBS ("FM.SQL.Execute"; ""; "select \* from Contacts")  
Select with parameter:

 MBS ("FM.SQL.Execute"; ""; "select Menge from Boxen where Artikel\_Nr=? ORDER BY Menge DESC"; "ABC123")  
Query some values:

 Set Variable \[$sql ; Value:MBS ( "FM.SQL.Execute"; ""; "select \* from Clients")\]  
If \[MBS ("IsError")\]  
 Show Custom Dialog \["SQL error"; $sql \]  
Else  
 Show Custom Dialog \["SQL result"; "got " &amp; MBS ( "[FM.SQL.RowCount](FMSQLRowCount.md)"; $sql ) &amp;" rows with each " &amp; MBS ( "[FM.SQL.FieldCount](FMSQLFieldCount.md)"; $sql ) &amp; " …"\]  
 Set Variable \[$r ; Value:MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; $sql )\]  
End If  
Query record as JSON:

 Let ( \[  
fields = MBS ( "[FM.QueryFieldsForTableName](FMQueryFieldsForTableName.md)"; "Contacts");  
Records = MBS ( "FM.SQL.Execute"; ""; "SELECT \\"" &amp; substitute(fields; ¶; "\\", \\"") &amp; "\\" FROM \\"Contacts\\" WHERE \\"First\\" = ?"; "Joe");  
  
json = MBS ( "[FM.SQL.JSONRecord](FMSQLJSONRecord.md)"; Records; 0; fields);  
r = MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; Records )\]  
; json )  
Make a JSON query in one Let statment:

 Let (  
 \[  
 $sdat = "10/1/2019 00:00";  
 $edat = "10/10/2019 00:00";  
 sku = "'PARbh50','PARgy50'";  
 sql1 = MBS ("FM.SQL.Execute"; ""; "Select docnum,linetotal,db from \\"MyTable\\" where ItemCode in (" &amp; sku &amp; ") and DocDate between ? and ?"; $sdat ; $edat );  
 json = MBS ( "[FM.SQL.JSONRecords](FMSQLJSONRecords.md)"; sql1; "DocNum¶LineTotal¶DB"; 1);  
 r = MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; sql1 )  
 \] ; json  
)  
Query related records as JSON:

 # get related teams   
Set Variable \[ $r ; Value: MBS ( "FM.SQL.Execute"; Get(FileName); "SELECT \\"UUID\\", \\"ID\\", \\"ID\_Department\\", \\"Team.Name\\", \\"MemberCount\\" FROM \\"Teams\\" WHERE ID\_Department=?"; Department::ID ) \]   
If \[ MBS ("IsError") \]   
 Show Custom Dialog \[ "SQL error" ; $r \]   
Else  
 # fill as json in field  
 Set Field \[ Department::JSON\_Department ; MBS ( "[FM.SQL.JSONRecords](FMSQLJSONRecords.md)"; $r ; "UUID¶ID¶ID\_Department¶Team.Name¶MemberCount") \]   
 Set Variable \[ $e ; Value: MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; $r ) \]   
End If  
Query two JSON arrays, one for group names and ID list:

 Set Variable \[ $sql ; Value: MBS ("FM.SQL.Execute"; ""; "SELECT DISTINCT \\"ID\\", \\"Group\\" FROM \\"Contacts\\" WHERE Length(\\"Group\\") &gt; 0") \]   
If \[ MBS ("IsError") \]   
 Show Custom Dialog \[ "Error" ; $sql \]   
Else  
 Set Variable \[ $IDs ; Value: MBS ("[FM.SQL.JSONColumn](FMSQLJSONColumn.md)"; $SQL ; 0) \]   
 Set Variable \[ $Groups ; Value: MBS ("[FM.SQL.JSONColumn](FMSQLJSONColumn.md)"; $SQL ; 1) \]   
 Set Variable \[ $r ; Value: MBS ("[FM.SQL.Release](FMSQLRelease.md)"; $SQL ) \]   
 Show Custom Dialog \[ "Values" ; $IDs &amp; ¶ &amp; $Groups \]   
End If  
Run query and add constant values:

 Set Variable \[ $Result ; Value: MBS ( "FM.SQL.Execute"; ""; "SELECT Field1, 'Some Text', 1234, Field2 FROM MyTable" ) \]  
Query current record as JSON:

 Let(\[  
// get field list  
fields = MBS ( "[FM.QueryFieldsForTableName](FMQueryFieldsForTableName.md)"; Get(LayoutTableName));  
// query the fields using that fieold list for current record  
sql = MBS ( "FM.SQL.Execute"; Get(FileName); "SELECT \\"" &amp; Substitute(fields; ¶; "\\", \\"") &amp; "\\" FROM \\"" &amp; Get ( LayoutTableName ) &amp; "\\" WHERE RowID = ?"; Get( RecordID ));  
// and query JSON  
json = MBS ( "[FM.SQL.JSONRecord](FMSQLJSONRecord.md)"; sql; 0; fields );  
// free SQL result  
R = MBS ("[FM.SQL.Release](FMSQLRelease.md)"; sql)  
\]; json)  
Query value lists via SQL in FileMaker 22:

 Let(\[  
 sql = MBS ("FM.SQL.Execute"; ""; "SELECT ValueListName, ValueListId, Source, ModCount FROM FileMaker\_ValueLists");  
 json = MBS ( "[FM.SQL.JSONRecords](FMSQLJSONRecords.md)"; sql; "ValueListName¶ValueListId¶Source¶ModCount" );  
 r = MBS ("[FM.SQL.Release](FMSQLRelease.md)"; sql)  
\]; json)  
### See also

- [FM.SQL.Min](FMSQLMin.md)
- [FM.SQL.RowCount](FMSQLRowCount.md)
- [FM.SQL.Text](FMSQLText.md)
- [FM.SQL.ToMatrix](FMSQLToMatrix.md)
- [FM.SQL.XMLRecord](FMSQLXMLRecord.md)
- [FM.SQL.XMLRecords](FMSQLXMLRecords.md)
- [JSON.InsertOrUpdateRecord](JSONInsertOrUpdateRecord.md)
- [Matrix.ConvertDataType](MatrixConvertDataType.md)
- [SQL.Execute](SQLExecute.md)
- [SQL.FieldCount](SQLFieldCount.md)

### Release notes

- **Version 8.5**
    - Fixed [FM.SQL.Execute](http://www.mbsplugins.eu/FMSQLExecute.shtml) to clear lasterror on success.

### Example Databases

- [DynaPDF/Extract Images](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/DynaPDF/Extract%20Images.shtml#13033FieldAnchor_)
- [SQL in FileMaker/ImageGallery](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/ImageGallery.shtml#1CustomFunctionAnchor_)
- [SQL in FileMaker/Insert or Update Tests](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/Insert%20or%20Update%20Tests.shtml#4ScriptAnchor_)
- [SQL in FileMaker/SQL Select Container](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/SQL%20Select%20Container.shtml#1ScriptAnchor_)
- [SQL to other databases/ODBC Query](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/ODBC%20Query.shtml#5ScriptAnchor_)
- [SQL to other databases/SQL Export](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/SQL%20Export.shtml#1ScriptAnchor_)
- [SQL to other databases/SQLite fun](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/SQLite%20fun.shtml#11ScriptAnchor_)
- [Third Party/FileMaker Snippet Storage](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/Third%20Party/FileMaker%20Snippet%20Storage.shtml#29CustomFunctionAnchor_)

### Blog Entries

- [MBS FileMaker Plugin 16.1 News](https://www.mbsplugins.de/archive/2026-03-31/MBS_FileMaker_Plugin_161_News/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 21 - XML](https://www.mbsplugins.de/archive/2025-12-21/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 20 - Copying records to a new table with SQL](https://www.mbsplugins.de/archive/2025-12-20/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 19 - CSV and TSV](https://www.mbsplugins.de/archive/2025-12-19/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 14 - SQL and JSON](https://www.mbsplugins.de/archive/2025-12-14/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 12 - UNION](https://www.mbsplugins.de/archive/2025-12-12/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 11 - Adding records](https://www.mbsplugins.de/archive/2025-12-11/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 10 - Minimum and Maximum](https://www.mbsplugins.de/archive/2025-12-10/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 9 - Totals and averages](https://www.mbsplugins.de/archive/2025-12-09/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 7 - Precise entries and their types](https://www.mbsplugins.de/archive/2025-12-07/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)

### FileMaker Magazin

- [Ausgabe 2/2022, Seite 29](https://filemaker-magazin.de/neuigkeit/4174-Appetithappen-FMM_202202)
- [Ausgabe 6/2020, Seite 26](https://filemaker-magazin.de/neuigkeit/4118-Appetithappen-FMM_202006)
- [Ausgabe 5/2020, Seite 20](https://filemaker-magazin.de/neuigkeit/4110-Appetithappen-FMM_202005)
- [Ausgabe 3/2018, Seite 34](https://filemaker-magazin.de/neuigkeit/3953-Appetithappen-FMM_201803)

This function checks for a license.

Created 18th April 2015 , last changed 13th June 2025

  
[FM.SQL.CSV](FMSQLCSV.md) - [FM.SQL.Field](FMSQLField.md)

[HTML Version](FMSQLExecute.shtml)