Class

SQLiteDatabase

Description

The SQLiteDatabase class provides access to the SQLite data source, a.k.a., database engine or database back-end.

Properties

Name	Type	Read-Only
DatabaseFile	FolderItem
EncryptionKey	String
LibraryVersion	String	✓
LoadExtensions	Boolean
ThreadYieldInterval	Integer
Timeout	Double
WriteAheadLogging	Boolean

Methods

Name	Parameters	Returns
AddDatabase	file As FolderItem, databaseName As String, Optional password As String
AddRow	tableName As String, row As DatabaseRow
	tableName As String, row As DatabaseRow, idColumnName As String = ""	Integer
BackUp	destination As SQLiteDatabase, callbackHandler As SQLiteBackupInterface = Nil, sleepTimeInMilliseconds As Integer = 10
BeginTransaction
Close
CommitTransaction
Connect
Constructor
Constructor	dbFile As FolderItem
CreateBlob	tableName As String, columnName As String, row As UInt64, length As Integer, databaseName As String = ""	SQLiteBLOB
CreateDatabase
Decrypt
Encrypt	password As String
ExecuteSQL	command As String, Paramarray values() As Variant
IsConnected		Boolean
OpenBlob	tableName As String, columnName As String, row As UInt64, readWrite As Boolean, databaseName As String = ""	SQLiteBLOB
Prepare	statement As String	SQLitePreparedSQLStatement
RemoveDatabase	databaseName As String
RollbackTransaction
SelectSQL	query As String, Paramarray values() As Variant	RowSet
TableColumns	tableName As String	RowSet
TableIndexes	tableName As String	RowSet
Tables		RowSet

Property descriptions

SQLiteDatabase.DatabaseFile

DatabaseFile As FolderItem

Specifies the FolderItem for the SQLite database file. If DatabaseFile is Nil, calling the Connect method creates an in-memory database.

This code connect to an existing SQLite database:

Var db As New SQLiteDatabase

Var dbFile As FolderItem = FolderItem.ShowOpenFileDialog("")

If dbFile <> Nil And dbFile.Exists Then
  db.DatabaseFile = dbFile

  Try
    db.Connect
    MessageBox("Connected to database successfully!")
  Catch error As DatabaseException
    MessageBox("DB Connection Error: " + error.Message)
  End Try
End If

This code creates an in-memory database that you can use for storing temporary data:

Var db As New SQLiteDatabase

Try
  db.Connect
  ' Create tables and add data as necessary.
Catch error As DatabaseException
  MessageBox("DB Connection Error: " + error.Message)
End Try

SQLiteDatabase.EncryptionKey

EncryptionKey As String

Specifies the encryption key used to create or connect to an encrypted database.

Note

You cannot encrypt a database from within a transaction. You must encrypt it outside of any transactions.

To encrypt a new database, specify this value before calling CreateDatabase.

To connect to an encrypted database, specify this value before calling Database.Connect.

To encrypt an existing database, use the Encrypt method.

2017r3 and prior: AES-128 encryption is always used.

2018r1 and later defaults to AES-128, but AES-256 can also be used by including the prefix "aes256:" before the rest of the encryption key.

This code supplies EncryptionKey before attempting to connect to an encrypted database:

Var dbFile As New FolderItem("MyDB.sqlite")

Var db As New SQLiteDatabase
db.DatabaseFile = dbFile

db.EncryptionKey = "horse+20$"

Try
  db.Connect
  ' Key was correct; DB is connected
  MessageBox("Connected to database.")
Catch error As DatabaseException
  ' Connection error. This could be because the key is wrong or other reasons
  MessageBox("Connection error: " + error.Message)
End Try

SQLiteDatabase.LibraryVersion

LibraryVersion As String

Returns a String containing the version of SQLite used by Xojo.

This property is read-only.

Displays the SQLite version in use:

Var db As New SQLiteDatabase
MessageBox("SQLite version = " + db.LibraryVersion)

SQLiteDatabase.LoadExtensions

LoadExtensions As Boolean

The default is False. Set this to True before loading an SQLite extension. After loading the extension, this property gets reset to False.

Important

This method is not currently supported for Android.

This allows SQLite Runtime Loadable Extensions to be loaded.

To load an extension, execute an SQL command like this:

SELECT load_extension ('/path/to/extensionfile', 'sqlite3_extension_init')

More on loading extensions, e.g. the ICU library for unicode support, can be found on Thomas Tempelmann's Blog post

SQLiteDatabase.ThreadYieldInterval

ThreadYieldInterval As Integer

Yields time back to your application every N virtual machine instructions. The unit is virtual machine instructions.

Important

This method is not currently supported for Android.

A value of zero means disabled. Higher values mean less time to your applications and more time to SQLite. There is no "best" value to use, since one cannot know in advance how many virtual machine instructions are required in order to execute an SQL statement. If performance is a problem, the user should try different values and use among the ones that work well for their application.

