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: " &amp; $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)