Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

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
Function: This is the central class for the using the SQL database access.
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
Notes: 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.

Feedback, Comments & Corrections

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:

Some events for this class:

Some examples which use this class:


The items on this page are in the following plugins: MBS SQL Plugin.


SQLCLobMBS   -   SQLConnectionMBS




Links
MBS Xojo PDF Plugins