Xojo Conferences
MBSSep2018MunichDE
XDCMay2019MiamiUSA

Platforms to show: All Mac Windows Linux Cross-Platform

SQLDatabaseMBS.Connect as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Connects to the database.
Example:
dim db as new SQLDatabaseMBS

// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibraryMySQL, SpecialFolder.UserHome.Child("libmysqlclient.dylib")

// connect to database
// in this example it is MySQL,
// but can also be Sybase, Informix, Oracle, DB2
// SQLServer, InterBase, SQLBase and ODBC

db.DatabaseName="mysql:192.168.1.80:3306@test"

db.UserName="root"
db.Password=""

// or postgreSQL with timeout and ssl mode
db.DatabaseName="PostgreSQL:127.0.0.1,5432@dbname=postgres connection_timeout=10 sslmode=require"

if db.Connect then

MsgBox "We are connected!"

MsgBox "Server Version: "+db.Connection.ServerVersionString

// Disconnect is optional
// autodisconnect will ocur in destructor if needed

else
MsgBox db.ErrorMessage
end if
Notes:
Returns true on success and false on failure.

Please set the DatabaseName, UserName and Password properties. The Host property is ignored.
The database name must contain the complete information and a prefix for the kind of database.

Use this prefixes: "CubeSQL:", "SQLAnywhere:", "ODBC:", "Oracle:", "SQLServer:", "Firebird:", "InterBase:", "SQLBase:", "DB2:", "Informix:", "Sybase:", "MySQL:", "PostgreSQL:" or "SQLite:".

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

SQLDatabaseMBS.ConnectMT as Boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.4 Yes Yes Yes Yes No
Function: Connects to the database.
Notes:
Returns true on success and false on failure.

Please set the DatabaseName, UserName and Password properties. The Host property is ignored.
The database name must contain the complete information and a prefix for the kind of database.

Use this prefixes: "CubeSQL:", "SQLAnywhere:", "ODBC:", "Oracle:", "SQLServer:", "Firebird:", "InterBase:", "SQLBase:", "DB2:", "Informix:", "Sybase:", "MySQL:", "PostgreSQL:" or "SQLite:".

For IPv6 we changed plugin to use , instead of : for the port separator. So please use , to separate port from IP or host.

Same as Connect, but if you run this on a thread, the plugin gives time to other threads so the rest of your application runs just fine.

The MT method will not trigger WillConnect and DidConnect events.

Feedback, Comments & Corrections

SQLDatabaseMBS.Constructor(globals as SQLGlobalsMBS = nil)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.2 Yes Yes Yes Yes No
Function: The constructor.
Notes: Please don't call this directly as it's called automatically with using new command.

Feedback, Comments & Corrections

SQLDatabaseMBS.InsertRecord(TableName as String, Record as Dictionary)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 18.3 Yes Yes Yes Yes No
Function: Convenience function to insert a record.
Example:
dim db as SQLDatabaseMBS // your database connection

dim d as new Dictionary

d.Value("ID")=2
d.Value("text")="test insert"
d.Value("other")="Just a test"

db.InsertRecord("test_tbl", d)

if db.Error then
MsgBox db.ErrorMessage
end if
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.

Feedback, Comments & Corrections

SQLDatabaseMBS.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

SQLDatabaseMBS.Option(name as string) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
property SQL MBS SQL Plugin 10.5 Yes Yes Yes Yes No
Function: Sets an option for the connection.
Example:
dim c as SQLDatabaseMBS // your database 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"

// turn on auto cache
c.Option("AutoCache") = "true"

// set connection timeout for ODBC:
c.Option("SQL_ATTR_CONNECTION_TIMEOUT") = "10"
Notes: (Read and Write computed property)

Feedback, Comments & Corrections

SQLDatabaseMBS.Prepare(statement as string) as SQLPreparedStatementMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.4 Yes Yes Yes Yes No
Function: Prepares a statement.
Notes:
Returns prepared statement or nil in case of error.
Please check ErrorMessage property for errors.

Feedback, Comments & Corrections

SQLDatabaseMBS.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 SQLDatabaseMBS

// 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

SQLDatabaseMBS.SQLExecute(ExecuteString as string, CommandType as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.2 Yes Yes Yes Yes No
Function: Runs the SQLExecute threaded.
Notes: Same as SQLExecute, but with additional CommandType parameter.

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLExecuteMT(ExecuteString as string, CommandType as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.5 Yes Yes Yes Yes No
Function: Runs the SQLExecute threaded.
Notes: Same as SQLExecute, but if you run this on a thread, the plugin gives time to other threads so the rest of your application runs just fine.

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLSelect(SelectString as string, CommandType as Integer) as RecordSet
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.2 Yes Yes Yes Yes No
Function: Runs the SQLSelect threaded.
Notes:
Same as SQLSelect, but with additional CommandType parameter.

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

SQLDatabaseMBS.SQLSelectMT(SelectString as string, CommandType as Integer = 0) as RecordSet
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.5 Yes Yes Yes Yes No
Function: Runs the SQLSelect threaded.
Notes:
Same as SQLSelect, but if you run this on a thread, the plugin gives time to other threads so the rest of your application runs just fine.

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

SQLDatabaseMBS.UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 18.3 Yes Yes Yes Yes No
Function: Convenience function to update a record.
Example:
dim db as SQLDatabaseMBS // your database connection

dim d as new Dictionary

d.Value("text")="new text"
d.Value("other")="second value"

db.UpdateRecord("test_tbl", d, new dictionary("ID":2))

if db.Error then
MsgBox db.ErrorMessage
end if
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.

Feedback, Comments & Corrections

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





Links
MBS FileMaker tutorial videos