Xojo Conferences
MBSSep2018MunichDE
XDCMay2019MiamiUSA

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 blog