In most situations you should not need to change this value from its default of 0.

This example sets the value of ThreadYieldInterval. Please keep in mind that users need to experiment to find a good value for their case.

Var dbFile As New FolderItem("MyDB.sqlite")

Var db As New SQLiteDatabase
db.DatabaseFile = dbFile

db.ThreadYieldInterval = 200

Try
  db.Connect
  ' Key was correct; DB is connected
  MessageBox("Connected to database.")
Catch error As DatabaseException
  MessageBox("Connection error: " + error.Message)
End Try

SQLiteDatabase.Timeout

Timeout As Double

The maximum number of seconds that an operation may wait before a lock is cleared (if any). The default is 10.

If an operation has to wait longer than this amount of seconds then the operation will fail (with a suitable SQL error).

If the value is less than or equal to 0 then all busy handlers will be disabled and the operation will wait indefinitely.

This value can be changed at any time.

This example sets the Timeout to 30 seconds.

Var dbFile As New FolderItem("MyDB.sqlite")

Var db As New SQLiteDatabase
db.DatabaseFile = dbFile

db.Timeout = 30

Try
  db.Connect
  ' Key was correct; DB is connected
  MessageBox("Connected to database.")
Catch error As DatabaseException
  MessageBox("Connection error: " + error.Message)
End Try

SQLiteDatabase.WriteAheadLogging

WriteAheadLogging As Boolean

Enables the SQLite Write-Ahead Logging (WAL) mode which can improve performance of database writes. This is especially useful when multiple users are writing to the database, as can be the case with web applications.

The SQLite organization does not recommend using SQLite on a network drive, even with WAL enabled. There is a high risk of database corruption. If your desktop app needs a multi-user database, it should use a database server.

If you want to use WAL, you need to set this property to True after connecting to the database by calling Connect.

WAL is faster than normal mode (called Journaled) because there is less disk writing. With WAL, a database change writes once to the write-ahead log. With Journaling, a database change writes to a rollback file and to the original database file.

Although faster, WAL does have some limitations:

WAL database cannot be opened in read-only mode
Rolling back large transaction (over 100MB) can be slow
Two extra files are created (*.sqlite-wal and *.sqlite-shm) alongside the main database file
The processes using the database must be on the same host computer
WAL does not work over a network filesystem

For more information about WAL, refer to the SQLite documentation: https://www.sqlite.org/wal.html.

This example sets WriteAheadLogging to True for a SQLite database:

Var dbFile As New FolderItem("MyDB.sqlite")

Var db As New SQLiteDatabase
db.DatabaseFile = dbFile
Try
  db.Connect
  db.WriteAheadLogging = True
  MessageBox("Connected to database.")
Catch error As DatabaseException
  MessageBox("Connection error: " + error.Message)
End Try

Method descriptions

SQLiteDatabase.AddDatabase

AddDatabase(file As FolderItem, databaseName As String, [password As String])

Adds the SQLite database referred to by file to the database object and names it databaseName.

Note

This cannot be used to attach an in-memory database.

When a database has been added, it is possible to do cross-database queries. In order to add an encrypted database, you must pass the correct password for the database to be attached.

You should prefix all SQL queries to tables in the added database with the databaseName.

If the database cannot be added, an IOException occurs.

Var f As FolderItem = SpecialFolder.Desktop.Child("MyDatabase")
Var db As New SQLiteDatabase
Try
  db.AddDatabase(f, "CompanyDatabase", "mylongpassword")
Catch error As IOException
  MessageBox("The database could not be attached.")
End Try

This example adds a database file to an existing, connected database, currentDB:

Var addDBFile As New FolderItem("AddDB.sqlite")

Try
  currentDB.AddDatabase(AddDBFile, "locations", "myverylongpassword") ' currentDB is already connected elsewhere
  MessageBox("Database attached.")
  ' Queries against the added database would be prefixed with "locations", such as
  ' SELECT City FROM locations.Addresses WHERE ST = 'TX'
Catch error As IOException
  MessageBox("The database could not be added.")
End Try

SQLiteDatabase.AddRow

AddRow(tableName As String, row As DatabaseRow)

Inserts row as a new row in TableName.

AddRow(tableName As String, row As DatabaseRow, idColumnName As String = "") As Integer

Inserts row as a new row in tableName returning the newly assigned ID in the idColumnName column.

Note

Returning the newly assigned ID is not currently supported for Android.

Always look for a DatabaseException to verify whether or not the data was added.

This code adds a row to an existing Team table with the columns "Name", "Coach" and "City". It's assumed that the variable db contains an active database connection:

Var row As New DatabaseRow
' ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"

Try
  db.AddRow("Team", row)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

SQLiteDatabase.BackUp

