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.

Blog Entries

• MBS Xojo Plugins, version 25.2pr3
• News from the MBS Xojo Plugins Version 23.5
• MonkeyBread Software Releases the MBS Xojo Plugins in version 23.5
• MBS SQL Plugin Tips and Tricks
• 12th birthday of MBS SQL Plugin
• Connecting to PostgreSQL with SSL and client certificate file in Xojo
• Move from REALSQLdatabase to SQLiteDatabase
• Microsoft SQL Server connectivity
• Big SQLite Cache for 64-bit in Xojo
• [ANN] MonkeyBread Software Releases the MBS Xojo / Real Studio plug-ins in version 14.3

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 25.2
  • Fixed BOF of RecordSet when used with our SQLDatabaseMBS class to report BOF = true on the first row of the result set.
• Version 25.1
  • Fixed memory leak in SQLDatabaseMBS where tag value was not freed in destructor.
• 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.

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

Some related classes:

• class Database
• class SQLCommandMBS
• class SQLConnectionMBS
• class SQLGlobalsMBS
• class SQLite3BackupMBS
• class SQLPreparedStatementMBS

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

SQLConnectionMBS   -   SQLDataConsumerMBS