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:
12.1
12.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.4
Statistic
FMM
Blog
Executes a SQL command at idle time.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
FM FMSQL | 6.5 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example | Flags |
---|---|---|---|
SQL Statement | the SQL statement | ||
FileName | The file name of the database. Using a table in another database file may require you adding the other file as an external data source and adding the external table to your relationship graph to grant access. |
Get(FileName) | |
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). |
9 | 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). |
13 | Optional |
Params... | Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. | 123 | Optional |
Returns OK or error.
Create a new text field in a table:
MBS("FM.ExecuteSQLOnIdle";"ALTER TABLE TestTable ADD MyField varchar")
Create in a script and check error:
# set command to run
Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQLOnIdle"; "ALTER TABLE Kontakte ADD MyField varchar"; "Kontakte.fmp12") ]
# Make pause to run it
Pause/Resume Script [ Duration (seconds): ,1 ]
# Check error status
Set Variable [ $errorNumber ; Value: MBS( "FM.ExecuteSQL.LastError" ) ]
Set Variable [ $errorMessage ; Value: MBS( "FM.ExecuteSQL.LastErrorMessage" ) ]
Create a field:
Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQLOnIdle";"ALTER TABLE TestTable ADD MyField varchar"; Get(FileName)) ]
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 a new text field in a table with variables for the names:
Set Variable [ $TableName ; Value: "TestTable" ]
Set Variable [ $FieldName ; Value: "MyField" ]
Set Variable [ $FieldType ; Value: "VARCHAR" ]
Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQLOnIdle";"ALTER TABLE \"" & $TableName & "\" ADD \"" & $FieldName & "\" " & $FieldType & " " ; Get(FileName)) ]
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 a table:
Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQLOnIdle";"CREATE TABLE \"TestTable\" (ID INT PRIMARY KEY, MyTextField VARCHAR, MyNumberField DECIMAL)"; Get(FileName)) ]
// for details see fm16_sql_reference.pdf (or newer) from Claris Inc.
This function checks for a license.
Created 16th October 2016, last changed 20th March 2023