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.5   11.0   11.1   11.2   11.3   11.4   11.5   12.0   12.1   12.2    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 iOS SDK License
FM FMSQL 1.2 ✅ Yes ✅ Yes ❌ No ✅ Yes, on macOS and Windows ❌ No Paid

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

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 TypeSQL Type
Textvarchar
Numberdecimal
Datedate
Timetime
Timestamptimestamp
Containerblob

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

See also

Release notes

Example Databases

Blog Entries

Created 18th August 2014, last changed 25th March 2022


FM.ExecuteSQL.LastSQL - FM.GetDictionary

Feedback: Report problem or ask question.


Start Chat