Platforms to show: All Mac Windows Linux Cross-Platform
SQLCommandMBS class
Type | Topic | Plugin | Version | macOS | Windows | Linux | iOS | Targets |
class | SQL | MBS SQL Plugin | 9.3 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | All |
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
The plugin can cache the recordset locally. To enable you can call SQLCommandMBS.Cache or use the Option("AutoCache") = "true" on either command or connection or database objects. The plugin will than fetch all records and store them in memory. After this you can walk over the recordset and use FetchPos, FetchFirst, FetchLast, FetchPrev and FetchNext to locate the rows you need. When you call Field() you always get last row, but to read from cached result set, please use Value() function. When using RecordSet, the values are read via Value() functions automatically.
see also
https://www.sqlapi.com/ApiDoc/class_s_a_command.html
- 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
- 38 methods
- method AsRecordSet as RecordSet
- method AsRowSet as RowSet
- 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 DB2SQLExecDirect(sql as string)
- method DB2SQLRowCount as Int64
- 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 FieldExists(name as string) as Boolean
- 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 PostgreSQLField(RecordIndex as integer, FieldIndex as integer) as string
- method PostgreSQLField(RecordIndex as integer, FieldName as string) as string
- method PostgreSQLFieldCount as Integer
- method PostgreSQLRowCount as Integer
- 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.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.FieldCount(cmd as SQLCommandMBS) as Integer
- SQLConnectionMBS.Commands as SQLCommandMBS()
- SQLDatabaseMBS.Commands as SQLCommandMBS()
Some events using 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 using this class:
- /SQL/CubeSQL Version
- /SQL/Microsoft SQL Stored Procedure
- /SQL/MySQL Fetch rows bulk
- /SQL/MySQL Write blob to file
- /SQL/SQL Exception Tests
- /SQL/SQLDatabaseMBS Microsoft SQL Stored Procedure
- /SQL/SQLite Benchmark vs REALSQLDatabase
- /SQL/SQLite Blob test
- /SQL/SQLite ExecuteSQL
- /SQL/SQLite with ICU
Blog Entries
- News from the MBS Xojo Plugins Version 21.4
- MonkeyBread Software Releases the MBS Xojo Plugins in version 21.4
- Multithreaded plugin functions can increase speed of Xojo application
- Prefetching records from databases
- Problems with killing Xojo threads with plugin calls.
- MBS Xojo / Real Studio Plugins, version 14.3pr10
- MonkeyBread Software Releases the MBS Xojo / Real Studio plug-ins in version 14.0
- MonkeyBread Software Releases the MBS Real Studio plug-ins in version 13.0
- Release notes for SQL or ChartDirector?
- MBS Plugins 11.1 Release notes
Xojo Developer Magazine
- 14.1, pages 28 to 30: The MBS SQL Plugin, An alternative way to connect to databases by Christian Schmitz
- 14.1, pages 24 to 26: The MBS SQL Plugin, An alternative way to connect to databases by Christian Schmitz
- 12.2, page 10: News
The items on this page are in the following plugins: MBS SQL Plugin.
