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

SQLite3MBS.BackupFinish(Backup as SQLite3BackupMBS) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Finishes a backup run.
Notes:
When BackupStep has returned kErrorDone, or when the application wishes to abandon the backup operation, the application should destroy the SQLite3BackupMBS by passing it to BackupFinish. The BackupFinish interfaces releases all resources associated with the SQLite3BackupMBS object. If BackupStep has not yet returned kErrorDone, then any active write-transaction on the destination database is rolled back. The SQLite3BackupMBS object is invalid and may not be used following a call to BackupFinish.
The value returned by BackupFinish is kErrorOK if no BackupStep errors occurred, regardless or whether or not BackupStep completed. If an out-of-memory condition or IO error occurred during any prior BackupStep call on the same SQLite3BackupMBS object, then BackupFinish returns the corresponding error code.
A return of kErrorBusy or kErrorLocked from BackupStep is not a permanent error and does not affect the return value of BackupFinish.

Feedback, Comments & Corrections

SQLite3MBS.BackupInit(Dest as SQLConnectionMBS, DestName as String, Source as SQLConnectionMBS, SourceName as String) as SQLite3BackupMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Initializes a backup.
Notes:
The backup API copies the content of one database into another. It is useful either for creating backups of databases or for copying in-memory databases to or from persistent files.

see also
http://www.sqlite.org/c3ref/backup_finish.html

Exclusive access is required to the destination database for the duration of the operation. However the source database is only read-locked while it is actually being read; it is not locked continuously for the entire backup operation. Thus, the backup may be performed on a live source database without preventing other users from reading or writing to the source database while the backup is underway.

To perform a backup operation:
  • BackupInit is called once to initialize the backup,
  • BackupStep is called one or more times to transfer the data between the two databases, and finally
  • BackupFinish is called to release all resources associated with the backup operation.
There should be exactly one call to BackupFinish for each successful call to BackupInit.

The D and N arguments to BackupInit(D,N,S,M) are the database connection associated with the destination database and the database name, respectively. The database name is "main" for the main database, "temp" for the temporary database, or the name specified after the AS keyword in an ATTACH statement for an attached database. The S and M arguments passed to BackupInit(D,N,S,M) identify the database connection and database name of the source database, respectively. The source and destination database connections (parameters S and D) must be different or else BackupInit(D,N,S,M) will file with an error.
If an error occurs within BackupInit(D,N,S,M), then nil is returned and an error code and error message are store3d in the destination database connection D. The error code and message for the failed call to BackupInit can be retrieved using the ErrCode and ErrMessage functions. A successful call to BackupInit returns a SQLite3BackupMBS object. The SQLite3BackupMBS object may be used with the BackupStep and BackupFinish functions to perform the specified backup operation.

Concurrent Usage of Database Handles
The source database connection may be used by the application for other purposes while a backup operation is underway or being initialized. If SQLite is compiled and configured to support threadsafe database connections, then the source database connection may be used concurrently from within other threads.
However, the application must guarantee that the destination database connection is not passed to any other API (by any thread) after BackupInit is called and before the corresponding call to BackupFinish. SQLite does not currently check to see if the application incorrectly accesses the destination database connection and so no error code is reported, but the operations may malfunction nevertheless. Use of the destination database connection while a backup is in progress might also also cause a mutex deadlock.
If running in shared cache mode, the application must guarantee that the shared cache used by the destination database is not accessed while the backup is running. In practice this means that the application must guarantee that the disk file being backed up to is not accessed by any connection within the process, not just the specific connection that was passed to BackupInit.
The SQLite3BackupMBS object itself is partially threadsafe. Multiple threads may safely make multiple concurrent calls to BackupStep. However, the BackupRemaining and BackupPageCount APIs are not strictly speaking threadsafe. If they are invoked at the same time as another thread is invoking BackupStep it is possible that they return invalid values.
*/

Feedback, Comments & Corrections

SQLite3MBS.BackupPageCount(Backup as SQLite3BackupMBS) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Returns the number of pages in total.
Notes:
Each call to BackupStep sets two values inside the SQLite3BackupMBS object: the number of pages still to be backed up and the total number of pages in the source databae file. The BackupRemaining and BackupPageCount interfaces retrieve these two values, respectively.
The values returned by these functions are only updated by BackupStep. If the source database is modified during a backup operation, then the values are not updated to account for any extra pages that need to be updated or the size of the source database file changing.

Feedback, Comments & Corrections

SQLite3MBS.BackupRemaining(Backup as SQLite3BackupMBS) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Returns the number of pages remaining.
Notes:
Each call to BackupStep sets two values inside the SQLite3BackupMBS object: the number of pages still to be backed up and the total number of pages in the source databae file. The BackupRemaining and BackupPageCount interfaces retrieve these two values, respectively.
The values returned by these functions are only updated by BackupStep. If the source database is modified during a backup operation, then the values are not updated to account for any extra pages that need to be updated or the size of the source database file changing.

Feedback, Comments & Corrections