BackUp(destination As SQLiteDatabase, callbackHandler As SQLiteBackupInterface = Nil, sleepTimeInMilliseconds As Integer = 10)

Backs up the database asynchronously or synchronously. You must be connected to the destination database and its original contents (if any) will be overwritten by the backup.

Important

This method is not currently supported for Android.

Back up a previously connected database synchronously. This means you app will pause and wait for the backup to finish:

Var backupDBFile As FolderItem = FolderItem.ShowSaveFileDialog("", "backup.sqlite")

If backupDBFile <> Nil Then
  Var backupDB As New SQLiteDatabase
  backupDB.DatabaseFile = backupDBFile
  Try
    backupDB.CreateDatabase
    ' db As SQLiteDatabase must be declared elsewhere
    ' and connected to your SQLite database.
    db.BackUp(backupDB, Nil, -1)
    MessageBox("Backup finished!")
  Catch error As IOException
    MessageBox("Failed to create backup database. Error: " + error.Message)
  End Try
End If

Backing up the database asynchronously requires the use of a separate class that implements SQLiteBackupInterface.

Var backupFile As Folderitem
backupFile = FolderItem.ShowSaveFileDialog("", "backup.sqlite")

If backupFile Is Nil Then Return
  ' This is a property on the Window so that it stays in scope when the method exits
  mBackupDB = New SQLiteDatabase
  mBackupDB.DatabaseFile = backupFile
  Try
    mBackupDB.CreateDatabase Then
   ' This is a property on the Window so that it stays in scope when the method exits
    mBackupHandler = New BackupHandler

   ' The window has a progress bar that is updated as the backup progresses
    mBackupHandler.BackupProgressBar = BackupProgress
    mDB.BackUp(mBackupDB, mBackupHandler)
  Catch error As IOException
    MessageBox("Backup Database could not be created. Error: " + error.Message)
  End Try
End If

A class called BackupHandler implements SQLiteBackupInterface and has code in these methods:

Complete:

MessageBox("Backup Complete.")

Error:

MessageBox("There was an error during the backup: " + errorCode.ToString)

Progress:

If BackupProgressBar <> Nil Then
  BackupProgressBar.MaximumValue = 100
  BackupProgressBar.Visible = True
  BackupProgressBar.Value = percent * 100
End If

You can also load a SQLite data file into an in-memory database by swapping the parameters:

' Connect to the database file on disk
Var fileDB as New SQLiteDatabase
fileDB.DatabaseFile = SpecialFolder.Resources.Child("template.db")
fileDB.Connect

' Create the database in memory
Var memoryDB as New SQLiteDatabase
memoryDB.Connect

' "Backup" the file database into memory
fileDB.BackUp(memoryDB, Nil, -1)

' Close the file database
fileDB.Close

SQLiteDatabase.BeginTransaction

BeginTransaction

Creates a new transaction. Changes to the database made after this call can be saved with CommitTransaction or undone with RollbackTransaction.

A DatabaseException will be raised if the transaction could not begin or another transaction is already in progress.

You typically want to Commit changes after ensuring there were no database errors:

' Prior DB code has run

Try
  DB.BeginTransaction
  DB.ExecuteSQL("CREATE TABLE AddressBook name VARCHAR, email VARCHAR")
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

SQLiteDatabase.Close

Close

Closes or disconnects the database.

Calling Close does not issue a Commit, but some databases will automatically Commit changes in a transaction when you Close the connection and some database will automatically Rollback changes in a transaction when the connection is closed. Refer to the documentation for your database to check what its behavior is.

For desktop apps, you will often Connect to the database when the app starts and Close it when the app quits.

For web apps, you usually Connect to the database when the Session starts and Close it when the Session quits.

This code in the App.Closing event handler closes a previously connected database:

DB.Close ' DB is a property on App

SQLiteDatabase.CommitTransaction

CommitTransaction

Commits an open transaction. This permanently saves changes to the database.

A DatabaseException will be raised if the transaction could not be committed.

You have to have an open transaction to be able to use CommitTransation. Use BeginTransaction to begin a transaction:

DB.BeginTransaction

You typically want to commit changes after ensuring there were no database errors:

' Prior DB code has run

Try
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

SQLiteDatabase.Connect

Connect

Connects to the database so that you can begin using it.

Warning

A DatabaseException will be raised if the connection could not be made.

This example connects to the database object in the variable db:

Try
  db.Connect
  MessageBox("Connected!")
Catch error As DatabaseException
  MessageBox("Error connecting to the database: " + error.Message)
End Try

SQLiteDatabase.Constructor

Constructor

Note

Constructors are special methods called when you create an object with the New keyword and pass in the parameters above.

Creates an in-memory SQLiteDatabase.

SQLiteDatabase.Constructor

Constructor(dbFile As FolderItem)

Note

Constructors are special methods called when you create an object with the New keyword and pass in the parameters above.

