Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Platforms to show: All Mac Windows Linux Cross-Platform

SQLConnectionMBS.Commit
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
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.

Feedback, Comments & Corrections

SQLConnectionMBS.Connect(DBString as string, UserID as string, Password as string, client as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Opens the connection to a data source.
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)
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.

For Firebird, if you connect to a database and you have 32/64bit mismatch, you get error number 3.

Feedback, Comments & Corrections

SQLConnectionMBS.ConnectMT(DBString as string, UserID as string, Password as string, client as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.4 Yes Yes Yes Yes No
Function: Opens the connection to a data source.
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 (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) thread, so your GUI stays responsive.

The MT method will not trigger WillConnect and DidConnect events.

Feedback, Comments & Corrections

SQLConnectionMBS.Disconnect
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Disconnects the connection from the database.

Feedback, Comments & Corrections

SQLConnectionMBS.InsertRecord(TableName as String, Record as Dictionary)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 17.5 Yes Yes Yes Yes No
Function: Convenience function to insert a record.
Notes:
The plugin builds for you SQL statement with prepared statement and runs the insert command with values.
Please note that it does not work if field names contain spaces.

Feedback, Comments & Corrections

SQLConnectionMBS.Listen
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.1 Yes Yes Yes Yes No
Function: Start listening for notifications.
Notes:
Works only for PostgresSQL Client.
Please set client or connect before calling this method.

Feedback, Comments & Corrections

SQLConnectionMBS.Option(name as string) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
property SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: A string value of a specific connection or command option.
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"
Notes:
see also:
http://www.sqlapi.com/OnLineDoc/Connection_Option.html
(Read and Write computed property)

Feedback, Comments & Corrections

SQLConnectionMBS.Rollback
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
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.

Feedback, Comments & Corrections

SQLConnectionMBS.SetFileOption(name as string, file as folderitem)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.5 Yes Yes Yes Yes No
Function: Sets an option with passing a file path.
Example:
dim db as new SQLConnectionMBS

// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibraryMySQL, SpecialFolder.UserHome.Child("libmysqlclient.dylib")
Notes: Makes sure the path is correct and you have a 32bit library. 64 bit libraries will not work with Real Studio.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLExecute(command as string, CommandType as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.2 Yes Yes Yes Yes No
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.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLExecuteMT(command as string, CommandType as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.4 Yes Yes Yes Yes No
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 (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) 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.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelect(command as string, CommandType as Integer = 0) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.2 Yes Yes Yes Yes No
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.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectAsRecordSet(command as string, CommandType as Integer = 0) as RecordSet
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.0 Yes Yes Yes Yes No
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 Real Studio 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.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectAsRecordSetMT(command as string, CommandType as Integer = 0) as RecordSet
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.0 Yes Yes Yes Yes No
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 (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) thread, so your GUI stays responsive.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Real Studio 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.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectMT(command as string, CommandType as Integer = 0) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.4 Yes Yes Yes Yes No
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 (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) 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.

Feedback, Comments & Corrections

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




Links
MBS Xojo Plugins