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:
12.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.4
13.5
Statistic
FMM
Blog
Execute the SQL Statement against a FileMaker database.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
FM FMSQL | 3.5 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example | Flags |
---|---|---|---|
FileName | The target database name. Can be empty to not limit query to one database. Using a table in another database file may require you adding the other file as an external data source and adding the external table to your relationship graph to grant access. |
"" | |
SQL Statement | SQL Statement as a Text string | ||
Column Delimiter | Columns Separator, only used with SELECT statement. Default is tab character, which has unicode code point 9. You can pass delimiter as text (one character only) or as unicode code point (as number). |
9 | Optional |
Record Delimiter | Record Separator, only used with SELECT statement. Default is return character, which has unicode code point 13. You can pass delimiter as text or as unicode code point. |
13 | Optional |
Params... | Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. | 123 | Optional |
Returns result of SQL expression or error.
SELECT records
MBS("FM.ExecuteFileSQL"; ""; "select * from Contacts")
INSERT record
MBS("FM.ExecuteFileSQL"; ""; "INSERT INTO Contacts (FirstName, LastName) Values('Todd', 'Geist')")
Select records using delimiters
MBS("FM.ExecuteFileSQL"; ""; "select * from Contacts"; "#"; "|")
SELECT records with a Where clause
MBS("FM.ExecuteFileSQL"; ""; "SELECT First_Name, Last_Name from Contacts WHERE ID_Contact_PK ='CN10013'")
Insert records with parameters 2 and 3:
MBS("FM.ExecuteFileSQL"; ""; "INSERT INTO \"OtherTable\" (\"ID\", \"ID Job\") Values(?,?)"; 9; 13; 2; 3 )
Create Record with values via SQL
Set Variable [$FirstName; Value:"First test"]
Set Variable [$LastName; Value:"Last test"]
Set Variable [$Street; Value:"Street test"]
Set Variable [$Zip; Value:"Zip test"]
Set Variable [$City; Value:"City test"]
Set Variable [$Country; Value:"Country test"]
Set Variable [$Phone; Value:"Phone test"]
Set Variable [$r; Value:MBS( "FM.ExecuteFileSQL"; ""; "INSERT INTO \"Export records\" (\"First Name\", \"Last Name\", \"Street\", \"Zip\", \"City\", \"Country\", \"Phone\") VALUES (?,?,?,?,?,?,?)"; 9; 13; $FirstName; $LastName; $Street; $Zip; $City; $City; $Country; $Phone]
If [MBS("IsError")]
Show Custom Dialog [MBS( "FM.ExecuteSQL.LastErrorMessage" )]
End If
Set value for all records of a table:
MBS( "FM.ExecuteFileSQL"; "test.fmp12"; "UPDATE \"My Table\" SET \"My Field\" = 'Hello' ")
Set timestamp to today:
MBS( "FM.ExecuteFileSQL"; "test.fmp12"; "UPDATE \"my Table\" SET \"Change Time\" = TODAY WHERE \"ID\" = '12345' ")
Update 5 fields:
MBS( "FM.ExecuteFileSQL"; "test.fmp12"; "UPDATE \"my Table\" SET \"Number Field\" = 1, \"Text Field" = 'test', \"Dest Field\" = \"Source Field\", \"Hour Field\" = CAST(HOUR(curtime) AS VARCHAR), \"Empty FIeld\" = NULL WHERE \"ID\" = '12345' ")
Create an index:
MBS( "FM.ExecuteFileSQL"; ""; "CREATE INDEX ON tablename.fieldname" )
Select with parameter:
MBS("FM.ExecuteFileSQL"; ""; "select Menge from Boxen where Artikel_Nr=? ORDER BY Menge DESC"; 9; 13; "ABC123")
Query value of a repeating field with []:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT test[2] FROM Kontakte")
Set value of repeating field:
MBS( "FM.ExecuteFileSQL"; ""; "UPDATE Kontakte SET test[2] = 'test'")
Query only first row:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT * FROM ProductImages FETCH FIRST 1 ROWS ONLY")
Insert with parameters:
MBS("FM.ExecuteFileSQL"; ""; "INSERT INTO AttachmentsActions (RecID, Container) VALUES (?, ?)"; 9; 13; $recid; files::container)
Set repeating global field values:
MBS( "FM.ExecuteFileSQL"; ""; "UPDATE \"Meine Tabelle\" SET test[1]='', test[2]='', test[3]='' ")
Set timestamp field:
# via CURRENT_TIMESTAMP constant:
MBS ("FM.ExecuteFileSQL"; ""; "UPDATE Kontakte SET Datum = CURRENT_TIMESTAMP ")
# or cast text to timestamp:
MBS ("FM.ExecuteFileSQL"; ""; "UPDATE Kontakte SET Datum = CAST('2017-04-12 14:14:14' AS TIMESTAMP) ")
# via CURTIMESTAMP constant:
MBS ("FM.ExecuteFileSQL"; ""; "UPDATE Kontakte SET Datum = CURTIMESTAMP ")
# or to now plus one hour:
MBS ("FM.ExecuteFileSQL"; ""; "UPDATE Kontakte SET Datum = CURTIMESTAMP + 3600 ")
Query tables and fields metadata:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT FILEMAKER_TABLES.TableName, FILEMAKER_TABLES.TableID, FILEMAKER_TABLES.BaseTableName, FILEMAKER_TABLES.BaseFileName, FILEMAKER_TABLES.ModCount, FILEMAKER_FIELDS.FieldName, FILEMAKER_FIELDS.FieldType, FILEMAKER_FIELDS.FieldID, FILEMAKER_FIELDS.FieldClass, FILEMAKER_FIELDS.FieldReps, FILEMAKER_FIELDS.ModCount from FILEMAKER_TABLES, FILEMAKER_FIELDS where FILEMAKER_FIELDS.TableName = FILEMAKER_TABLES.TableName" )
Get list of fields in all tables:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT * FROM FileMaker_Fields")
Query list of table names and IDs:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT TableName, TableID FROM Filemaker_Tables")
Find table name for a given table ID:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT TableName FROM Filemaker_Tables WHERE TableID=?"; 9; 13; 1065089)
Query field type of a field:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT FieldType FROM FILEMAKER_FIELDS WHERE TableName=? AND FieldName=? "; 9; 13; "Contacts"; "gtest")
// could return "global varchar", if gtest is a global field in table Contacts
Query list of record IDs for a given table:
MBS("FM.ExecuteFileSQL"; ""; "SELECT ROWID FROM MyTable")
Look for a control character in the text and return record IDs:
MBS("FM.ExecuteFileSQL"; Get(FileName); "SELECT ROWID from \"MyTable\" WHERE \"MyField\" like '%'+ CHR(1) + '%' ")
Update all values in a field based on a query:
MBS("FM.ExecuteFileSQL"; ""; "UPDATE \"Invoices\" SET \"Sum\" = (SELECT SUM(\"Quantity\" * \"Price\") FROM Positions WHERE Invoices.CustomerID = Positions.CustomerID AND Invoices.InvoiceID = Positions.InvoiceID)")
Add container field by using BLOB as type:
MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE Contacts ADD MyField BLOB")
Get all field names and IDs for a table:
MBS( "FM.ExecuteFileSQL"; Get(Filename); "SELECT FieldName, FieldID FROM FILEMAKER_FIELDS WHERE TableName='Contacts' " )
Find newer records via RowID in Contacts table:
# first make a query to get newest RowID
Set Variable [ $RowID; MBS("FM.ExecuteFileSQL"; ""; "SELECT MAX(RowID) FROM \"Contacts\" ") ]
#
# now create/import new records
#
# Finally query IDs of new records
Set Variable [ $NewIDs; MBS("FM.ExecuteFileSQL"; ""; "SELECT \"ID\" FROM \"Contacts\" WHERE RowID > ?"; 9; 13; $RowID)
Use JOIN in SQL:
cross join:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT * from TableA JOIN TableB ON TableA.ID = TableB.aID WHERE TableA.Title='test' ")
Left outer join
MBS( "FM.ExecuteFileSQL"; ""; "SELECT * from TableA LEFT OUTER JOIN TableB ON TableA.ID = TableB.aID WHERE TableA.Title='test' ")
Find duplicates in first name with referencing same table twice:
MBS( "FM.ExecuteFileSQL"; ""; "SELECT * FROM Kontakte A LEFT OUTER JOIN Kontakte B ON A.Vorname = B.Vorname WHERE A.ID != B.ID AND A.Titel = 'Herr' ")
This function checks for a license.
Created 18th August 2014, last changed 4th May 2023