Creates a SQLiteDatabase using the provided database file.

You can use this Constructor instead of manually assigning the data file using the DatabaseFile property.

SQLiteDatabase.CreateBlob

CreateBlob(tableName As String, columnName As String, row As UInt64, length As Integer, databaseName As String = "") As SQLiteBLOB

Creates a new BLOB column for the specified table and column at the specified row (rowID).

The row parameter is the rowid value, and not the actual row number, for example if you only have one record in your database, with a rowid of 100 then you would pass in a row of 100 and not 1 for example.
The blob cannot be resized
Creating a new blob automatically zeros the entire blob
The row must exist when calling CreateBlob, it does not create a new record for you

You can use the Database constants: MainDatabase ("main") and TempDatabase ("temp") as necessary.

This example stores a picture in a BLOB (db is a preexisting database):

Var file As FolderItem
file = FolderItem.ShowOpenFileDialog("")

Var blob As SQLiteBlob
Var dbRowID As Integer = 1
blob = db.CreateBlob("Team", "Logo", dbRowID, file.Length)

If blob <> Nil Then
  Var bd As BinaryStream
  bd = BinaryStream.Open(file, False)

  Var data As String
  While Not bd.EndOfFile
    data = bd.Read(1000)

    blob.Write(data)
    If blob.WriteError Then
       MessageBox("Error writing to BLOB.")
       Exit While
    End If
  Wend
  bd.Close
  blob.Close

  ' Do something with the data
End If

SQLiteDatabase.CreateDatabase

CreateDatabase

Creates then opens a new SQLiteDatabase using the FolderItem in the DatabaseFile property.

If the database already exists, this function works like Database.Connect. If CreateDatabase succeeds, the DatabaseFile property will bet set to the DatabaseFile. If CreateDatabase does not succeed, an IOException is raised.

This code creates a database file and catches the exception if the operation fails:

Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child("MyDatabase.sqlite")
Try
  db.CreateDatabase
Catch error As IOException
  MessageBox("The database file could not be created: " + error.Message)
End Try

SQLiteDatabase.Decrypt

Decrypt

Decrypts an encrypted database. You must be connected to the database in order to decrypt it.

This example decrypts an encrypted database:

Var db As New SQLiteDatabase
db.DatabaseFile = FolderItem.ShowOpenFileDialog("db.sqlite")
db.EncryptionKey = "howdy+doody"
Try
  db.Connect
  db.Decrypt
Catch error As DatabaseException
  ' handle error here
End Try

SQLiteDatabase.Encrypt

Encrypt(password As String)

Encrypts the database using password as the encryption key. If you are already connected to an encrypted database and call Encrypt with an empty string, the database is decrypted.

Note

You cannot encrypt a database from within a transaction. You must encrypt it outside of any transactions.

Encrypt can be used both to encrypt a non-encrypted database, to change the password for an existing encrypted database and to decrypt a database.

Encrypt does not yield to threads, so it will freeze the application for large databases.

Always back up the database before encrypting it.

2017r3 and prior: AES-128 encryption is always used.

2018r1 and later defaults to AES-128, but AES-256 can also be used by including the prefix "aes256:" before the rest of the encryption key.

If you have a database that was previously encrypted as AES-128 and would like to switch to AES-256, first remove the encryption using Decrypt and then encrypt it again with Encrypt.

This example encrypts a database using the passed password.

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("db.sqlite")
Try
  db.Connect
  db.Encrypt("howdy+doody")
Catch error As DatabaseException
  ' handle error here
End Try

SQLiteDatabase.ExecuteSQL

ExecuteSQL(command As String, ParamArray values() As Variant)

Used to execute an SQL command. Use this for commands that do not return any data, such as CREATE TABLE or INSERT. The command parameter contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

A DatabaseException will be raised if the SQL passed is invalid or if an error occurs.

Passing values as parameters will protect your database by automatically creating a SQLitePreparedStatement.

Important

On Android, SQLiteDatabase.ExecuteSQL does not support multiple commands separated by semicolons. If you send multiple commands in this way, only the first command is run.

In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a SQL injection attack:

' Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City='" + CityField.Text + "' WHERE PostalCode='" + PostalCodeField.Text + "'"
Try
 db.ExecuteSQL(sql)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

Here's the same example but using parameters which protects you against a SQL injection attack:

' Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Try
 db.ExecuteSQL(sql, CityField.Text, PostalCode.Text)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

The parameter values can also be passed in as a variant array:

Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Var values(1) As Variant
values(0) = CityField.Text
values(1) = PostalCode.Text
Try
 db.ExecuteSQL(sql, values)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

This code creates the Team table:

' db is a SQLite database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
  db.ExecuteSQL(sql)
  MessageBox("Team table created successfully.")
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

SQLiteDatabase.IsConnected

IsConnected As Boolean

