Platforms to show: All Mac Windows Linux Cross-Platform

Back to SQLDatabaseMBS class.

SQLDatabaseMBS.AutoCommit as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Whether autocommit is enabled or disabled for the current connection.
Example
Dim db as SQLDatabaseMBS // your database connection
db.AutoCommit = SQLDatabaseMBS.kAutoCommitOn

If autocommit is on, the database is committed automatically after each SQL command. Otherwise, transaction is committed only after Commit calling.
(Read and Write property)

SQLDatabaseMBS.Client as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The current DBMS client assigned for the connection.
Example
dim con as new SQLDatabaseMBS
db.client = SQLConnectionMBS.kSQLiteClient

Raises OutOfBoundsException exception if value parameter is out of range on setting.
With SQLDatabaseMBS, the client is usually taken from the DatabaseName property with a prefix, e.g. "ODBC:test".
(Read and Write property)

SQLDatabaseMBS.ClientVersion as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the DBMS client API version number.

The higher word contains the major client version (the XX value in the XX.YY version number); the lower word contains the minor client version (the YY value in the XX.YY version number).

If an DBMS client was not set calling ClientVersion method will throw an exception.
(Read only property)

SQLDatabaseMBS.Connection as SQLConnectionMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
Property SQL MBS SQL Plugin 9.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The connection for this database used in the background.

Note that methods on this connection object can raise exceptions while methods on the SQLDatabaseMBS class sets the error properties.
(Read only property)

SQLDatabaseMBS.isAlive as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns the database server connection status for a particular connection object.

Returns true if the database server is active and accessible; otherwise false.
This method uses the safe query execution for most supported DBMS-es. The query uses the well known database table or procedure (mysql_ping is used for MySQL or MariaDB). If the query fails the method returns false.
(Read only property)

SQLDatabaseMBS.isConnected as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns the connection state for a particular connection object.

Returns true if connected; otherwise false.
(Read only property)

SQLDatabaseMBS.IsolationLevel as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The transaction isolation level.

Use the kReadCommitted, kReadUncommitted, kRepeatableRead, kSerializable and kLevelUnknown constants.
(Read and Write property)

SQLDatabaseMBS.LastStatement as String

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 13.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The last executed SQL Statement.

(Read only property)

SQLDatabaseMBS.NativeAPI as Variant   Deprecated

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 16.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
This item is deprecated and should no longer be used.
Returns a set of functions of native DBMS client API.

Returns a SQLAPIMBS object.
Deprecated in version 19.5 in favor of direct methods in SQLDatabaseMBS and SQLConnectionMBS classes.
(Read only property)

SQLDatabaseMBS.Options as Dictionary

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 18.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a dictionary with all options.

For debugging, it may be useful to inspect options in debugger.
(Read only property)

SQLDatabaseMBS.RaiseExceptions as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Whether to raise exceptions.

Default is false which means we set error, ErrorCode and ErrorMessage properties and not raise SQLErrorExceptionMBS exception.
If you set to true, we do raise the exception and you have similar behavior as with SQLConnection class.
We recommend to use exceptions as they are not so easily ignored like an error property being true.
(Read and Write property)

SQLDatabaseMBS.RowsAffected as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 23.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns number of rows affected by last DML operation.

Returns the number of rows affected by the last insert/update/delete command execution.

Can also return the result set rows count, usually when the result set is cached at the client side.
(Read only property)

SQLDatabaseMBS.Scrollable as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 14.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Whether to make internally created SQLCommand objects scrollable.

Since plugin version 15.0, Scrollable is false by default.
(Read and Write property)

SQLDatabaseMBS.ServerVersion as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the currently connected DBMS server version number.

The higher word contains the major server version (the XX value in the XX.YY version number); the lower word contains the minor server version (the YY value in the XX.YY version number).
(Read only property)

SQLDatabaseMBS.ServerVersionString as string

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 12.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the currently connected DBMS server version string.

A server version string may contain some useful information about server brand, configuration and so on. It is a good idea to display this information in all your applications.
(Read only property)

SQLDatabaseMBS.SQLiteEncryptionKey as String

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 15.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The encryption key to use.
Example
// enable our built-in SQLite library, which supports encryption
Call InternalSQLiteLibraryMBS.Use

// where to store?
Dim f As FolderItem = SpecialFolder.Desktop.Child("test.db")
Dim storage_database As New SQLDatabaseMBS ' SQLiteDatabase
storage_database.SQLiteEncryptionKey = "aes256:password" ' <-- password with AES256 as prefix to pick algorithm
storage_database.DatabaseName = "sqlite:"+ f.NativePath

If storage_database.Connect Then

// create table if this is not yet here
storage_database.SQLExecute "Create table if not exists pics(pic_id integer PRIMARY KEY AUTOINCREMENT, name varchar(20), pic blob)"

// done
MsgBox "Ready"
Else
MsgBox storage_database.ErrorMessage
End If

This key is applied to the database after connecting. In case of an error, the plugin raises an exception. An empty key can be used for having no encryption.
Alternatively you can use SQLite3MBS.SetKey yourself.

The amount of key material actually used by the encryption extension depends on which variant of SEE you are using. With RC4, the first 256 byte of key are used. With the AES128, the first 16 bytes of the key are used. With AES256, the first 32 bytes of key are used.

If you specify a key that is shorter than the maximum key length, then the key material is repeated as many times as necessary to complete the key. If you specify a key that is larger than the maximum key length, then the excess key material is silently ignored.

The key must begin with an ASCII prefix to specify which algorithm to use. The prefix must be one of "rc4:", "aes128:", or "aes256:". The prefix is not used as part of the key sent into the encryption algorithm. So the real key should begin on the first byte after the prefix. If no prefix is given, we default to AES 128. To be compatible to Xojo, you can use AES128.

The string provided to the plugin is used with it's current encoding. So be sure you use right text encoding for what you want. e.g. using "Müller" as key in text encoding Windows ANSI will not open a database which used that key in UTF-8 encoding.

The Xojo database encryption in SQLiteDatabase class uses AES-128 OFB.
(Read and Write property)

SQLDatabaseMBS.Tag as Variant

Type Topic Plugin Version macOS Windows Linux iOS Targets
property SQL MBS SQL Plugin 14.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The tag property.

You can store here whatever you like.
(Read and Write property)

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


The biggest plugin in space...