Super class: Database
|Type||Topic||Plugin||Version||macOS||Windows||Linux||Console & Web||iOS|
|class||SQL||MBS SQL Plugin||9.3||Yes||Yes||Yes||Yes||No|
dim 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
dim path as string
if TargetMacOS then
path = "/tmp/test.db" // put the database in the temporary folder
path = "test.db" // for Windows and Linux in the current folder the application is inside.
db.DatabaseName = "sqlite:"+path
if db.Connect then
MsgBox "We are connected!"
// Disconnect is optional
// autodisconnect will ocur in destructor if needed
msgbox "We are disconnected!"
You can use the SQL plugin without using Xojo built in database classes if you use the SQLConnectionMBS and SQLCommandMBS classes.
Or you use the SQLDatabaseMBS class which is a subclass of the database class and can be used with Xojo's RecordSet class. The current implementation is not complete. You can connect with passing the database URL in the DatabaseName property of the SQLDatabaseMBS class. You prefix this URL with the database type you are using.
You can use Execute and Select to run SQL statements. Errors can be queries with the lasterror properties. For the RecordSet, you can get the column count, the column names and values and move to the next row. All the other methods like deleting a record or updating a value are not implemented and you need to use SQL commands to do this.
Supported databases: CubeSQL, Centura SQLBase, DB2, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase.
Connect to Microsoft Access, FileMaker Server (or Pro), Microsoft Visual FoxPro and others via ODBC.
As field and table schema functions are not implemented, you can't use this database with the database browser features in the Xojo IDE.
The plugin does not provide RecordCount on RecordSet class. For that you need to make a extra SELECT count(*) query.
With Xojo 2013r1, you only need a database server license from Xojo, Inc. if you use the SQLDatabaseMBS class. The SQLConnectionMBS class does not require this license. But some features like getting a recordset do need the license as they refer to the SQLDatabaseMBS class.
Please free all RecordSets and SQLCommand objects before you close the SQLConnection or the SQLDatabase. The plugin keeps references from RecordSets and SQLCommand to prevent automatic destruction of the database connection. If you close a database connection while you have RecordSets and SQLCommand in use, things may go wrong.
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.
You can use InternalPostgreSQLLibraryMBS or InternalSQLiteLibraryMBS if you like to use our built in SQLite or PostgreSQL database libraries.
The class pings the database every minute by checking whether it's alive and to avoid server dropping connection. This can be disabled by setting Option("Ping") = "false". Ping is not used for SQLite.
Subclass of the Database class.
- 4 events
- 17 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 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
- 37 methods
- method CancelAllCommands
- method Commands as SQLCommandMBS()
- method Connect as boolean
- method ConnectMT as Boolean
- method Constructor(globals as SQLGlobalsMBS = nil)
- 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 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)
- 48 constants
ANSI Level 0
ANSI Level 1
ANSI Level 2
ANSI Level 3
Values for autocommit property
Autocommit is off.
Autocommit is on.
Command is an SQL statement.
Command is an SQL statement that mustn't be interpreted by SQLAPI.
Command is a stored procedure or a function.
Used by default. Library detects command type automatically.
Bind variable not found.
Initialization failed for client.
Client not set.
Unsupported client type for this platform.
Library file is too old.
Field not found.
Failed to query library version.
Failed to load a library. For example path could be wrong or 32/64bit mismatch.
Out of memory.
Unknown column type.
Unknown data type.
Unknown parameter type.
Failed to convert a value, e.g. string to number.
Can't convert text to date.
Options to specify the library with SetFileOption
for MySQL. Library extension on Mac is ".dylib", on Linux ".so" and on Windows ".dll". You get this library with the MySQL download on their homepage.
for Postgre SQL.
for SQL Anywhere.
for SQLite. Can also be the spatialite or SQLcipher library.
Super class Database
- 7 properties
- 17 methods
- method AddRow(TableName as String, row as DatabaseRow)
- 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 prepared statement
- /SQL/SQLDatabaseMBS Firebird Connect
- /SQL/SQLDatabaseMBS Microsoft SQL Connect
- /SQL/SQLDatabaseMBS Microsoft SQL Connect and query version
- /SQL/SQLDatabaseMBS Microsoft SQL cross platform
- /SQL/SQLDatabaseMBS MSSQL Execute
- /SQL/SQLDatabaseMBS SQL Anywhere Connect
- /SQL/SQLDatabaseMBS SQLite Connect in Memory Database
- /SQL/SQLDatabaseMBS SQLite ExecuteSQL
- /SQL/Web app/with SQLDatabaseMBS
- Move from REALSQLdatabase to SQLiteDatabase
- CubeSQL support for MBS Xojo SQL Plugin
- MBS Xojo Plugins, version 18.1pr1
- Big SQLite Cache for 64-bit in Xojo
- MBS Xojo / Real Studio Plugins, version 14.5pr2
- MBS Xojo / Real Studio Plugins, version 14.0pr4
- MBS Xojo / Real Studio Plugins, version 13.2pr11
- MBS Real Studio Plugins, version 12.5pr13
- MBS Real Studio Plugins, version 12.3pr16
- MBS REALbasic Plugins, version 10.5pr2
Xojo Developer Magazine
- 18.5, page 8
- 14.1, page 11: News
- 13.4, page 11: News
- 12.4, page 24: Writing OS X Apps for Game Center, Getting started by authenticating the local player with the Game Center Sandbox testing facility by Tom Baumgartner
- 12.3, page 9: News
- 11.6, page 10: News
- 11.3, page 9: News
- 11.2, page 40: Windows Installing, Using Inno Setup to Create a Windows Installer App by Marc Zeedar
- 11.1, page 9: News
The items on this page are in the following plugins: MBS SQL Plugin.
MBS Xojo blog