Returns True if the connection to the database is still active.

SQLiteDatabase.OpenBlob

OpenBlob(tableName As String, columnName As String, row As UInt64, readWrite As Boolean, databaseName As String = "") As SQLiteBLOB

Opens a BLOB column for the specified table and column at the specified row (rowID).

The row parameter is the rowid value, and not the actual row number, for example if you only have one record in your database, with a rowid of 100 then you would pass in a row of 100 and not 1 for example.
Pass in false as the readWrite parameter to open the blob for reading only.
The blob cannot be resized
Creating a new blob automatically zeros the entire blob
The row must exist when calling CreateBlob, it does not create a new record for you

Important

This method is not currently supported for Android.

This example reads the Logo (stored as a binary picture) from the Logo column for rowID = 1 in the Team table:

Var blob As SQLiteBlob
blob = db.OpenBlob("Team", "Logo", 1, True)
If blob <> Nil Then
  Var data As String
  While Not blob.EndOfFile
    Try
      data = data + blob.Read(1000)
    Catch e As IOException
      MessageBox("Error reading from BLOB.")
      Exit While
    End Try
  Wend
  blob.Close
  ' Do something with the data
End If

SQLiteDatabase.Prepare

Prepare(statement As String) As PreparedSQLStatement

Creates a PreparedSQLStatement using the SQL statement for use with the SQLitePreparedStatement class. A prepared statement is an SQL statement with parameters that has been pre-processed by the database so that it can be executed more quickly if it is re-used with different parameters. Prepared statements also mitigate the risk of SQL injection in web apps.

If the provided SQL statement has an error, a DatabaseException will occur when you call SelectSQL or ExecuteSQL.

SQL Injection is a way to attack database-driven applications.

Important

This method is not currently supported for Android.

To create a prepared statement, you use the appropriate class for the database you are using. For example, to create a prepared statement for SQLite:

' db is a previously connected SQLiteSQLDatabase object

Var ps As SQLitePreparedStatement
ps = db.Prepare("SELECT * FROM Country WHERE code = $1")

SQLiteDatabase.RemoveDatabase

RemoveDatabase(databaseName As String)

Removes the passed database that was previously added with AddDatabase.

This example removes as previously added database:

Var DBFile As New FolderItem("MyDB.sqlite")

Try
  currentDB.AddDatabase(attachDBFile, "locations")
  MessageBox("Database attached.")
  ' Queries against the attached database would be prefixed with "locations", such as
  ' SELECT City FROM locations.Addresses WHERE ST = 'TX'
  ' When you are finished, you can detach the database
  currentDB.RemoveDatabase("locations")
Catch error As IOException
  MessageBox("The database could not be added.")

Catch error As DatabaseException
  MessageBox("The database could not be removed."
End Try

SQLiteDatabase.RollbackTransaction

RollbackTransaction

Cancels an open transaction restoring the database to the state it was in before the transaction began.

A DatabaseException will be raised if the rollback could not be completed.

You will generally want to rollback database changes if a DatabaseException occurs within the transaction.

Important

While rolling back a transaction restores the database to its previous state, it has no impact on the data you access in a RowSet. To update the RowSet so that its data matches the database, you will need to re-query the database to recreate the RowSet.

You have to have an open transaction to be able to use Rollback. Call BeginTransaction to begin a transaction:

DB.BeginTransaction

This code uses rollback to revert database changes in a transaction when an error occurs:

' Prior DB code has run

Try
  db.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  db.RollbackTransaction
End If

SQLiteDatabase.SelectSQL

SelectSQL(query As String, ParamArray values() As Variant) As RowSet

Executes a SQL SELECT statement and returns the results in a RowSet. The query parameter contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

A DatabaseException will be raised if the SQL passed is invalid or if an error occurs.

Passing values as parameters will protect your database by automatically creating a SQLitePreparedStatement.

This sample adds the names of all customers in a particular postal code to a ListBox. It passes the entire SQL select as a single statement and appends the value from a TextField called PostalCode, leaving the database vulnerable to a SQL injection attack:

' db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

This is the same code as above but instead it uses a value identifier (?) and then passes the value in separately to avoid a SQL injection attack:

' db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are both DesktopTextField controls:

' db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Note

SQLite supports other value identifiers as well. See the SQLite documentation for details.

SQLiteDatabase.TableColumns

TableColumns(tableName As String) As RowSet

Returns a RowSet with information about all the columns (fields) in the specified tableName.

Note

If an error occurs, a DatabaseException will be raised.

TableColumns returns a RowSet with these columns:

ColumnName: A string that is the name of the column.
FieldType: An integer that describes the type of the column (refer to the table below).
IsPrimary: A boolean that indicates if the column is part of the primary key.
NotNull: A boolean that indicates if the column can be set to NULL.
Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns).
cid: (SQLite Only) The ID of the column.

