Platforms to show: All Mac Windows Linux Cross-Platform

Previous items

SQLDatabaseMBS.SQLiteReKey(Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: You can change the key on a database using the Rekey Function.
Notes:
An empty key decrypts the database.

Rekeying requires that every page of the database file be read, decrypted, reencrypted with the new key, then written out again. Consequently, rekeying can take a long time on a larger database.

Most SEE variants allow you to encrypt an existing database that was created using the public domain version of SQLite. This is not possible when using the authenticating version of the encryption extension in see-aes128-ccm. If you do encrypt a database that was created with the public domain version of SQLite, no nonce will be used and the file will be vulnerable to a chosen-plaintext attach. If you call SetKey() immediately after Open when you are first creating the database, space will be reserved in the database for a nonce and the encryption will be much stronger. If you do not want to encrypt right away, call SetKey() anyway, with an empty key, and the space for the nonce will be reserved in the database even though no encryption is done initially.

A public domain version of the SQLite library can read and write an encrypted database with an empty key. You only need the encryption extension if the key is non-empty.

Returns a SQLite error code.

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLiteSetBusyHandler(MaxAttempts as Integer = 5)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: Installs busy handler for this connection.
Notes:
This routine sets a callback function that might be invoked whenever an attempt is made to open a database table that another thread or process has locked.

The plugin has an busy handler which will wait up to MaxAttemps and yield to other Xojo threads while waiting.
Passing 5 should wait up to 100ms.

There can only be a single busy handler defined for each [database connection]. Setting a new busy handler clears any previously set handler.) Note that calling SetBusyTimeout will also set or clear the busy handler.

The busy callback should not take any actions which modify the database connection that invoked the busy handler. Any such actions result in undefined behavior.

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLiteSetBusyTimeout(TimeOutMS as Integer = 20)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: This routine sets a busy handler that sleeps for a specified amount of time when a table is locked.
Notes:
The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. ^After at least "ms" milliseconds of sleeping, the handler returns 0 which causes SQLite query to return SQLite Busy or IO Blocked error.

Calling this routine with an argument less than or equal to zero turns off all busy handlers.

(There can only be a single busy handler for a particular database connection any any given moment. If another busy handler was defined (using SetBusyHandler prior to calling this routine, that other busy handler is cleared.)

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLiteSetKey(Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: Applies encryption to a database connection.
Notes:
Returns a SQLite error code.

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.

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.

Feedback, Comments & Corrections

SQLDatabaseMBS.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
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: Extract Metadata About A Column Of A Table
Notes:
Not available in all sqlite libraries!

This routine returns metadata about a specific column of a specific database table accessible using the database connection handle passed as the first function argument.
The column is identified by the second, third and fourth parameters to this function. The second parameter is either the name of the database (i.e. "main", "temp", or an attached database) containing the specified table or NULL. If it is NULL, then all attached databases are searched for the table using the same algorithm used by the database engine to resolve unqualified table references.
The third and fourth parameters to this function are the table and column name of the desired column, respectively. Neither of these parameters may be NULL.
Metadata is returned by writing to the memory locations passed as the 5th and subsequent parameters to this function. Any of these arguments may be NULL, in which case the corresponding element of metadata is omitted.

CollationSequence is assigned the Name of default collation sequence. NotNull is set to true if column has a NOT NULL constraint. PrimaryKey is set to true if column is part of the PRIMARY KEY and AutoIncrement is set to true if column is AUTOINCREMENT.

If the specified table is actually a view, an error code is returned.

If the specified column is "rowid", "oid" or "_rowid_" and an INTEGER PRIMARY KEY column has been explicitly declared, then the output parameters are set for the explicitly declared column. (If there is no explicitly declared INTEGER PRIMARY KEY column, then the output parameters are set as follows:

data type: "INTEGER"
collation sequence: "BINARY"
not null: false
primary key: true
auto increment: false

(This function may load one or more schemas from database files. If an error occurs during this process, or if the requested table or column cannot be found, an error code is returned and an error message left in the database connection (to be retrieved using ErrMessage).)

This API is only available if the library was compiled with the SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined.

Feedback, Comments & Corrections

SQLDatabaseMBS.SQLiteThreadsafe as integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 19.5 Yes Yes Yes Yes No
Function: Test To See If The Library Is Threadsafe.
Notes:
The threadsafe() function returns zero if and only if SQLite was compiled mutexing code omitted due to the SQLITE_THREADSAFE compile-time option being set to 0.

SQLite can be compiled with or without mutexes. When the SQLITE_THREADSAFE C preprocessor macro is 1 or 2, mutexes are enabled and SQLite is threadsafe. When the SQLITE_THREADSAFE macro is 0, the mutexes are omitted. Without the mutexes, it is not safe to use SQLite concurrently from more than one thread.

Enabling mutexes incurs a measurable performance penalty. So if speed is of utmost importance, it makes sense to disable the mutexes. But for maximum safety, mutexes should be enabled. The default behavior is for mutexes to be enabled.

This interface can be used by an application to make sure that the version of SQLite that it is linking against was compiled with the desired setting of the SQLITE_THREADSAFE macro.

This interface only reports on the compile-time mutex setting of the SQLITE_THREADSAFE flag. If SQLite is compiled with SQLITE_THREADSAFE=1 or =2 then mutexes are enabled by default but can be fully or partially disabled using a call to sqlite3_config() with the verbs SQLITE_CONFIG_SINGLETHREAD, SQLITE_CONFIG_MULTITHREAD, or SQLITE_CONFIG_MUTEX. ^(The return value of the sqlite3_threadsafe() function shows only the compile-time setting of thread safety, not any run-time changes to that setting made by sqlite3_config(). In other words, the return value from sqlite3_threadsafe() is unchanged by calls to sqlite3_config().)^

See the threading mode documentation for additional information.

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

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

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

Previous items

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

The biggest plugin in space...




Links
MBS Xojo tutorial videos