Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

FM.ExecuteSQL

Execute the SQL Statement against the current FileMaker File.

Component Version macOS Windows Linux Server iOS SDK
FM FMSQL 1.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes

Deprecated

This function was deprecated. Use FM.ExecuteFileSQL instead.

MBS( "FM.ExecuteSQL"; SQL Statement { ; Column Delimiter; Record Delimiter } )   More

Parameters

Parameter Description Example Flags
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).		 "" 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 (one character only) or as unicode code point (as number).		 Optional

Result

Returns result of SQL expression or error.

Description

Execute the SQL Statement against the current FileMaker File.
There must be an occurrence of the Table on the current tables' Graph. SELECT returns the the records in as a tabbed delimited text string. All other SQL commands return "".
Use FM.ExecuteSQL.LastError to check for errors.
Supported Commands: SELECT UPDATE (see limitation below) DELETE INSERT CREATE TABLE DROP TABLE CREATE INDEX DROP INDEX WHERE
String comparisons are case sensitive! WHERE First_Name="todd" will not find records that have "Todd" in the first Name Field. You can wrap the field you are searching for with "lower" like this WHERE lower(First_Name)="todd" KNOWN LIMITATIONS Schema editing SQL functions are not supported in FileMaker 8 and 8.5.
Record Locking Errors are Not Reported! This means you must exercise caution when using UPDATE. CAUTION! This is a very powerful function! It is relying on very new and relatively untested resources made available by the release of FileMaker 8.
Seems like on FileMaker 11 you get LastError 8310 for any syntax error, like bad braces or missing quotes. You can put field names in double quotes and string values in single quotes.
Use FM.ExecuteSQLOnIdle for special commands which change database like ALTER TABLE.
Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message.
Error 8309 means you did pass wrong data type, like string for a number.

If you need to pass parameters, please use FM.ExecuteFileSQL.

You can make queries against FILEMAKER_TABLES and FILEMAKER_FIELDS to query information about fields in your solution. The functions FM.QueryBaseTableNames, FM.QueryFieldsForBaseTableName, FM.QueryFieldsForTableName and FM.QueryTableNames provide simplified functions for some queries.

FILEMAKER_TABLES table contains:
1. TableName
2. TableID
3. BaseTableName
4. BaseFileName
5. ModCount

FILEMAKER_FIELDS table contains:
1. TableName
2. FieldName
3. FieldType (the SQL data type, not the FileMaker data type)
4. FieldID
5. FieldClass (Normal, Summary, Calculated)
6. FieldReps
7. ModCount

Some standard SQL functions work in FileMaker including CHR, COALESCE, LEFT, LENGTH, LOWER, LTRIM, MID, RIGHT, RTRIM, SPACE, SUBSTR, SUBSTRING, TRIM and UPPER. There may be more!

If you need longer delimiters, please use FM.SQL.Execute with FM.SQL.Text to use multiple characters for column and record delimiters.

Does not take part in transactions in FileMaker. Changes are made directly to the database, so you can't revert them later with Revert Transaction script step.

Examples

SELECT records

MBS("FM.ExecuteSQL";"select * from Contacts")

INSERT record

MBS("FM.ExecuteSQL";"INSERT INTO Contacts (FirstName, LastName) Values('Bob', 'Miller')")

Select records using delimiters

MBS("FM.ExecuteSQL";"select * from Contacts"; "#"; "|")

SELECT records with a Where clause

MBS("FM.ExecuteSQL"; "SELECT First_Name, Last_Name from Contacts WHERE ID_Contact_PK ='CN10013'")

Get list of fields in all tables:

MBS("FM.ExecuteSQL";"SELECT * FROM FileMaker_Fields")

Create Record with values via SQL

Set Variable [$r; Value:MBS( "FM.ExecuteSQL"; "INSERT INTO \"Export records\" (\"First Name\", \"Last Name\", \"Street\", \"Zip\", \"City\", \"Country\", \"Phone\") VALUES ('MyFirst', 'MyLast', 'MyStreet', 'MyZip', 'MyCity', 'MyCountry', 'MyPhone')")]
If [MBS("IsError")]
    Show Custom Dialog [MBS( "FM.ExecuteSQL.LastErrorMessage" )]
End If

Set value for all records of a table:

MBS( "FM.ExecuteSQL"; "UPDATE \"My Table\" SET \"My Field\" = 'Hello' ")

Set timestamp to today:

MBS( "FM.ExecuteSQL"; "UPDATE \"my Table\" SET \"Change Time\" = TODAY WHERE \"ID\" = '12345' ")

Update 5 fields:

MBS( "FM.ExecuteSQL"; "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' ")

Select with passing variable as part of expression:

MBS("FM.ExecuteSQL"; "SELECT First_Name, Last_Name from Contacts WHERE ID_Contact_PK ='" & $Variable & "'")

Update date field:

MBS( "FM.ExecuteSQL"; "UPDATE myTable SET myDateField = CAST('2015-08-15' as DATE) " )

Delete all data in a table:

$Result = MBS( "FM.ExecuteSQL"; "DELETE FROM \"Prova\";")

Create an index:

MBS( "FM.ExecuteSQL"; "CREATE INDEX ON tablename.fieldname" )

Drop an index:

MBS( "FM.ExecuteSQL"; "DROP INDEX ON tablename.fieldname" )

Value in List of values:

MBS( "FM.ExecuteSQL"; "select * from \"Anlagen\" where \"Modell\" in ('test', 'abc')")

Delete all records from Table People:

# no where condition, so deletes all records!
MBS( "FM.ExecuteSQL"; "DELETE FROM \"People\"")

Query value of a repeating field with []:

MBS( "FM.ExecuteSQL"; "SELECT test[2] FROM Kontakte")

Set value of repeating field:

MBS( "FM.ExecuteSQL"; "UPDATE Kontakte SET test[2] = 'test'")

Query only first row:

MBS( "FM.ExecuteSQL"; "SELECT * FROM ProductImages FETCH FIRST 1 ROWS ONLY")

Truncates table in FileMaker 15 or newer:

MBS( "FM.ExecuteSQL"; "TRUNCATE TABLE Kontakte")

Query field ID for a field with given name and table name:

MBS("FM.ExecuteSQL"; "SELECT FieldID FROM FileMaker_Fields WHERE FieldName='Vorname' and TableName='Kontakte' ")

Query repetition count of global field by name:

MBS("FM.ExecuteSQL"; "SELECT FieldReps FROM FILEMAKER_FIELDS WHERE FieldName='Wiederholen' ")

Query field name and repetition count for all fields in a table:

MBS("FM.ExecuteSQL"; "SELECT FieldReps, FieldName FROM FILEMAKER_FIELDS WHERE TableName='Aufgaben' AND FieldType like '%global%' ")

Query list of record IDs for a given table:

MBS("FM.ExecuteSQL"; "SELECT ROWID FROM MyTable")

Concat two texts with plus and use CAST to make number to text:

MBS( "FM.ExecuteSQL"; "SELECT 'A' + CAST(123 AS VARCHAR) FROM Contacts")

See also

Release notes

Example Databases

Blog Entries

This function checks for a license.

Created 18th August 2014, last changed 15th July 2024


FM.ExecuteFileSQLValue - FM.ExecuteSQL.LastError