From Xojo Documentation

Revision as of 17:46, 27 March 2019 by PLefebvre (talk | contribs)

Class (inherits from Database)

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

DatabaseFile LoadExtensions ThreadYieldInterval
EncryptionKey MultiUser Timeout
LibraryVersion fa-lock-32.png ShortColumnNames
AddRow CreateBlob OpenBlob
AttachDatabase CreateDatabaseFile Prepare
Backup Decrypt RollbackTransaction
BeginTransaction DetachDatabase SelectSQL
Close Encrypt TableColumns
CommitTransaction ExecuteSQL TableIndexes
Connect LastRowID Tables


Xojo version SQLite version
2019r1 3.26.0
2018r4 3.25.3
2018r3 3.24.0
2018r2 3.23.1
2018r1 3.22.0
2017r3 3.20.1
2017r2 3.19.3

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

More specific topics:

A call to SQLSelect returns a dynamic RecordSet; you can move forward, backward, or jump to the beginning or end as much as you like.

SQLite supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the database class.

All four RecordSet navigation methods are supported for SQLite: MoveFirst, MoveNext, MovePrevious, and MoveLast.

Network Access

SQLite is not a server database and 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.


SQLSelect and SQLExecute statements do not block when called from within Threads.


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.SQLExecute("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:


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


Encrypted Databases

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 CreateDatabaseFile. In other words, write something like this to create a new database:

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
If Not db.CreateDatabaseFile Then
// handle error here
End If

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

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
If Not db.Connect Then
// handle error here
End If

Result Codes

SQLite sets the database class's Error flag after each operation and returns values in the ErrorCode and ErrorMessage properties. When the Error flag is False, the ErrorCode is "0" and ErrorMessage is empty. If the Error flag is True, the following codes and messages are returned in ErrorMessage

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:


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
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 SQLExecute:

Dim db As New SQLiteDatabase
If db.Connect Then
// Enable foreign keys
db.SQLExecute("PRAGMA foreign_keys = ON;")
End If

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
  • 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 DatabaseRecord or DatabaseField:

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 the DatabaseRecord.BlobColumn method or DatabaseField.StringValue 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. Booleans are stored using 0 or 1 in an INTEGER column or as "true" or "false" in a TEXT column. DatabaseRecord.BooleanColumn and DatabaseField.BooleanValue 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 in conjunction with DatabaseRecord.CurrencyColumn and DatabaseField.CurrencyValue.
Date SQLite does not have a DATE data type. Dates are stored as text using the format YYYY-MM-DD when using DatabaseRecord.DateColumn and DatabaseField.DateValue. To store a DateTime value, you should manually save Date.SQLDateTime as a string.
Double Use the REAL data type to store Double values in conjunction with DatabaseRecord.DoubleColumn and DatabaseField.DoubleValue.
Integer Use the INTEGER data type to store Integer values with DatabaseRecord.IntegerColumn and DatabaseField.IntegerValue. SQLite always stored 64-bit integer values.
Int64 Use the INTEGER data type to store Int64 values with DatabaseRecord.Int64Column and DatabaseField.Int64Value. SQLite always stored 64-bit integer values.
Picture Use the BLOB data type to store Pictures with DatabaseRecord.PictureColumn and DatabaseField.PictureValue.
String Use the TEXT data type to store String values with DatabaseRecord.Column and DatabaseField.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.

Because of the way SQLite stores all data, you can use DatabaseRecord.Column and DatabaseField.StringValue to get the values of data in any column, even if is not specifically a TEXT column.

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, just create a new SQLiteDatabase instance and connect to it:

Dim inMemoryDB As New SQLiteDatabase
If inMemoryDB.Connect Then
// Can now use inMemoryDB
End If

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();


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 MultiUser 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:

Dim db As New SQLiteDatabase
If db.Connect Then
// Set page_size before any other commands are sent
db.SQLExecute("PRAGMA page_size = 8192;")
db.SQLExecute("CREATE TABLE Foo (c1, c2);")
End If

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

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.


Sample Code

The following example creates a new SQLite database:

Dim f As FolderItem
f = New FolderItem("MyDB.sqlite")

Dim db As New SQLiteDatabase
db.DatabaseFile = f
If db.CreateDatabaseFile Then
// proceed with database operations...
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If

The following example opens an existing SQLite database.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem("MyDB.sqlite")
db.DatabaseFile = dbFile
If db.Connect Then
// proceed with database operations here..
MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
End If

The following example adds a record to a table.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem("Employees.sqlite")
db.DatabaseFile = dbFile
If db.Connect Then
db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _
+ "('Dr.Strangelove','Advisor',1962);")
If db.Error then
MsgBox("Error: " + db.ErrorMessage)
End If
MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
End If

See Also

Database Class, DatabaseRecord, PreparedSQLStatement, SQLitePreparedStatement, RecordSet, iOSSQLiteDatabase classes.