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:
12.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.4
13.5
Statistic
FMM
Blog
Executes the current command.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
SQL | 2.6 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example |
---|---|---|
Command | The command reference number gained with SQL.NewCommand. | $Command |
Returns "OK" or error message.
Executes a command:
MBS("SQL.Execute"; $Command)
Create a table:
// create comand
$Command = MBS("SQL.NewCommand"; $Connection; "CREATE TABLE Test(FirstName VARCHAR, LastName VARCHAR, Birthday date, NumberOfOrders integer, TotalSales double)")
// run it
$result2 = MBS("SQL.Execute"; $Command)
// commit changed
$result3 = MBS("SQL.Commit"; $Connection)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
Run something in transaction:
$Command = MBS("SQL.NewCommand"; $Connection; “BEGIN TRANSACTION“
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// create command
$Command = MBS("SQL.NewCommand"; $Connection; "some other SQL command here"
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// create command
$Command = MBS("SQL.NewCommand"; $Connection; "some other SQL command here"
// run it
$result2 = MBS("SQL.Execute"; $Command)
// and free command
$result4 = MBS("SQL.FreeCommand"; $Command)
// commit changed
$result3 = MBS("SQL.Commit"; $Connection)
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)
Run geopoly function to create svg:
Let ( [
command = MBS("SQL.NewCommand"; 22001; "SELECT '<svg version=\"1.1\" viewBox=\"0 0 600 300\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">';
WITH t1(x,y,n,color) AS (VALUES
(100,100,3,'red'),
(200,100,4,'orange'),
(300,100,5,'green'),
(400,100,6,'blue'),
(500,100,7,'purple'),
(100,200,8,'red'),
(200,200,10,'orange'),
(300,200,12,'green'),
(400,200,16,'blue'),
(500,200,20,'purple')
)
SELECT
geopoly_svg(geopoly_regular(x,y,40,n),
printf('style=\"fill:none;stroke:%s;stroke-width:2\"',color))
|| printf(' <text x=\"%d\" y=\"%d\" alignment-baseline=\"central\" text-anchor=\"middle\">%d</text>',x,y+6,n)
FROM t1;
SELECT '</svg>';");
status = MBS("SQL.Execute"; command);
result = MBS("SQL.GetRecordsAsText"; command) & MBS("SQL.GetRecordsAsText"; command) & MBS("SQL.GetRecordsAsText"; command);
freed = MBS("SQL.FreeCommand"; Command)
]; result)
// adapted from https://sqlite.org/geopoly.html
Configure MariaDB/MySQL to use standard quotes around table and field names:
Set Variable [ $cmd ; Value: MBS("SQL.NewCommand"; $Connection; "SET SESSION SQL_MODE=ANSI_QUOTES;") ]
Set Variable [ $cmd ; Value: MBS( "SQL.Execute"; $cmd ) ]
Set Variable [ $r ; Value: MBS( "SQL.FreeCommand"; $cmd) ]
This function is free to use.
Created 18th August 2014, last changed 6th March 2023