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.0   10.1   10.2   10.3   10.4   10.5   11.0   11.1   11.2   11.3    Statistic    FMM    Blog  

SQL.Execute

Executes the current command.

Component Version macOS Windows Linux Server iOS SDK
SQL 2.6 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "SQL.Execute"; Command )   More

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

See also

Release notes

Example Databases

Blog Entries

FileMaker Magazin

Created 18th August 2014, last changed 21st May 2021


SQL.Disconnect - SQL.FetchFirst

Feedback: Report problem or ask question.



Links
MBS FileMaker blog

Start Chat