Monkeybread Software - MBS FileMaker Plugin: FM.ExecuteSQLOnIdle

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: 9.3 9.4 9.5 10.0 10.1 10.2 10.3 10.4 10.5 10.6 Statistic FMM Blog

FM.ExecuteSQLOnIdle

This is similar to FM.ExecuteSQL, except that it waits until idle to execute.

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

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

Parameters

Parameter Description Example Flags

SQL Statement the SQL statement

Column Delimiter Optional column delimiter Optional

Record Delimiter Optional record delimiter Optional

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

Parameter	Description	Example	Flags
SQL Statement	the SQL statement
Column Delimiter	Optional column delimiter		Optional
Record Delimiter	Optional record delimiter		Optional
Params	Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.	123	Optional

Description

This is similar to FM.ExecuteSQL, except that it waits until idle to execute.
You run a script pause after using this function to give it time to execute.
See the other function for more info.
Seems like on FileMaker 11 you get LastError 8310 if you use fields with underscore in the name. You avoid that by placing the field names in quotes.

Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message. Use FM.ExecuteFileSQL if you want to specify the file name.

When you create fields, you can use the following types. Add global on the end to make it a global field.

FileMaker Type	SQL Type
Text	varchar
Number	decimal
Date	date
Time	time
Timestamp	timestamp
Container	binary

Examples

Create a new text field in a table:

MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyField varchar")

And delete the field:

MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable DROP COLUMN MyField")

Create new table:

MBS("FM.ExecuteSQLOnIdle";"CREATE TABLE \"MyTABLE\" (test varchar)")

Create new index:

MBS("FM.ExecuteSQLOnIdle";"CREATE INDEX \"IndexLastname\" ON \"Persons\" (\"LastName\");")

Add a global field:

MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyGlobalField varchar global")

Create a field:

Set Variable [ $r ; Value: MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyField varchar") ]
Pause/Resume Script [ Duration (seconds): ,1 ]
If [ MBS("FM.ExecuteSQL.LastError") ≠ 0 ]
Show Custom Dialog [ "Error adding column" ; MBS("FM.ExecuteSQL.LastError") & ": " & MBS("FM.ExecuteSQL.LastErrorMessage") ]
Else
Show Custom Dialog [ "Removed Column" ; "OK" ]
End If

Remove a field:

Set Variable [ $r ; Value: MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable DROP COLUMN MyField") ]
Pause/Resume Script [ Duration (seconds): 0.1 ]
If [ MBS("FM.ExecuteSQL.LastError") ≠ 0 ]
Show Custom Dialog [ "Error removing column" ; MBS("FM.ExecuteSQL.LastError") & ": " & MBS("FM.ExecuteSQL.LastErrorMessage") ]
Else
Show Custom Dialog [ "Removed Column" ; "OK" ]
End If

Change default value:

Set Variable [ $r ; Wert: MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE myTable ALTER myField SET DEFAULT 'FileMaker'") ]

Example Databases

SQL in FileMaker/Create Fields in Filemaker Table

Blog Entries

FileMaker Magazin

Release notes

Version 7.4
- Added checks to ExecuteSQL to return error for ALTER TABLE and CREATE TABLE to remind you to use FM.ExecuteSQLOnIdle.

Created 18th August 2014, last changed 9th April 2019

FM.ExecuteSQL.LastSQL - FM.GetDictionary

Feedback: Report problem or ask question.