Platforms to show: All Mac Windows Linux Cross-Platform

Previous items

SQLConnectionMBS.SQLiteMemoryHighwater(reset as boolean = false) as Int64
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Function: Queries maximum memory usage so far.
Notes: Can be reset with reset parameter being true.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLiteMemoryUsed as Int64
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Function: Queries memory in use by SQLite.
Notes:
This is memory allocated, but not yet freed.
Value is zero until SQLite3 initialized.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLiteReKey(Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

SQLConnectionMBS.SQLiteSetBusyHandler(MaxAttempts as Integer = 5)
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

SQLConnectionMBS.SQLiteSetBusyTimeout(TimeOutMS as Integer = 20)
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

SQLConnectionMBS.SQLiteSetKey(Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

SQLConnectionMBS.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 iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

SQLConnectionMBS.SQLiteThreadsafe as integer
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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

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

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectAsRecordSetMT(command as string, CommandType as Integer = 0) as RecordSet
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 13.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectAsRowSet(command as string, CommandType as integer = 0) as RowSet
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 21.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Function: Executes a SQL command and returns the result as RowSet 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 RowSet functionality.

If Scrollable property is true, the RowSet will be requested to be scrollable.

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

For Xojo 2019r2 and newer. See SQLSelectAsRecordSet for older versions.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectAsRowSetMT(command as string, CommandType as integer = 0) as RowSet
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 21.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Function: Executes a SQL command and returns the result as RowSet 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 RowSet functionality.

If Scrollable property is true, the RowSet will be requested to be scrollable.

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

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

For Xojo 2019r2 and newer. See SQLSelectAsRecordSetMT for older versions.

Feedback, Comments & Corrections

SQLConnectionMBS.SQLSelectMT(command as string, CommandType as Integer = 0) as string
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 10.4 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

Feedback, Comments & Corrections

SQLConnectionMBS.UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 18.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Function: Convenience function to update a record.
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))
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.

You can put multiple field names in the keys dictionary.

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 FileMaker blog

Start Chat