Platforms to show: All Mac Windows Linux Cross-Platform
SQLCommandMBS class
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
class | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
Example:
dim con as SQLConnectionMBS
dim cmd as SQLCommandMBS
try
con = new SQLConnectionMBS // connection object
cmd = new SQLCommandMBS // create command object
// where is the library?
con.SetFileOption con.kOptionLibraryMySQL, SpecialFolder.UserHome.Child("libmysqlclient.dylib")
// connect to database (mySQL in our example)
// server: 192.168.1.80
// port: 3306
// database: test
// name: root
// no password
con.Connect("192.168.1.80,3306@test","root","",SQLConnectionMBS.kMySQLClient)
// associate a command with connection
// connection can also be specified in SACommand constructor
cmd.Connection=con
// create table
cmd.setCommandText("Create table test_tbl(fid integer, fvarchar20 varchar(20), fblob blob)")
cmd.Execute
// insert value
cmd.setCommandText("Insert into test_tbl(fid, fvarchar20) values (1, 'Some string (1)')")
cmd.Execute
// commit changes on success
con.Commit
MsgBox("Table created, row inserted!")
catch r as SQLErrorExceptionMBS
// SAConnection::Rollback()
// can also throw an exception
// (if a network error for example),
// we will be ready
try
// on error rollback changes
if con<>nil then
con.rollback
end if
catch x as SQLErrorExceptionMBS
// ignore
end try
// show error message
MsgBox r.message
end try
- 2 events
- 16 properties
- property CommandText as string
- property CommandType as Integer
- property Connection as SQLConnectionMBS
- property FieldCount as Integer
- property Fields as Dictionary
- property hasCache as Boolean
- property isExecuted as boolean
- property isExecuting as Boolean
- property isOpened as boolean
- property isResultSet as boolean
- property Options as Dictionary
- property ParamCount as Integer
- property Parameters as Dictionary
- property RowsAffected as Integer
- property Tag as Variant
- property Option(name as string) as string
- shared property CommandCount as Integer
- 30 methods
- method AsRecordSet as RecordSet
- method Cache
- method Cancel
- method Close
- method Constructor
- method Constructor(connection as SQLConnectionMBS, SQLCommand as String, CommandType as Integer = 0)
- method CreateParam(name as string, ParamType as Integer, DirType as Integer=0) as SQLParamMBS
- method CreateParam(name as string, ParamType as Integer, NativeType as Integer, ParamSize as Integer, ParamPrecision as Integer, ParamScale as Integer, DirType as Integer=0) as SQLParamMBS
- method DestroyParams
- method Execute
- method ExecuteCommand(SQLCommand as string, CommandType as Integer=0)
- method ExecuteCommandMT(SQLCommand as string, CommandType as Integer=0)
- method ExecuteMT
- method FetchFirst as boolean
- method FetchLast as boolean
- method FetchNext as boolean
- method FetchPos(offset as Integer, relative as boolean = false) as boolean
- method FetchPrior as boolean
- method Field(index as Integer) as SQLFieldMBS
- method Field(name as string) as SQLFieldMBS
- method FieldNames as String()
- method Open
- method Param(ID as Integer) as SQLParamMBS
- method Param(name as string) as SQLParamMBS
- method ParamByIndex(index as Integer) as SQLParamMBS
- method Prepare
- method setCommandText(SQLCommand as string, CommandType as Integer = 0)
- method SetParameters(Params as dictionary)
- method Value(index as Integer) as SQLValueReadMBS
- method Value(name as string) as SQLValueReadMBS
- 9 constants
- const kOptionPreFetchRows = "PreFetchRows"
- const kParamDirTypeInput = 0
- const kParamDirTypeInputOutput = 1
- const kParamDirTypeOutput = 2
- const kParamDirTypeReturn = 3
Command Types
Constant | Value | Description |
---|---|---|
kCommandTypeSQLStatement | 1 |
Command is an SQL statement. |
kCommandTypeSQLStatementRaw | 2 |
Command is an SQL statement that mustn't be interpreted by SQLAPI. |
kCommandTypeStoredProcedure | 3 |
Command is a stored procedure or a function. |
kCommandTypeUnknown | 0 |
Used by default. Library detects command type automatically. |
This class has no sub classes.
Some methods using this class:
- DB2MBS.SQLExecDirect(cmd as SQLCommandMBS, text as string)
- DB2MBS.SQLRowCount(cmd as SQLCommandMBS) as Int64
- InformixMBS.Error(cmd as SQLCommandMBS, byref SQLState as string, byref NativeError as Integer, byref ErrorMsg as string) as Integer
- InformixMBS.GetCursorName(cmd as SQLCommandMBS) as string
- InformixMBS.HSTMT(cmd as SQLCommandMBS) as Integer
- InformixMBS.SetCursorName(cmd as SQLCommandMBS, name as string) as boolean
- MySQLMBS.NumberOfRows(cmd as SQLCommandMBS) as UInt64
- PostgreSQLAPIMBS.Field(cmd as SQLCommandMBS, RecordIndex as Integer, FieldIndex as Integer) as string
- PostgreSQLAPIMBS.Field(cmd as SQLCommandMBS, RecordIndex as Integer, FieldName as string) as string
- PostgreSQLAPIMBS.FieldCount(cmd as SQLCommandMBS) as Integer
- PostgreSQLAPIMBS.RecordCount(cmd as SQLCommandMBS) as Integer
- SQLConnectionMBS.Commands as SQLCommandMBS()
- SQLDatabaseMBS.Commands as SQLCommandMBS()
Some events for this class:
- SQLConnectionMBS.Trace(traceInfo as Integer, SQL as string, Command as SQLCommandMBS)
- SQLDatabaseMBS.Trace(traceInfo as Integer, SQL as string, Command as SQLCommandMBS)
- SQLGlobalsMBS.Trace(traceInfo as Integer, SQL as string, Connection as SQLConnectionMBS, Command as SQLCommandMBS)
Some examples which use this class:
- /SQL/CubeSQL Version
- /SQL/FileMaker ODBC Connect
- /SQL/Firebird Query
- /SQL/Microsoft SQL Stored Procedure
- /SQL/Microsoft SQL via ODBC
- /SQL/Microsoft SQL via ODBC on Linux
- /SQL/Microsoft SQL via ODBC on Mac
- /SQL/MySQL Connect via SSH
- /SQL/MySQL ExecuteSQL
- /SQL/MySQL ExecuteSQL with Parameters
- /SQL/MySQL Fetch rows bulk
- /SQL/MySQL Fetch values
- /SQL/MySQL Query Version
- /SQL/MySQL Update values
- /SQL/MySQL Write blob to file
- /SQL/PostgreSQL Connect
- /SQL/PostgreSQL Notify and Listen
- /SQL/PostgreSQL RecordCount
- /SQL/SQL Exception Tests
- /SQL/SQLDatabaseMBS Microsoft SQL Server Data Types
- /SQL/SQLDatabaseMBS Microsoft SQL Stored Procedure
Blog Entries
- MonkeyBread Software Releases the MBS Xojo Plugins in version 18.1
- MBS Xojo Plugins, version 18.1pr1
- Prefetching records from databases
- Problems with killing Xojo threads with plugin calls.
- Multiple recordsets with Microsoft SQL Server
- MBS Xojo / Real Studio Plugins, version 16.5pr9
- MBS Releases the MBS Xojo / Real Studio plug-ins in version 16.4
- MBS Xojo / Real Studio Plugins, version 16.4pr5
- Upcoming Changes for our SQL Plugin
- MBS Xojo / Real Studio Plugins, version 15.2pr4
The items on this page are in the following plugins: MBS SQL Plugin.
Links
MBS FileMaker Plugins