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:
9.5
10.0
10.1
10.2
10.3
10.4
10.5
11.0
11.1
11.2
Statistic
FMM
Blog
Creates a new command.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
SQL | 2.6 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
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 |
Returns command reference number or error message.
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
Created 18th August 2014, last changed 10th October 2019
SQL.MySQL.InsertID - SQL.NewConnection
Feedback: Report problem or ask question.