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.1
10.2
10.3
10.4
10.5
11.0
11.1
11.2
11.3
11.4
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. | "" | |
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 or as unicode code point. |
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)
Created 18th August 2014, last changed 5th June 2021
FM.Evaluate - FM.ExecuteFileSQLOnIdle
Feedback: Report problem or ask question.