Platforms to show: All Mac Windows Linux Cross-Platform

SQLDatabaseMBS class

Super class: Database

Var db as new SQLDatabaseMBS

// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibrarySQLite, getfolderitem("/usr/lib/libsqlite3.0.dylib", folderitem.PathTypeShell)

// connect to database
// in this example it is SQLite,
// but can also be Sybase, Oracle, Informix, DB2, SQLServer, InterBase, MySQL, SQLBase and ODBC

Var path as string

if TargetMacOS then
  path = "/tmp/test.db" // put the database in the temporary folder
else
  path = "test.db" // for Windows and Linux in the current folder the application is inside.
end if

db.DatabaseName = "sqlite:"+path

if db.Connect then
  
  MsgBox "We are connected!"
  
  // Disconnect is optional
  // autodisconnect will ocur in destructor if needed
  db.close
  
  msgbox "We are disconnected!"
end if

• 4 events
  • event DidConnect
  • event PostgresNotification(NotificationName as string, PID as Integer, Extras as String)
  • event Trace(traceInfo as Integer, SQL as string, Command as SQLCommandMBS)
  • event WillConnect
• 18 properties
  • property AutoCommit as Integer
  • property Client as Integer
  • property ClientVersion as Integer
  • Property Connection as SQLConnectionMBS
  • property isAlive as boolean
  • property isConnected as boolean
  • property IsolationLevel as Integer
  • property LastStatement as String
  • property Options as Dictionary
  • property RaiseExceptions as Boolean
  • property RowsAffected as Integer
  • property Scrollable as Boolean
  • property ServerVersion as Integer
  • property ServerVersionString as string
  • property SQLiteEncryptionKey as String
  • property Tag as Variant
  • property Option(name as string) as string
• 40 methods
  • method BeginTransaction
  • method CancelAllCommands
  • method Commands as SQLCommandMBS()
  • method Connect as boolean
  • method ConnectMT as Boolean
  • method Constructor(globals as SQLGlobalsMBS = nil)
  • method CubeSQLLastInsertID as Int64
  • method CubeSQLReceiveData(byref data as String, byref IsEndChunk as Boolean) as Boolean
  • method CubeSQLSendData(data as MemoryBlock)
  • method CubeSQLSendData(data as String)
  • method CubeSQLSendEndData
  • method InsertRecord(TableName as String, Record as Dictionary)
  • method Listen
  • method MySQLInsertID as Int64
  • method Prepare(statement as string) as SQLPreparedStatementMBS
  • method SetFileOption(name as string, file as folderitem)
  • method SQLExecute(ExecuteString as string, CommandType as Integer)
  • method SQLExecuteMT(ExecuteString as string, CommandType as Integer = 0)
  • method SQLiteBackupFinish(Backup as SQLite3BackupMBS) as integer
  • method SQLiteBackupInit(Dest as Variant, DestName as String, Source as Variant, SourceName as String) as SQLite3BackupMBS
  • method SQLiteBackupPageCount(Backup as SQLite3BackupMBS) as integer
  • method SQLiteBackupRemaining(Backup as SQLite3BackupMBS) as integer
  • method SQLiteBackupStep(Backup as SQLite3BackupMBS, Pages as Integer) as integer
  • method SQLiteConnectionHandle as Ptr
  • method SQLiteEnableLoadExtension(OnOff as boolean)
  • method SQLiteLastInsertRowID as Int64
  • method SQLiteLibVersion as String
  • method SQLiteLoadExtension(file as FolderItem, ByRef ErrorMessage as String) as Integer
  • method SQLiteLoadExtension(path as String, ByRef ErrorMessage as String) as Integer
  • method SQLiteMemoryHighwater(reset as boolean = false) as Int64
  • method SQLiteMemoryUsed as Int64
  • method SQLiteReKey(Key as String) as Integer
  • method SQLiteSetBusyHandler(MaxAttempts as Integer = 5)
  • method SQLiteSetBusyTimeout(TimeOutMS as Integer = 20)
  • method SQLiteSetKey(Key as String) as Integer
  • method SQLiteTableColumnMetaData(DBName as string, TableName as string, ColumnName as string, byref DataType as string, byref CollationSequence as string, byref NotNull as boolean, byref PrimaryKey as boolean, byref AutoIncrement as Boolean) as integer
  • method SQLiteThreadsafe as integer
  • method SQLSelect(SelectString as string, CommandType as Integer) as RecordSet
  • method SQLSelectMT(SelectString as string, CommandType as Integer = 0) as RecordSet
  • method UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
• 50 constants

Constants

Isolation Levels

Values for autocommit property

Command Types

Error Codes

