SQL.NewCommand -------------- Creates a new command. | Component | Version | macOS | Windows | Linux | Server | iOS SDK | |---|---|---|---|---|---|---| | [SQL](component_SQL.md) | [2.6](newinversion26.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | MBS( "SQL.NewCommand"; Connection { ; Command; CommandType; ID } ) **MBS**( **"SQL.NewCommand";** /\* Creates a new command. \*/ **$Connection**; /\* The command reference number gained with [SQL.NewConnection](SQLNewConnection.md). \*/ **$Command**; /\* Optional; This represents command text string (an SQL statement or a stored procedure name). If it is empty string, no command text is associated with the command, and you have to call [SQL.SetCommandText](SQLSetCommandText.md) method later.e.g. "SELECT \* FROM Test" \*/ **$CommandType**; /\* Optional; Type of a command. Can be CmdSQLStmt, CmdSQLStmtRaw or CmdStoredProc.e.g. "CmdSQLStmt" \*/ **$ID**) /\* Optional; The ID to use instead of default one. Must be unique. If none is provided, the plugin will create one. \*/ ### Parameters | Parameter | Description | Example | Flags | |---|---|---|---| | Connection | The command reference number gained with [SQL.NewConnection](SQLNewConnection.md). | $Connection | | | Command | This represents command text string (an SQL statement or a stored procedure name). If it is empty string, no command text is associated with the command, and you have to call [SQL.SetCommandText](SQLSetCommandText.md) method later. | "SELECT \* FROM Test" | Optional | | CommandType | Type of a command. Can be CmdSQLStmt, CmdSQLStmtRaw or CmdStoredProc. | "CmdSQLStmt" | Optional | | ID | The ID to use instead of default one. Must be unique. If none is provided, the plugin will create one. | | Optional | ### Result Returns command reference number or error message. ### Description Creates a new command. Don't forget to free command with [SQL.FreeCommand](SQLFreeCommand.md). SQL command reference numbers are starting at 23000 and counting up for each new command. For server scripting, please use one connection for each script running on server, so multiple scripts running at the same data don't access the same connection or command objects in memory. And each connection has its own transaction and error state. ### Examples Creates a new command to create table: MBS ("SQL.NewCommand"; $Connection ; "CREATE TABLE Test(FirstName VARCHAR, LastName VARCHAR, Birthday date, NumberOfOrders integer, TotalSales double)") Run a stored procedure: # new command for calling our test procedure on a Microsoft SQL Server Set Variable \[$command ; MBS ( "SQL.NewCommand"; $Connection ; "dbo.Test" ) \] \# set a parameter Set Variable \[$r ; MBS ( "[SQL.SetParamAsNumber](SQLSetParamAsNumber.md)"; $Command ; "Param1"; 7 ) \] \# execute query Set Variable \[$r ; MBS ( "[SQL.Execute](SQLExecute.md)"; $Command ) \] \# fetch first record Set Variable \[$r ; MBS ( "[SQL.FetchNext](SQLFetchNext.md)"; $Command ) \] \# get result field Set Variable \[$result ; MBS ( "[SQL.GetFieldAsText](SQLGetFieldAsText.md)"; $Command ; 1 ) \] New command with named parameters: MBS ("SQL.NewCommand"; $Connection ; "update Documents set ProductNumber=:ProductNumber, PubOptionNo=:PubOptionNo, DueInStock=:DueInStock, ProductTypeID=:ProductTypeID where DocID=:DocID“ ) 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](SQLNewConnection.md)" ) \] Set Variable \[ $SetToODBC ; Value: MBS ( "[SQL.SetClient](SQLSetClient.md)" ; $$Connection ; "ODBC" ) \] Set Variable \[ $ConnectResult ; Value: MBS ( "[SQL.Connect](SQLConnect.md)" ; $$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](SQLGetParamNameList.md)"; $StoredProcedure ) \] # set parameters Set Variable \[ $RoleName ; Value: MBS ( "[SQL.SetParamAsText](SQLSetParamAsText.md)" ; $StoredProcedure ; "@rolename" ; "xxx" ) \] Set Variable \[ $Password ; Value: MBS ( "[SQL.SetParamAsText](SQLSetParamAsText.md)" ; $StoredProcedure ; "@password" ; "yyy" ) \] Set Variable \[ $Cookie ; Value: MBS ( "[SQL.SetParamAsBoolean](SQLSetParamAsBoolean.md)" ; $StoredProcedure ; "@fCreateCookie" ; 1) \] Set Variable \[ $Encrypt ; Value: MBS ( "[SQL.SetParamAsText](SQLSetParamAsText.md)" ; $StoredProcedure ; "@encrypt"; "none") \] Set Variable \[ $Execute ; Value: MBS ( "[SQL.Execute](SQLExecute.md)" ; $StoredProcedure ) \] // # If \[ MBS ("IsError") = 0 \] # query results on success Set Variable \[ $ReturnValue ; Value: MBS ( "[SQL.GetParamAsNumber](SQLGetParamAsNumber.md)" ; $StoredProcedure ; "@RETURN\_VALUE" ) \] Set Variable \[ $cookie ; Value: MBS ( "[SQL.GetParamAsText](SQLGetParamAsText.md)" ; $StoredProcedure ; "@cookie" ) \] End If Set Variable \[ $FreeStoredProcedure ; Value: MBS ( "[SQL.FreeCommand](SQLFreeCommand.md)" ; $StoredProcedure ) \] End If Set Variable \[ $FreeConnection ; Value: MBS ( "[SQL.FreeConnection](SQLFreeConnection.md)" ; $$Connection ) \] End If Query with Let statement: Let ( \[ command = MBS ("SQL.NewCommand"; 22001; "SELECT sqlite\_version();"); status = MBS ("[SQL.Execute](SQLExecute.md)"; command); result = MBS ("[SQL.GetRecordsAsText](SQLGetRecordsAsText.md)"; command); freed = MBS ("[SQL.FreeCommand](SQLFreeCommand.md)"; Command) \]; result) Use INSERT and SELECT to get new ID of new record for Microsoft SQL Server: MBS ("SQL.NewCommand"; $Connection ; "insert into TestTable (TZKONZ,TZFIRM,TZIDEN,TZBEZ1) values ('a','b',1234,'Tralala'); SELECT SCOPE\_IDENTITY();") Query version for DuckDB: Set Variable \[ $Connection ; Value: MBS ("[SQL.NewConnection](SQLNewConnection.md)") \] \# Tell plugin where PostgreSQL library is Set Variable \[ $result ; Value: MBS ("[SQL.SetConnectionOption](SQLSetConnectionOption.md)"; $Connection ; "DUCKDB.LIBS"; "/opt/homebrew/Cellar/duckdb/1.2 .1/lib/libduckdb.dylib") \] \# Connect to database Set Variable \[ $result ; Value: MBS ("[SQL.Connect](SQLConnect.md)"; $Connection ; "/Users/cs/Desktop/test.duckdb"; ""; ""; "DuckDB") \] If \[ $result ≠ "OK" \] Show Custom Dialog \[ "Error: " & $result \] Set Variable \[ $result ; Value: MBS ("[SQL.FreeConnection](SQLFreeConnection.md)"; $Connection ) \] Else # Create select and run it Set Variable \[ $Command ; Value: MBS ("SQL.NewCommand"; $Connection ; "SELECT version() AS version;") \] # Run it Set Variable \[ $result ; Value: MBS ("[SQL.Execute](SQLExecute.md)"; $Command ) \] If \[ $result ≠ "OK" \] Show Custom Dialog \[ "Error: "; $result \] Else # get first record Set Variable \[ $result ; Value: MBS ("[SQL.FetchNext](SQLFetchNext.md)"; $Command ) \] # get version value Set Variable \[ $v ; Value: MBS ("[SQL.GetFieldAsText](SQLGetFieldAsText.md)"; $command ; 1) \] Show Custom Dialog \[ "Version" ; $v \] End If End If \# Cleanup Set Variable \[ $result2 ; Value: MBS ("[SQL.FreeCommand](SQLFreeCommand.md)"; $Command ) \] Set Variable \[ $result2 ; Value: MBS ("[SQL.FreeConnection](SQLFreeConnection.md)"; $Connection ) \] ### See also - [SQL.FreeCommand](SQLFreeCommand.md) - [SQL.isExecuted](SQLisExecuted.md) - [SQL.isOpened](SQLisOpened.md) - [SQL.isResultSet](SQLisResultSet.md) - [SQL.SetParamDataType](SQLSetParamDataType.md) - [SQL.SetParamOption](SQLSetParamOption.md) - [SQL.SetParamsWithJSON](SQLSetParamsWithJSON.md) - [SQL.SetParamTypesValues](SQLSetParamTypesValues.md) - [SQL.SetParamValue](SQLSetParamValue.md) - [SQL.SetParamValues](SQLSetParamValues.md) ### Example Databases - [SQL in FileMaker/Insert or Update Tests](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/Insert%20or%20Update%20Tests.shtml#14ScriptAnchor_) - [SQL to other databases/DuckDB Query](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/DuckDB%20Query.shtml#1ScriptAnchor_) - [SQL to other databases/Microsoft Access Execute](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/Microsoft%20Access%20Execute.shtml#1ScriptAnchor_) - [SQL to other databases/Microsoft SQL Execute](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/Microsoft%20SQL%20Execute.shtml#1ScriptAnchor_) - [SQL to other databases/Microsoft SQL Server Connect](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/Microsoft%20SQL%20Server%20Connect.shtml#1ScriptAnchor_) - [SQL to other databases/MySQL example](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/MySQL%20example.shtml#1ScriptAnchor_) - [SQL to other databases/ODBC Query](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/ODBC%20Query.shtml#1ScriptAnchor_) - [SQL to other databases/PostgreSQL Query](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/PostgreSQL%20Query.shtml#1ScriptAnchor_) - [SQL to other databases/SQLite blob](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/SQLite%20blob.shtml#1ScriptAnchor_) - [SQL to other databases/SQLite fun](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20to%20other%20databases/SQLite%20fun.shtml#1ScriptAnchor_) ### Blog Entries - [Connect to DuckDB in FileMaker](https://www.mbsplugins.de/archive/2025-04-10/Connect_to_DuckDB_in_FileMaker/monkeybreadsoftware_blog_filemaker) - [Connect to Postgres in FileMaker](https://www.mbsplugins.de/archive/2025-04-09/Connect_to_Postgres_in_FileMak/monkeybreadsoftware_blog_filemaker) - [Insert and return new record ID](https://www.mbsplugins.de/archive/2024-06-27/Insert_and_return_new_record_I/monkeybreadsoftware_blog_filemaker) - [Moving data from ODBC to FileMaker via script](https://www.mbsplugins.de/archive/2023-02-12/Moving_data_from_ODBC_to_FileM/monkeybreadsoftware_blog_filemaker) - [Transactions with SQL functions](https://www.mbsplugins.de/archive/2022-04-28/Transactions_with_SQL_function/monkeybreadsoftware_blog_filemaker) - [Connect to an external database server in FileMaker](https://www.mbsplugins.de/archive/2022-03-11/Connect_to_an_external_databas/monkeybreadsoftware_blog_filemaker) - [Can FileMaker connect to a Microsoft Access database?](https://www.mbsplugins.de/archive/2019-12-29/Can_FileMaker_connect_to_a_Mic/monkeybreadsoftware_blog_filemaker) - [MBS FileMaker Plugin IDs](https://www.mbsplugins.de/archive/2017-04-15/MBS_FileMaker_Plugin_IDs/monkeybreadsoftware_blog_filemaker) - [Tip of the day: Connect to MySQL and run a query](https://www.mbsplugins.de/archive/2017-02-07/Tip_of_the_day_Connect_to_MySQ/monkeybreadsoftware_blog_filemaker) - [MBS FileMaker Plugin, version 6.4pr7](https://www.mbsplugins.de/archive/2016-09-14/MBS_FileMaker_Plugin_version_6/monkeybreadsoftware_blog_filemaker) ### FileMaker Magazin - [Ausgabe 2/2022, Seite 28](https://filemaker-magazin.de/neuigkeit/4174-Appetithappen-FMM_202202) - [Ausgabe 4/2016, Seite 28](https://filemaker-magazin.de/neuigkeit/3876-Appetithappen-FMM_201604) - [Ausgabe 3/2016, Seite 30](https://filemaker-magazin.de/neuigkeit/3883-Appetithappen-FMM_201603) - [Ausgabe 2/2016, Seite 14](https://filemaker-magazin.de/neuigkeit/3879-Appetithappen-FMM_201602) - [Ausgabe 1/2016, Seite 29](https://filemaker-magazin.de/neuigkeit/3870-Appetithappen-FMM_201601) - [Ausgabe 4/2015, Seite 16](https://filemaker-magazin.de/neuigkeit/3822-Appetithappen-FMM_201504) This function checks for a license. Created 18th August 2014 , last changed 6th April 2025 [SQL.MySQL.InsertID](SQLMySQLInsertID.md) - [SQL.NewConnection](SQLNewConnection.md) [HTML Version](SQLNewCommand.shtml)