SQLite3MBS.BackupStep(Backup as SQLite3BackupMBS, Pages as Integer) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Copies up to Pages pages between the source and destination databases specified by SQLite3BackupMBS object.
Notes:
If N is negative, all remaining source pages are copied. If BackupStep(B,N) successfully copies N pages and there are still more pages to be copied, then the function resturns kErrorOK. If BackupStep(B,N) successfully finishes copying all pages from source to destination, then it returns kErrorDone. If an error occurs while running BackupStep(B,N), then an error code is returned. As well as kErrorOK and kErrorDone, a call to BackupStep may return kErrorReadOnly, kErrorNoMem, kErrorBusy, kErrorLocked, or an kErrorIOACCESS | kErrorIOXXX extended error code.
The BackupStep might return kErrorReadOnly if the destination database was opened read-only or if the destination is an in-memory database with a different page size from the source database.
If BackupStep cannot obtain a required file-system lock, then the sqlite3_busy_handler | busy-handler function is invoked (if one is specified). If the busy-handler returns non-zero before the lock is available, then kErrorBusy is returned to the caller. In this case the call to BackupStep can be retried later. If the source database connection is being used to write to the source database when BackupStep is called, then kErrorLocked is returned immediately. Again, in this case the call to BackupStep can be retried later on. (If kErrorIOACCESS | kErrorIOXXX, kErrorNoMem, or kErrorReadOnly is returned, then there is no point in retrying the call to BackupStep. These errors are considered fatal.) The application must accept that the backup operation has failed and pass the backup operation handle to the BackupFinish to release associated resources.
The first call to BackupStep obtains an exclusive lock on the destination file. The exclusive lock is not released until either BackupFinish is called or the backup operation is complete and BackupStep returns kErrorDone. Every call to BackupStep obtains a shared lock on the source database that lasts for the duration of the BackupStep call. Because the source database is not locked between calls to BackupStep, the source database may be modified mid-way through the backup process. If the source database is modified by an external process or via a database connection other than the one being used by the backup operation, then the backup will be automatically restarted by the next call to BackupStep. If the source database is modified by the using the same database connection as is used by the backup operation, then the backup database is automatically updated at the same time.

Feedback, Comments & Corrections

SQLite3MBS.EnableLoadExtension(Conn as SQLConnectionMBS, OnOff as boolean)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 14.4 Yes Yes Yes Yes No
Function: Enables/disables extension loading for the given connection.

Feedback, Comments & Corrections

SQLite3MBS.ErrCode(Conn as SQLConnectionMBS) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: The ErrCode function returns the numeric result code or extended result code for the most recent failed sqlite3 API call associated with a database connection.
Notes: If a prior API call failed but the most recent API call succeeded, the return value from ErrCode is undefined.

Feedback, Comments & Corrections

SQLite3MBS.ErrMessage(Conn as SQLConnectionMBS) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Returns the most recent error message in english for the given connection.

Feedback, Comments & Corrections

SQLite3MBS.LastInsertRowID(Conn as SQLConnectionMBS) as Int64
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 Yes Yes Yes Yes No
Function: Returns Last Insert Rowid.
Notes:
Each entry in an SQLite table has a unique 64-bit signed integer key called the ROWID. The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned.

(If an INSERT occurs within a trigger, then the rowid of the inserted row is returned by this routine as long as the trigger is running. But once the trigger terminates, the value returned by this routine reverts to the last value inserted before the trigger fired.)

An INSERT that fails due to a constraint violation is not a successful INSERT and does not change the value returned by this routine. ^Thus INSERT OR FAIL, INSERT OR IGNORE, INSERT OR ROLLBACK, and INSERT OR ABORT make no changes to the return value of this routine when their insertion fails. ^(When INSERT OR REPLACE encounters a constraint violation, it does not fail. The INSERT continues to completion after deleting rows that caused the constraint problem so INSERT OR REPLACE will always change the return value of this interface.)^

For the purposes of this routine, an INSERT is considered to be successful even if it is subsequently rolled back.

This function is accessible to SQL statements via the last_insert_rowid() SQL function.

If a separate thread performs a new INSERT on the same database connection while the LastInsertRowID function is running and thus changes the last insert rowid, then the value returned by LastInsertRowID is unpredictable and might not equal either the old or the new last insert rowid.

Feedback, Comments & Corrections

SQLite3MBS.LoadExtension(Conn as SQLConnectionMBS, file as FolderItem, ByRef ErrorMessage as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.4 Yes Yes Yes Yes No
Function: Loads an SQLite extension library from the named file.
Notes:
The LoadExtension interface attempts to load an SQLite extension library contained in the file.

Returns kErrorOk on success and kErrorError if something goes wrong.
Extension loading must be enabled using EnableLoadExtension prior to calling this API, otherwise an error will be returned.

See also:

Feedback, Comments & Corrections

SQLite3MBS.LoadExtension(Conn as SQLConnectionMBS, path as String, ByRef ErrorMessage as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.4 Yes Yes Yes Yes No
Function: Loads an SQLite extension library from the named file.
Notes:
The LoadExtension interface attempts to load an SQLite extension library contained in the file.

Returns kErrorOk on success and kErrorError if something goes wrong.
Extension loading must be enabled using EnableLoadExtension prior to calling this API, otherwise an error will be returned.

See also:

Feedback, Comments & Corrections

SQLite3MBS.ReKey(Conn as SQLConnectionMBS, Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.3 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

SQLite3MBS.SetBusyHandler(Conn as SQLConnectionMBS, MaxAttempts as Integer = 5)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.1 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

SQLite3MBS.SetBusyTimeout(Conn as SQLConnectionMBS, TimeOutMS as Integer = 20)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.1 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

SQLite3MBS.SetKey(Conn as SQLConnectionMBS, Key as String) as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.3 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

SQLite3MBS.TableColumnMetaData(Conn as SQLConnectionMBS, 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 12.4 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

SQLite3MBS.Threadsafe as Integer
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 12.4 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

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




Links
MBS Xojo blog