Options to specify the library with SetFileOption

Super class Database

• 7 properties
  • property DatabaseName as String
  • property Error as Boolean
  • property ErrorCode as Integer
  • property ErrorMessage as String
  • property Host as String
  • property Password as String
  • property UserName as String
• 18 methods
  • method AddRow(TableName as String, row as DatabaseRow)
  • method AddRow(TableName as String, row as DatabaseRow, idColumnName as string = "") as Integer
  • method BeginTransaction
  • method Close
  • method Commit
  • method CommitTransaction
  • method Connect
  • method Connect as boolean
  • method ExecuteSQL(sql As String, ParamArray values As Variant)
  • method ExecuteSQL(sql As String, values() As Variant)
  • method InsertRecord(TableName as String, Data as DatabaseRecord)
  • method Prepare(statement as String) as PreparedSQLStatement
  • method Rollback
  • method RollbackTransaction
  • method SelectSQL(sql As String, ParamArray values As Variant) as RowSet
  • method SelectSQL(sql As String, values() As Variant) as RowSet
  • method SQLExecute(ExecuteString as string)
  • method SQLSelect(SelectString as string) as RecordSet

This class has no sub classes.

Some examples using this class:

• /SQL/SQLDatabaseMBS CubeSQL select version
• /SQL/SQLDatabaseMBS DuckDB
• /SQL/SQLDatabaseMBS MSSQL Connect
• /SQL/SQLDatabaseMBS MySQL Connect
• /SQL/SQLDatabaseMBS PostgreSQL
• /SQL/SQLDatabaseMBS PostgreSQL Notify and Listen
• /SQL/SQLDatabaseMBS SQLite Connect in Memory Database
• /SQL/SQLDatabaseMBS SQLite Fetch values
• /SQL/SQLDatabaseMBS SQLite insert record
• /SQL/SQLDatabaseMBS SQLite select version

Blog Entries

• 15th birthday of MBS SQL Plugin
• Using MBS SQL Plugin with PostgreSQL
• News from the MBS Xojo Plugins Version 21.1
• MonkeyBread Software Releases the MBS Xojo Plugins in version 21.1
• RowSet in MBS Xojo SQL Plugin
• Converting Xojo project to use MBS SQL Plugin
• Problems with killing Xojo threads with plugin calls.
• MBS Real Studio SQL Plugin and Oracle
• MBS Releases the MBS Real Studio Web Starter Kit in version 1.0
• Tip of the day: SQLite Cache

Xojo Developer Magazine

• 21.1, page 9: News
• 21.1, page 26: News from MBS Xojo Plugins, What's up with MonkeyBread Software by Stefanie Juchmes
• 18.5, page 10: News
• 14.1, pages 24 to 30: The MBS SQL Plugin, An alternative way to connect to databases by Christian Schmitz
• 13.4, page 11: News
• 12.3, page 10: News
• 11.6, page 40: ChartPart 3.0, Create charts and graphs within your Xojo applications by Kevin Cully
• 11.3, page 11: News
• 11.2, page 33: Windows Installing, Using Inno Setup to Create a Windows Installer App by Marc Zeedar
• 11.1, page 9: News

Videos

• MBS SQL Plugin Presentation
• Presentation from Munich conference about MBS Plugins.

Release notes

• Version 24.5
  • Added MYSQL_OPT_LOCAL_INFILE option for SQLConnectionMBS and SQLDatabaseMBS classes for MySQL connections.
• Version 24.3
  • Added AddRow() variant to SQLDatabaseMBS with returning insert id.
• Version 24.0
  • Added checks for SQLDatabaseMBS class to raise an exception if you try to close, SQLExecute or SQLSelect while a background thread runs a SQL query.
• Version 23.5
  • Added RowsAffected property to SQLConnectionMBS and SQLDatabaseMBS classes.
• Version 23.0
  • Fixed a problem with an OutOfBoundsException in Connect method of SQLDatabaseMBS class.
• Version 22.3
  • Added "MariaDB:" prefix for SQLDatabaseMBS class.
• Version 22.2
  • Added option for SQLDatabaseMBS and SQLConnectionMBS to enable quotes around automatic generated INSERT statements: db.Option("Quotes") = "yes".
  • Added SQLiteConnectionHandle method to SQLConnectionMBS and SQLDatabaseMBS classes.
• Version 22.1
  • Added BeginTransaction method for SQLDatabaseMBS to avoid SQL errors.
• Version 21.5
  • Added CacheInsertStatement flag for SQLDatabaseMBS to cache insert statements for InsertRecord to speed those up.

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

SQLConnectionMBS   -   SQLDataConsumerMBS