This table identifies the Column Type based on the FieldType Integer:

Field Type	Value	Description
Null	0	Denotes the absence of any value, i.e., a missing value.
Byte	1	Stores the byte representation of a character string.
SmallInt	2	A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. SQLite supports 4-byte smallints. If you are using another data source, check the documentation of your data source.
Integer	3	A numeric data type with no fractional part. The maximum number of digits is implementation-specific. SQLite supports 8-byte integer columns and the FieldType evaluates to 19 (64-bit integer).
Char	4	Stores alphabetic data, in which you specify the maximum number of characters for the field, i.e., CHAR (20) for a 20 character field. If a record contains fewer than the maximum number of characters for the field, the remaining characters will be padded with blanks.
Text or VarChar	5	Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks. For example, VARCHAR (20) specifies a VARCHAR field with a maximum length of 20 characters.
Float	6	Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5).
Double	7	Stores double-precision floating-point numbers.
Date	8	Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits.
Time	9	Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero.
TimeStamp	10	Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component.
Currency	11	This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.
Boolean	12	Stores the values of TRUE or FALSE.
Decimal	13	Stores a numeric value that can have both an integral and fractional part. You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999.
Binary	14	Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary field.
Long Text (Blob)	15	Stores a text object. Consult the documentation of your data source for information on the maximum size of a Blob.
Long VarBinary (Blob)	16	Stores a binary object. SQLite supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source.
MacPICT	17	Stores a Macintosh PICT image. SQLite does not support this data type. Use a Blob to store images.
String	18	Text up to about 2 billion bytes. The same as VarChar.
Int64	19	Stores a 64-bit integer. Integer fields in SQLite are 64 bits and FieldType returns 19.
Unknown	255	Unrecognized data type.

Note

Depending upon the version you are using, only a subset of these types may be supported.

The following code creates a table and then display each column name one at a time:

Var db As SQLiteDatabase
Var dbFile As FolderItem

dbFile = New FolderItem("mydb.sqlite")
If dbFile.Exists Then
  dbFile.Remove
End If
db = New SQLiteDatabase
db.DatabaseFile = dbFile

Try
  db.CreateDatabase
  db.ExecuteSQL("CREATE TABLE Invoices (ID INTEGER, CustID INTEGER, Amount Double, Note TEXT)")

  Var columns As RowSet = db.TableColumns("Invoices")

  For Each c As DatabaseRow In columns
    MessageBox("Column: " + c.Column("ColumnName").StringValue)
  Next
Catch error As IOException
  MessageBox("The database could not be created: " + error.Message)
Catch error As DatabaseException
  MessageBox("Database error: " + error.Message)
End Try

SQLiteDatabase.TableIndexes

TableIndexes(tableName As String) As RowSet

Returns a RowSet containing the list of indexes for the passed tableName. Returns Nil if the table has no indexes or the database source does not support indexes.

A DatabaseException will be raised if an error occurs.

The RowSet returns one row for each index on the table and it has one field: IndexName As String.

This code displays the indexes for the "Invoices" table (if it exists) in the specified SQLite database:

Var dbFile As FolderItem
dbFile = FolderItem.ShowOpenFileDialog("")

If dbFile <> Nil Then
  Var db As New SQLiteDatabase
  Try
    db.Connect
    Var indexRS As RowSet
    indexRS = db.TableIndexes("Invoices") ' A table with indexes in the SQLite DB
    For Each row As DatabaseRow In indexRS
      MessageBox("Index: " + row.ColumnAt(0).StringValue)
    Next
    indexRS.Close
  Catch error As NilObjectException
    MessageBox("This database has no indexes.")
  Catch error As DatabaseException
    MessageBox("Could not connect to database. Error: " + error.Message)
  End Try
End If

SQLiteDatabase.Tables

Tables As RowSet

Returns a RowSet with a list of all tables in the database.

A DatabaseException will be raised if an error occurs.

Tables returns a RowSet with one field: TableName As String.

The following code gets and displays the table names for the connected database:

' App.db is a connected database
Var tables As RowSet
tables = App.db.Tables

Try
  For Each row As DatabaseRow In tables
    MessageBox(row.ColumnAt(0).StringValue)
  Next
  tables.Close
Catch error As NilObjectException
  MessageBox("This database has no tables.")
End Try

Notes

SQLite supports a subset of SQL/92 and SQL/99, including queries that involve self-joins, aggregate functions and more.

For complete documentation of SQLite, refer to the official SQLite documentation: http://sqlite.org/docs.html

Network access

SQLite is not a server database. There are appropriate uses for SQLite. According the SQLite developers should not be placed on a shared network drive for access by multiple client apps as this can lead to database corruption.

If you require a database that is shared with multiple client apps you can switch to a server database such as PostgreSQL or MySQL. There are also products that wrap a server around a SQLite database, such as CubeSQL. Another common solution is to create a web service that handles all requests to the SQLite database. Your clients communicate with the web service, which is the only app that then communicates with the SQLite database.

