Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 8.3   8.4   8.5   9.0   9.1   9.2   9.3   9.4   9.5   10.0    Statistic  

SQL.NewCommand

Creates a new command.

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

Parameters

Parameter Description Example value
Connection The command reference number gained with SQL.NewConnection. $Connection
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 method later.
"SELECT * FROM Test"
CommandType Optional
Type of a command. Can be CmdSQLStmt, CmdSQLStmtRaw or CmdStoredProc.
"CmdSQLStmt"
ID Optional
The ID to use instead of default one.
Must be unique. If none is provided, the plugin will create one.

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

See also

Example Databases

Blog Entries

Created 18th August 2014, last changed 10th October 2019


SQL.MySQL.InsertID   -   SQL.NewConnection

Feedback: Report problem or ask question.




Links
MBS Xojo Chart Plugins