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
11.0
Statistic
FMM
Blog
SQL.Execute
Executes the current command.
| Component | Version | macOS | Windows | Linux | Server | FileMaker iOS SDK |
| SQL | 2.6 | Yes | Yes | Yes | Yes | Yes |
Parameters
| Parameter | Description | Example |
|---|---|---|
| Command | The command reference number gained with SQL.NewCommand. | $Command |
Result
Returns "OK" or error message.
Description
Executes the current command.Use the Execute method to execute the query or stored procedure specified in the command text. Execute method calls Prepare method implicitly if needed. If the command has input parameters, they should be bound before calling Execute method. Input parameters represented by param object. To bind input variables, use the SetParam commands.
A command (an SQL statement or procedure) can have a result set after executing. To check whether a result set exists use SQL.isResultSet method. If result set exists, a set of Field objects is created after command execution. Rows from the result set can be fetched one by one using SQL.FetchNext method. To get field description or value use Field method.
Output parameters represented by param objects. They are available after command execution.
Examples
Executes a command:
MBS("SQL.Execute"; $Command)
Create a table:
// create comand
$Command = MBS("SQL.NewCommand"; $Connection; "CREATE TABLE Test(FirstName VARCHAR, LastName VARCHAR, Birthday date, NumberOfOrders integer, TotalSales double)"
// run it
$result2 = MBS("SQL.Execute"; $Command)
// commit changed
$result3 = MBS("SQL.Commit"; $Connection)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
Run something in transaction:
$Command = MBS("SQL.NewCommand"; $Connection; “BEGIN TRANSACTION“
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// create command
$Command = MBS("SQL.NewCommand"; $Connection; "some other SQL command here"
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// create command
$Command = MBS("SQL.NewCommand"; $Connection; "some other SQL command here"
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// commit changed
$result3 = MBS("SQL.Commit"; $Connection)
Call stored procedure sys.sp_setapprole via ODBC connection to Microsoft SQL Server:
# connect via SQL to a Microsoft SQL Server
Set Variable [ $$Connection ; Value: MBS ( "SQL.NewConnection" ) ]
Set Variable [ $SetToODBC ; Value: MBS ( "SQL.SetClient" ; $$Connection ; "ODBC" ) ]
Set Variable [ $ConnectResult ; Value: MBS ( "SQL.Connect" ; $$Connection ; "connection string" ; "user" ; "password" ; "ODBC" ) ]
If [ MBS("IsError") = 0 ]
# make a new command for the stored procedure
Set Variable [ $StoredProcedure ; Value: MBS ( "SQL.NewCommand" ; $$Connection ; "sys.sp_setapprole" ) ]
If [ MBS("IsError") = 0 ]
# for debugging query list of parameters
Set Variable [ $Params ; Value: MBS( "SQL.GetParamNameList"; $StoredProcedure) ]
# set parameters
Set Variable [ $RoleName ; Value: MBS ( "SQL.SetParamAsText" ; $StoredProcedure ; "@rolename" ; "xxx" ) ]
Set Variable [ $Password ; Value: MBS ( "SQL.SetParamAsText" ; $StoredProcedure ; "@password" ; "yyy" ) ]
Set Variable [ $Cookie ; Value: MBS ( "SQL.SetParamAsBoolean" ; $StoredProcedure ; "@fCreateCookie" ; 1) ]
Set Variable [ $Encrypt ; Value: MBS ( "SQL.SetParamAsText" ; $StoredProcedure ; "@encrypt"; "none") ]
Set Variable [ $Execute ; Value: MBS ( "SQL.Execute" ; $StoredProcedure ) ]
// #
If [ MBS("IsError") = 0 ]
# query results on success
Set Variable [ $ReturnValue ; Value: MBS ( "SQL.GetParamAsNumber" ; $StoredProcedure ; "@RETURN_VALUE" ) ]
Set Variable [ $cookie ; Value: MBS ( "SQL.GetParamAsText" ; $StoredProcedure ; "@cookie" ) ]
End If
Set Variable [ $FreeStoredProcedure ; Value: MBS ( "SQL.FreeCommand" ; $StoredProcedure ) ]
End If
Set Variable [ $FreeConnection ; Value: MBS ( "SQL.FreeConnection" ; $$Connection ) ]
End If
See also
- FM.SQL.Execute
- FM.SQL.FieldType
- PHP.Execute
- SQL.NewConnection
- SQL.RowsAffected
- SQL.SetParamAsBoolean
- SQL.SetParamAsContainer
- SQL.SetParamAsNumber
- SQL.SetParamAsText
- SSH.Execute
Example Databases
- SQL to other databases/Microsoft SQL Execute
- SQL to other databases/Microsoft SQL Server Connect
- SQL to other databases/Microsoft SQL Server Query
- SQL to other databases/MySQL example
- SQL to other databases/MySQL Query
- SQL to other databases/ODBC to FIleMaker/ODBC Test
- SQL to other databases/SQL Export
- SQL to other databases/SQLite blob
- SQL to other databases/SQLite fun
- Third Party/FileMaker Snippet Storage
Blog Entries
- Can FileMaker connect to a Microsoft Access database?
- Tip of the day: Connect to MySQL and run a query
- MBS FileMaker Plugin, version 5.1pr4
FileMaker Magazin
- Ausgabe 4/2016, Seite 28
- Ausgabe 3/2016, Seite 30
- Ausgabe 2/2016, Seite 14
- Ausgabe 1/2016, Seite 29
- Ausgabe 4/2015, Seite 16
Release notes
- Version 8.5
- Fixed FM.SQL.Execute to clear lasterror on success.
Created 18th August 2014, last changed 10th October 2019
SQL.Disconnect - SQL.FetchFirst
Feedback: Report problem or ask question.
Links
MBS FileMaker Plugins
