Platforms to show: All Mac Windows Linux Cross-Platform
SQLConnectionMBS.CancelAllCommands
Function:
Cancel all commands for the connection.
Notes: This loops over the list of commands associated with this connection and calls Cancel on them.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 18.4 | Yes | Yes | Yes | Yes | No |
Notes: This loops over the list of commands associated with this connection and calls Cancel on them.
SQLConnectionMBS.Commands as SQLCommandMBS()
Function:
Queries list of all command objects related to the connection.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 18.4 | Yes | Yes | Yes | Yes | No |
SQLConnectionMBS.Commit
Function:
Saves any changes and ends the current transaction.
Notes:
Use Commit method to write transaction changes permanently to a database. It commits the work of all commands that associated with that connection.
All changes to the database since the last commit are made permanent and cannot be undone. Before a commit, all changes made since the start of the transaction can be rolled back using Rollback method.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
Notes:
Use Commit method to write transaction changes permanently to a database. It commits the work of all commands that associated with that connection.
All changes to the database since the last commit are made permanent and cannot be undone. Before a commit, all changes made since the start of the transaction can be rolled back using Rollback method.
SQLConnectionMBS.Connect(DBString as string, UserID as string, Password as string, client as Integer = 0)
Function:
Opens the connection to a data source.
Example:
Notes:
Using the Connect method on a SAConnection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.
If you use the default value of Client parameter, you should set Client before using Connect.
To check whether a connection established use isConnected method. To check whether a connection is brocken or not use isAlive method.
see also for server specific notes:
http://www.sqlapi.com/OnLineDoc/Connection_Connect.html
For IPv6 we changed plugin to use , instead of : for the port separator. So please use , to separate port from IP or host.
For Firebird, if you connect to a database and you have 32/64bit mismatch, you get error number 3.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
Example:
dim con as SQLConnectionMBS// your connection
// some calls for MS SQL Server:
con.Connect("srv2@pubs","", "", SQLConnectionMBS.kSQLServerClient)
con.Connect("@pubs", "", "", SQLConnectionMBS.kSQLServerClient)
con.Connect("BEDLAM\SQL2005EX_EN@pubs", "", "", SQLConnectionMBS.kSQLServerClient)
con.Connect("BEDLAM\SQLEXPRESS@master", "", "", SQLConnectionMBS.kSQLServerClient)
// for MySQL:
con.Connect("192.168.1.80,3306@test","root","password", SQLConnectionMBS.kMySQLClient)
// for Postgre SQL:
con.Connect("somedb", "name", "password",SQLConnectionMBS.kPostgreSQLClient)
// with options
con.Connect("PostgreSQL:127.0.0.1,5432@dbname=postgres connection_timeout=10 sslmode=require", "name", "password",SQLConnectionMBS.kPostgreSQLClient)
// for SQLite:
con.Connect("/test.db", "", "",SQLConnectionMBS.kSQLiteClient)
DBString: | Name of database this connection will connect to (see Server specific notes). |
UserID: | A string containing a user name to use when establishing the connection (see Server specific notes). |
Password: | A string containing a password to use when establishing the connection. |
client: | Optional. One of the following values from k*Client constants. |
Using the Connect method on a SAConnection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.
If you use the default value of Client parameter, you should set Client before using Connect.
To check whether a connection established use isConnected method. To check whether a connection is brocken or not use isAlive method.
see also for server specific notes:
http://www.sqlapi.com/OnLineDoc/Connection_Connect.html
For IPv6 we changed plugin to use , instead of : for the port separator. So please use , to separate port from IP or host.
For Firebird, if you connect to a database and you have 32/64bit mismatch, you get error number 3.
SQLConnectionMBS.ConnectMT(DBString as string, UserID as string, Password as string, client as Integer = 0)
Function:
Opens the connection to a data source.
Notes:
Using the Connect method on a SAConnection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.
If you use the default value of Client parameter, you should set Client before using Connect.
To check whether a connection established use isConnected method. To check whether a connection is brocken or not use isAlive method.
see also for server specific notes:
http://www.sqlapi.com/OnLineDoc/Connection_Connect.html
For IPv6 we changed plugin to use , instead of : for the port separator. So please use , to separate port from IP or host.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
The MT method will not trigger WillConnect and DidConnect events.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 15.4 | Yes | Yes | Yes | Yes | No |
Notes:
DBString: | Name of database this connection will connect to (see Server specific notes). |
UserID: | A string containing a user name to use when establishing the connection (see Server specific notes). |
Password: | A string containing a password to use when establishing the connection. |
client: | Optional. One of the following values from k*Client constants. |
Using the Connect method on a SAConnection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.
If you use the default value of Client parameter, you should set Client before using Connect.
To check whether a connection established use isConnected method. To check whether a connection is brocken or not use isAlive method.
see also for server specific notes:
http://www.sqlapi.com/OnLineDoc/Connection_Connect.html
For IPv6 we changed plugin to use , instead of : for the port separator. So please use , to separate port from IP or host.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
The MT method will not trigger WillConnect and DidConnect events.
SQLConnectionMBS.Disconnect
Function:
Disconnects the connection from the database.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
SQLConnectionMBS.InsertRecord(TableName as String, Record as Dictionary)
Function:
Convenience function to insert a record.
Example:
Notes:
The plugin builds for you SQL statement with prepared statement and runs the insert command with values.
Lasterror is set or exception raised as with SQLExecute.
You can check statement via LastStatement property.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 17.5 | Yes | Yes | Yes | Yes | No |
Example:
dim con as SQLConnectionMBS // your database connection
dim d as new Dictionary
d.Value("ID")=2
d.Value("text")="test insert"
d.Value("other")="Just a test"
con.InsertRecord("test_tbl", d)
The plugin builds for you SQL statement with prepared statement and runs the insert command with values.
Lasterror is set or exception raised as with SQLExecute.
You can check statement via LastStatement property.
SQLConnectionMBS.Listen
Function:
Start listening for notifications.
Notes:
Works only for PostgresSQL Client.
Please set client or connect before calling this method.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 15.1 | Yes | Yes | Yes | Yes | No |
Notes:
Works only for PostgresSQL Client.
Please set client or connect before calling this method.
SQLConnectionMBS.Option(name as string) as string
Function:
A string value of a specific connection or command option.
Example:
Notes:
see also:
http://www.sqlapi.com/OnLineDoc/Connection_Option.html
(Read and Write computed property)
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
property | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
Example:
dim c as SQLConnectionMBS // your connection
// for Microsoft SQL use OLEDB, so you don't need native SQL drivers installed...
c.Option("UseAPI") = "OLEDB"
c.Option("SQLNCLI.LIBS") = "sqlsrv32.dll" // Library included in Windows Vista and newer
// for SQLite, set flag to open database file read only:
c.Option("SQLiteVFSFlags") = "1"
// set 10 seconds timeout for MySQL
c.Option("MYSQL_OPT_CONNECT_TIMEOUT") = "10"
// turn on auto cache
c.Option("AutoCache") = "true"
// set connection timeout for ODBC:
c.Option("SQL_ATTR_CONNECTION_TIMEOUT") = "10"
see also:
http://www.sqlapi.com/OnLineDoc/Connection_Option.html
(Read and Write computed property)
SQLConnectionMBS.Rollback
Function:
Cancels any changes made during the current transaction and ends the transaction.
Notes:
Rollback method rolls back the database to the state it was in at the completion of the last commit operation. All uncommitted work is undone.
Rollback method rolls back the work of all commands that associated with that connection.
To commit all changes made since the start of the transaction use Commit method.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 9.3 | Yes | Yes | Yes | Yes | No |
Notes:
Rollback method rolls back the database to the state it was in at the completion of the last commit operation. All uncommitted work is undone.
Rollback method rolls back the work of all commands that associated with that connection.
To commit all changes made since the start of the transaction use Commit method.
SQLConnectionMBS.SetFileOption(name as string, file as folderitem)
Function:
Sets an option with passing a file path.
Example:
Notes:
Makes sure the path is correct and you have a 32bit library. 64 bit libraries will not work with Xojo.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 10.5 | Yes | Yes | Yes | Yes | No |
Example:
dim db as new SQLConnectionMBS
// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibraryMySQL, SpecialFolder.UserHome.Child("libmysqlclient.dylib")
SQLConnectionMBS.SQLExecute(command as string, CommandType as Integer = 0)
Function:
Executes a SQL command and ignores result.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 10.2 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
SQLConnectionMBS.SQLExecuteMT(command as string, CommandType as Integer = 0)
Function:
Executes a SQL command and ignores result.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 10.4 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
SQLConnectionMBS.SQLSelect(command as string, CommandType as Integer = 0) as string
Function:
Executes a SQL command and returns the first field's string value.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
If the result is a record set, the first field from the first row is returned.
This is basicly useful for commands like "select sqlite_version()".
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 10.2 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
If the result is a record set, the first field from the first row is returned.
This is basicly useful for commands like "select sqlite_version()".
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
SQLConnectionMBS.SQLSelectAsRecordSet(command as string, CommandType as Integer = 0) as RecordSet
Function:
Executes a SQL command and returns the result as RecordSet object.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.
If Scrollable property is true, the recordset will be requested to be scrollable.
The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 13.0 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.
If Scrollable property is true, the recordset will be requested to be scrollable.
The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.
SQLConnectionMBS.SQLSelectAsRecordSetMT(command as string, CommandType as Integer = 0) as RecordSet
Function:
Executes a SQL command and returns the result as RecordSet object.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.
If Scrollable property is true, the recordset will be requested to be scrollable.
The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 13.0 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.
If Scrollable property is true, the recordset will be requested to be scrollable.
The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.
SQLConnectionMBS.SQLSelectMT(command as string, CommandType as Integer = 0) as string
Function:
Executes a SQL command and returns the first field's string value.
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
If the result is a record set, the first field from the first row is returned.
This is basicly useful for commands like "select sqlite_version()".
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 10.4 | Yes | Yes | Yes | Yes | No |
Notes:
This is a convenience function.
Internally it creates a SQLCommandMBS with the given command and calls Execute.
If the result is a record set, the first field from the first row is returned.
This is basicly useful for commands like "select sqlite_version()".
The work is performed on an extra thread, so this function can yield time to other Xojo (Xojo) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Xojo) thread, so your GUI stays responsive.
All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.
SQLConnectionMBS.UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
Function:
Convenience function to update a record.
Example:
Notes:
The plugin builds for you SQL statement with prepared statement and runs the update command with given values for records with given key values.
Lasterror is set or exception raised as with SQLExecute.
You can check statement via LastStatement property.
Type | Topic | Plugin | Version | macOS | Windows | Linux | Console & Web | iOS |
method | SQL | MBS SQL Plugin | 18.3 | Yes | Yes | Yes | Yes | No |
Example:
dim con as SQLConnectionMBS // your database connection
dim d as new Dictionary
d.Value("text")="new text"
d.Value("other")="second value"
con.UpdateRecord("test_tbl", d, new dictionary("ID":2))
The plugin builds for you SQL statement with prepared statement and runs the update command with given values for records with given key values.
Lasterror is set or exception raised as with SQLExecute.
You can check statement via LastStatement property.
The items on this page are in the following plugins: MBS SQL Plugin.
Links
MBS Xojo Plugins