Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

SQL.NewCommand

Creates a new command.

Component Version macOS Windows Linux Server iOS SDK
SQL 2.6 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "SQL.NewCommand"; Connection { ; Command; CommandType; ID } )   More

Parameters

Parameter Description Example Flags
Connection The command reference number gained with SQL.NewConnection. $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 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.
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"; $Command; "Param1"; 7 ) ]
# execute query
Set Variable [$r; MBS( "SQL.Execute"; $Command ) ]
# fetch first record
Set Variable [$r; MBS( "SQL.FetchNext"; $Command ) ]
# get result field
Set Variable [$result; MBS( "SQL.GetFieldAsText"; $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" ) ]
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)

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();")

See also

Example Databases

Blog Entries

This function checks for a license.

Created 18th August 2014, last changed 23th September 2021


SQL.MySQL.InsertID - SQL.NewConnection