Monkeybread Software - MBS FileMaker Plugin: FM.ExecuteFileSQL

Topics All Mac OS X (Only) Windows (Only) Linux (Only, Not) iOS (Only, Not)
Components Crossplatform Mac & Win Server Client Old Guides Examples
New in version: 8.4 8.5 9.0 9.1 9.2 9.3 9.4 9.5 10.0 10.1 Statistic

FM.ExecuteFileSQL

Execute the SQL Statement against a FileMaker database.

Component	Version	macOS	Windows	Server	FileMaker Cloud	FileMaker iOS SDK
FM FMSQL	3.5	Yes	Yes	Yes	Yes	Yes

MBS( "FM.ExecuteFileSQL"; FileName; SQL Statement { ; Column Delimiter; Record Delimiter; Params } ) More

Parameters

Parameter Description Example value

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 Optional
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

Record Delimiter Optional
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

Params Optional
Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. 123

Parameter	Description	Example value
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	Optional 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
Record Delimiter	Optional 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
Params	Optional Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.	123

Result

Returns result of SQL expression or error.

Description

Execute the SQL Statement against a FileMaker database.
This function requires FileMaker 11 or newer. Please use FM.ExecuteSQL on FileMaker 10 or older.

There must be an occurrence of the Table on the current tables' Graph in the file you specified. If filename is empty, you use current file. 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 Record Locking Errors are Not Reported! This means you must exercise caution when using UPDATE. CAUTION! This is a very powerful function!
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.

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

Examples

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)")

Example Databases

Blog Entries

FileMaker Magazin

Ausgabe 4/2019, Seite 35
Ausgabe 4/2019, Seite 34
Ausgabe 4/2019, Seite 33
Ausgabe 4/2019, Seite 32
Ausgabe 5/2018, Seite 25
Ausgabe 3/2018, Seite 34
Ausgabe 5/2017, Seite 22
Ausgabe 3/2015, Seite 41
Ausgabe 3/2015, Seite 28
Ausgabe 2/2015, Seite 15

Created 18th August 2014, last changed 13th January 2020

FM.Evaluate - FM.ExecuteFileSQLOnIdle

Feedback: Report problem or ask question.