Threading

SelectSQL and ExecuteSQL statements do not block when called from within a Thread.

Transactions

By default, SQLite does a Commit after each SQL command that changes the database or its data.

To ensure data integrity and to improve performance, you should create a transaction and do your database changes in the transaction. To start a transaction use this command:

db.ExecuteSQL("BEGIN TRANSACTION") ' db is an instance of SQLiteDatabase

When you are finished with the database changes, you can close the transaction by calling Commit to make the changes permanent:

db.CommitTransaction

If you want to cancel the changes, you can use Rollback:

db.RollbackTransaction

Encrypted databases

Note

You cannot encrypt a database within a transaction. The encryption must be done outside a transaction.

2017r3 and prior: AES-128 encryption is always used.

2018r1 and later defaults to AES-128, but AES-256 can also be used by including the prefix "aes256:" before the rest of the encryption key.

For more information about the encryption, refer to the SQLite Encryption documentation.

To create an encrypted database, specify a value for the EncryptionKey property. The EncryptionKey property must be set before calling either Connect or CreateDatabase. In other words, write something like this to create a new database:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
Try
  db.CreateDatabase
Catch error As IOException
  ' handle error here
  MessageBox(error.Message)
End Try

When you open an encrypted database file, you need to supply the key:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
Try
  db.Connect
Catch error As IOException
  ' handle error here
  MessageBox(error.Message)
End Try

Result codes

Operations that don't involve files and cause an error will result in SQLiteDatabase raising a DatabaseException. Error information is then available in the DatabaseException.ErrorNumber and DatabaseException.Message properties. Operations that involve the database file (such as it cannot be found or opened) will raise an IOException.

SQLite error codes:

Error code	Error Message
0	Not an error
1	SQL logic error or missing database
2	Internal SQLite implementation flaw
3	Access permission denied
4	Callback requested query abort
5	Database is locked
6	Database table is locked
7	Out of memory
8	Attempt to write a read/only database
9	Interrupted
10	Disk I/O error
11	Database disk image is malformed
12	Table or record not found
13	Database is full
14	Unable to open database file
15	Database locking protocol failure
16	Table contains no data
17	Database schema has changed
18	Too much data for one table row
19	Constraint failed
20	Datatype mismatch
21	Library routine called out of sequence
22	Kernel lacks large file support
23	Authorization denied
24	Auxiliary database format error
25	Bind or column index out of range
26	File is encrypted or is not a database
200	Not connected

Primary keys

All SQLite tables have an Integer Primary Key column. If you don't explicitly create such a column, one will be created for you with the name "rowid". If you create your own INTEGER PRIMARY KEY column, then rowid acts as an alias to that column. This means that a query that includes rowid will instead return the column that is the primary key.

This SQL will create an Employees table with "EmployeeID" as the primary key:

CREATE TABLE Employees (EmployeeID INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT)

You can also use this SQL:

CREATE TABLE Employees (EmployeeID INTEGER, FirstName TEXT, LastName TEXT, PRIMARY KEY (EmployeeID))

The above syntax is used when creating a table with a multi-part primary key.

Now consider the following queries:

SELECT * FROM Employees

SELECT rowid,* FROM Employees

SELECT rowid, FirstName, LastName FROM Employees

In all of these cases, you will get the EmployeeID column in place of the rowid column.

If you have your own primary key column but still wish to get the rowid, you can do so using the SQL AS keyword. The following example returns the columns rowid, EmployeeID, FirstName, LastName:

SELECT rowid AS rowid, * FROM tablename

If you don't explicitly define your own INTEGER PRIMARY KEY column, you won't get the 'rowid' column unless you specifically include it in the list of columns to include in your query:

SELECT rowid, * FROM TableName

Note

You should always specifically define a primary key for your tables and not rely on the rowid column. When there is no specific primary key, the values for rowid are not guaranteed to remain the same which could cause problems if you use the rowid values in your apps or in foreign keys. In particular, the rowid values could be renumbered when data is deleted from a table and when the database is cleaned up using the VACUUM command.

Foreign keys

SQLite supports foreign keys, but they are not enabled by default. To enable them, you use the SQLite PRAGMA command each time you connect to the database:

PRAGMA foreign_keys = ON;

You can send this PRAGMA command to SQLite from Xojo using ExecuteSQL:

Var db As New SQLiteDatabase
Try
  db.Connect
  ' Enable foreign keys
  db.ExecuteSQL("PRAGMA foreign_keys = ON;")
Catch error As DatabaseException
  MessageBox("Database error: " + error.Message)
End Try

Data types

SQLite handles data types differently than most other database.

In particular, it does not use strong data typing. Although a column and be defined using a data type (called a storage class or affinity with SQLite), you can still put any type of data in any column.

