Topics
All
MacOS
(Only)
Windows
(Only)
Linux
(Only, Not)
iOS
(Only, Not)
Components
Crossplatform Mac & Win
Server
Client
Old
Deprecated
Guides
Examples
Videos
New in version:
10.5
11.0
11.1
11.2
11.3
11.4
11.5
12.0
12.1
12.2
Statistic
FMM
Blog
FM.SQL.Execute
Execute the SQL Statement against a FileMaker database.
Component | Version | macOS | Windows | Linux | Server | iOS SDK | License |
FM FMSQL | 5.1 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | Paid |
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, FM.SQL.FieldCount, FM.SQL.Field and FM.SQL.FieldType to query result.
Using FM.SQL.Execute avoids converting all result values to text like in FM.ExecuteSQL or FM.ExecuteFileSQL 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 to release the result from memory when you are done with it.
This function requires FileMaker 11 or newer. Please use FM.ExecuteSQL 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 " & MBS( "FM.SQL.RowCount"; $sql ) &" rows with each " & MBS( "FM.SQL.FieldCount"; $sql ) & " …"]
Set Variable [$r; Value:MBS( "FM.SQL.Release"; $sql )]
End If
Query record as JSON:
Let ( [
fields = MBS( "FM.QueryFieldsForTableName"; "Contacts");
Records = MBS( "FM.SQL.Execute"; ""; "SELECT \"" & substitute(fields; ¶; "\", \"") & "\" FROM \"Contacts\" WHERE \"First\" = ?"; "Joe");
json = MBS( "FM.SQL.JSONRecord"; Records; 0; fields);
r = MBS( "FM.SQL.Release"; 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 (" & sku & ") and DocDate between ? and ?"; $sdat; $edat);
json = MBS( "FM.SQL.JSONRecords"; sql1; "DocNum¶LineTotal¶DB"; 1);
r = MBS( "FM.SQL.Release"; 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"; $r; "UUID¶ID¶ID_Department¶Team.Name¶MemberCount") ]
Set Variable [ $e ; Value: MBS( "FM.SQL.Release"; $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\") > 0") ]
If [ MBS("IsError") ]
Show Custom Dialog [ "Error" ; $sql ]
Else
Set Variable [ $IDs ; Value: MBS("FM.SQL.JSONColumn"; $SQL; 0) ]
Set Variable [ $Groups ; Value: MBS("FM.SQL.JSONColumn"; $SQL; 1) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $SQL) ]
Show Custom Dialog [ "Values" ; $IDs & ¶ & $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" ) ]
See also
- FM.SQL.InsertRecordsToSQL
- FM.SQL.JSONRecord
- FM.SQL.Max
- FM.SQL.Min
- FM.SQL.Release
- FM.SQL.RowCount
- FM.SQL.Text
- IsError
- SQL.Execute
- SQL.FieldCount
Release notes
- Version 8.5
- Fixed FM.SQL.Execute to clear lasterror on success.
Example Databases
- SQL in FileMaker/ImageGallery
- SQL in FileMaker/SQL Select Container
- SQL to other databases/SQL Export
- Third Party/FileMaker Snippet Storage
Blog Entries
- Sending email with a huge custom function
- FileMaker records to XML or JSON
- Query FileMaker records as JSON
- Can FileMaker connect to a Microsoft Access database?
- FileMaker SQL with Sum, Min, Max and Avg on results
- MBS FileMaker Plugin, version 8.5pr4
- Image Gallery in FileMaker using SQL
- CSV functions in MBS FileMaker Plugin
- FileMaker Custom function to query a value from other table or file
- MBS FileMaker Plugin 7.0 - More than 4400 Functions In One Plugin
FileMaker Magazin
Created 18th April 2015, last changed 12nd April 2022
Feedback: Report problem or ask question.
