Monkeybread Software - MBS FileMaker Plugin: FM.ExecuteSQLOnIdle

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

FM.ExecuteSQLOnIdle

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

Component	Version	macOS	Windows	Linux	Server	iOS SDK
FM FMSQL	1.2	✅ Yes	✅ Yes	❌ No	✅ Yes, on macOS and Windows	❌ No

Deprecated

This function was deprecated. Use FM.ExecuteFileSQLOnIdle instead.

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

Parameters

Parameter Description Example Flags

SQL Statement the SQL statement

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

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

You can do more schema changes via ODATA interface on FileMaker Server including changing or renaming fields.

This function takes variable number of parameters. Pass as much parameters as needed separated by the semicolon in FileMaker.
Please repeat Params parameter as often as you need.

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 container field by making BLOB:

Set Variable [ $r ; Value: MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyField blob") ]
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

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

Create numeric field with default value:

MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyField decimal DEFAULT 123“)

Release notes

Version 12.2
- Marked FM.ExecuteSQLOnIdle as deprecated in favor of newer FM.ExecuteFileSQLOnIdle function.
Version 7.4
- Added checks to ExecuteSQL to return error for ALTER TABLE and CREATE TABLE to remind you to use FM.ExecuteSQLOnIdle.

Example Databases

SQL in FileMaker/Create Fields in Filemaker Table

Blog Entries

FileMaker Magazin

This function checks for a license.

Created 18th August 2014, last changed 11st April 2024

FM.ExecuteSQL.LastSQL - FM.FieldStatistics

💬 Ask a question or report a problem