To start, SQLite only supports these data types:

NULL
INTEGER
REAL
TEXT
BLOB

Refer to the official SQLite Data Type page here: http://www.sqlite.org/datatype3.html

The following table describes how to use the SQLite data types to store data from built-in data types with DatabaseRow or DatabaseColumn:

FieldType	Description
Blob	BLOB stands for Binary Large OBject. It is a column that contains large string data. This data is not altered in any way (no encodings are applied), so you can use a BLOB to store pictures, files or any other binary data. Use DatabaseColumn.Value to read or save BLOB data. Alternatively you can use the SQLiteBLOB class to handle BLOB data incrementally.
Boolean	SQLite does not have a BOOLEAN data type. Boolean will correctly convert the above values to a boolean. Other values in a column are undefined when attempting to convert to a boolean.
Currency	Use the REAL data type to store Currency values with DatabaseColumn.CurrencyValue.
Date / DateTime	SQLite does not have a DATE or DATETIME data type. DateTime. To store a DateTime value, you should manually save DateTime.SQLDateTime as a string.
Double	Use the REAL data type to store Double values using DatabaseColumn.DoubleValue.
Integer	Use the INTEGER data type to store Integer values with DatabaseColumn.IntegerValue. SQLite always stored 64-bit integer values.
Int64	Use the INTEGER data type to store Int64 values with DatabaseColumn.Int64Value. SQLite always stored 64-bit integer values.
Picture	Use the BLOB data type to store Pictures with DatabaseColumn.PictureValue.
String	Use the TEXT data type to store String values with DatabaseColumn.StringValue. SQLite converts all text in TEXT columns to UTF-8 encoding. If you want to preserve the original encoding, use a BLOB column type instead.

In-memory database

An "in-memory database" is a SQLite database that exists only in memory; there is no related file on disk. It works exactly as a SQLite database except it is very fast and completely temporary.

To create an in-memory database, create a new SQLiteDatabase instance and connect to it:

Var inMemoryDB As New SQLiteDatabase
Try
  inMemoryDB.Connect
  ' Can now use inMemoryDB
Catch error As DatabaseException
  MessageBox("Connection failed: " + error.Message)
End Try

You can use the Backup method to save an in-memory database to a file on disk.

Modified rows

To find out the number of rows that were modified by an UPDATE or INSERT command, you can call the SQLite changes function:

SELECT changes();

Extensions

Extentions are not loaded by default. Use the LoadExtensions property to enable them as necessary.

Performance tips

To improve the performance of SQLite DB access, you can turn on Write-Ahead Logging by setting the WriteAheadLogging property to True. Additionally, to increase the user's perception of performance, you may want to place long-running queries within a Thread.

You can also increase the page size that SQLite uses by changing the page_size setting using the PRAGMA command. The default page_size is 4096, but a larger size can help reduce I/O. To change the setting, give it a new value after you create or connect to the database but before you send any other commands that would cause database I/O. The page_size is permanently retained for the database after it is set:

Var db As New SQLiteDatabase
Try
  db.Connect
  ' Set page_size before any other commands are sent
  db.ExecuteSQL("PRAGMA page_size = 8192;")
  db.ExecuteSQL("CREATE TABLE Foo (c1, c2);")
Catch error As DatabaseException
  MessageBox("A database error occurred: " + error.Message)
End Try

For more information about PRAGMA commands, visit the official SQLite documentation:

PRAGMA statements

Periodically use the ANALYZE command to update the internal information that SQLite uses to make better query planning choices. You'll probably want to do this any time you add or remove large amounts of data from a table or when the SQLite database version is updated.

db.ExecuteSQL("ANALYZE")

Sample code

The following example creates a new SQLite database:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("MyDB.sqlite")
Try
  db.CreateDatabase
  ' proceed with database operations...
Catch error As DatabaseException
  MessageBox("Database not created. Error: " + error.Message)
  Return
End Try

The following example opens an existing SQLite database.

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("MyDB.sqlite")
Try
  db.Connect
  ' proceed with database operations here..
Catch error As DatabaseException
  MessageBox("The database couldn't be opened. Error: " + error.Message)
  Return
End Try

The following example adds a record to a table.

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("Employees.sqlite")
Try
  db.Connect
  db.ExecuteSQL("BEGIN TRANSACTION")
  db.ExecuteSQL("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _
    + "('Dr.Strangelove','Advisor',1962)")
  db.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  db.RollbackTransaction
End Try

Compatibility

All project types on all supported operating systems.

SQLiteDatabase

Description

Properties

Methods

Property descriptions

Method descriptions

Notes

Network access

Threading

Transactions

Encrypted databases

Result codes

Primary keys

Foreign keys

Data types

In-memory database

Modified rows

Extensions

Performance tips

Sample code

Compatibility

See also