|class||SQL||MBS SQL Plugin||9.3||✅ Yes||✅ Yes||✅ Yes||✅ Yes||All|
dim con as new SQLConnectionMBS
// where is the library?
con.SetFileOption con.kOptionLibraryMySQL, SpecialFolder.UserHome.Child("libmysqlclient.dylib")
// connect to database
// in this example it is Oracle,
// but can also be Sybase, Informix, DB2
// SQLServer, InterBase, SQLBase and ODBC
dim server as string = "192.168.1.80,3306@test"
MsgBox "We are connected!"
// Disconnect is optional
// autodisconnect will ocur in destructor if needed
msgbox "We are disconnected!"
catch r as RuntimeException
// can also throw an exception
// (if a network error for example),
// we will be ready
// on error rollback changes
catch rr as runtimeexception
Supported databases: CubeSQL, Centura SQLBase, DB2, DuckDB, 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.
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.
MBS Database connections are implemented via SQLConnectionMBS and SQLCommandMBS classes. We provide a thin layer on top with SQLDatabaseMBS class to make it compatible to the Xojo database class. And when you use SQLDatabaseMBS, you can always get the matching SQLConnectionMBS objet via Connection property. Instead of SQLCommandMBS class, you may just use SelectSQL/ExecuteSQL or older SQLSelect/SQLExecute functions.
We have a collection of library files here:
- 5 events
- 20 properties
- Property AutoCommit as Integer
- Property Client as Integer
- Property ClientVersion as Integer
- Property Error as Boolean
- Property ErrorCode as Integer
- Property ErrorMessage as string
- 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 New in 23.5
- 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
- 2 shared properties
- 45 methods
- method BeginTransaction
- method CancelAllCommands
- method Commands as SQLCommandMBS()
- method Commit
- method Connect(DBString as string, UserID as string, Password as string, client as Integer = 0)
- method ConnectMT(DBString as string, UserID as string, Password as string, client as Integer = 0)
- 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 Disconnect
- method InsertRecord(TableName as String, Record as Dictionary)
- method Listen
- method MySQLInsertID as Int64
- method Rollback
- method SetFileOption(name as string, file as folderitem)
- method SQLExecute(command as string, CommandType as Integer = 0)
- method SQLExecuteMT(command 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(command as string, CommandType as Integer = 0) as string
- method SQLSelectAsRecordSet(command as string, CommandType as Integer = 0) as RecordSet
- method SQLSelectAsRecordSetMT(command as string, CommandType as Integer = 0) as RecordSet
- method SQLSelectAsRowSet(command as string, CommandType as integer = 0) as RowSet
- method SQLSelectAsRowSetMT(command as string, CommandType as integer = 0) as RowSet
- method SQLSelectMT(command as string, CommandType as Integer = 0) as string
- method UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
- 66 constants
This class has no sub classes.
Some methods using this class:
- SQLCommandMBS.Constructor(connection as SQLConnectionMBS, SQLCommand as String, CommandType as Integer = 0)
Some properties using for this class:
Some events using this class:
- SQLGlobalsMBS.Trace(traceInfo as Integer, SQL as string, Connection as SQLConnectionMBS, Command as SQLCommandMBS)
Some examples using this class:
- /SQL/DB2 Connect
- /SQL/Firebird Query
- /SQL/Microsoft SQL via ODBC
- /SQL/SQLDatabaseMBS MySQL Fetch values
- /SQL/SQLDatabaseMBS Oracle Connect
- /SQL/SQLDatabaseMBS SQLite Encryption Fetch values
- /SQL/SQLDatabaseMBS SQLite insert record
- /SQL/SQLite Connect in Memory Database
- /SQL/SQLite Display Schema
- /SQL/SQLite ExecuteSQL
- MonkeyBread Software Releases the MBS Xojo Plugins in version 21.1
- RowSet in MBS Xojo SQL Plugin
- Crossplatform connection to Microsoft SQL Server in Xojo
- CubeSQL support for MBS Xojo SQL Plugin
- Big SQLite Cache for 64-bit in Xojo
- Connecting to Microsoft Visual FoxPro
- Wishes for little plug-in additions?
- Multithreaded plugin functions can increase speed of Real Studio application
- MonkeyBread Software Releases the MBS Real Studio plug-ins in version 13.0
- How to get SQL Plugin running with Postgre SQL on Windows?
Xojo Developer Magazine
- 16.1, page 10: News
- 14.1, pages 24 to 30: The MBS SQL Plugin, An alternative way to connect to databases by Christian Schmitz
- 12.4, page 9: News
The items on this page are in the following plugins: MBS SQL Plugin.