Components | All | New | MacOS | Windows | Linux | iOS | ||||
Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old |
SQL.Execute
Executes the current command.
Component | Version | macOS | Windows | Linux | Server | 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
Query with Let statement:
Let ( [
command = MBS("SQL.NewCommand"; 22001; "SELECT sqlite_version();");
status = MBS("SQL.Execute"; command);
result = MBS("SQL.GetRecordsAsText"; command);
freed = MBS("SQL.FreeCommand"; Command)
]; result)
Run geopoly function to create svg:
Let ( [
command = MBS("SQL.NewCommand"; 22001; "SELECT '<svg version=\"1.1\" viewBox=\"0 0 600 300\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">';
WITH t1(x,y,n,color) AS (VALUES
(100,100,3,'red'),
(200,100,4,'orange'),
(300,100,5,'green'),
(400,100,6,'blue'),
(500,100,7,'purple'),
(100,200,8,'red'),
(200,200,10,'orange'),
(300,200,12,'green'),
(400,200,16,'blue'),
(500,200,20,'purple')
)
SELECT
geopoly_svg(geopoly_regular(x,y,40,n),
printf('style=\"fill:none;stroke:%s;stroke-width:2\"',color))
|| printf(' <text x=\"%d\" y=\"%d\" alignment-baseline=\"central\" text-anchor=\"middle\">%d</text>',x,y+6,n)
FROM t1;
SELECT '</svg>';");
status = MBS("SQL.Execute"; command);
result = MBS("SQL.GetRecordsAsText"; command) & MBS("SQL.GetRecordsAsText"; command) & MBS("SQL.GetRecordsAsText"; command);
freed = MBS("SQL.FreeCommand"; Command)
]; result)
// adapted from https://sqlite.org/geopoly.html
Configure MariaDB/MySQL to use standard quotes around table and field names:
Set Variable [ $cmd ; Value: MBS("SQL.NewCommand"; $Connection; "SET SESSION SQL_MODE=ANSI_QUOTES;") ]
Set Variable [ $cmd ; Value: MBS( "SQL.Execute"; $cmd ) ]
Set Variable [ $r ; Value: MBS( "SQL.FreeCommand"; $cmd) ]
See also
- SQL.isExecuted
- SQL.isResultSet
- SQL.NewConnection
- SQL.RowsAffected
- SQL.SetParamAsBoolean
- SQL.SetParamAsContainer
- SQL.SetParamAsNumber
- SQL.SetParamsWithJSON
- SQL.SetParamTypesValues
- SSH.Execute
Release notes
- Version 8.5
- Fixed FM.SQL.Execute to clear lasterror on success.
Example Databases
- SQL to other databases/Firebird Query
- SQL to other databases/Microsoft Access Execute
- SQL to other databases/Microsoft SQL Execute
- 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 Query
- SQL to other databases/ODBC to FIleMaker/ODBC Test
- SQL to other databases/PostgreSQL Query
- SQL to other databases/SQLite fun
Blog Entries
- Insert and return new record ID
- Moving data from ODBC to FileMaker via script
- Transactions with SQL functions
- Connect to an external database server in FileMaker
- 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
This function is free to use.
Created 18th August 2014, last changed 6th March 2023