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
This is the central class for the using the SQL database access.
Example 
Var con as SQLConnectionMBS
Var 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

Constants

Constant Value Description
kOptionPreFetchRows "PreFetchRows" One of the option constants. Example
kParamDirTypeInput 0 One of the parameter direction type constants. Input parameter.
kParamDirTypeInputOutput 1 One of the parameter direction type constants. Input/output parameter.
kParamDirTypeOutput 2 One of the parameter direction type constants. Output parameter.
kParamDirTypeReturn 3 One of the parameter direction type constants. Returning parameter.

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 using this class:

Some examples using this class:

Blog Entries

Xojo Developer Magazine

Release notes


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


SQLCLobMBS   -   SQLConnectionMBS


The biggest plugin in space...