Class

iOSSQLiteDatabase


Warning

This item was deprecated in version 2020r2. Please use SQLiteDatabase as a replacement.

Description

Provides access to SQLite databases in iOS projects.

Methods

Name

Parameters

Returns

Shared

AttachDatabase

file As FolderItem, databaseName As String, encryptionKey As String = ""

Boolean

Connect

Boolean

CreateDatabaseFile

Boolean

Decrypt

DetachDatabase

databaseName As String

Encrypt

encryptionKey As String

LastRowID

Int64

SQLExecute

sqlstatement As String, ParamArray values() As Variant

SQLSelect

sqlstatement As String, ParamArray values() As Variant

RowSet

Property descriptions


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

Connect to an existing database:

Var db As New iOSSQLiteDatabase

Var dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child("MyDB.sqlite")

If dbFile.Exists Then
  db.DatabaseFile = dbFile
  If db.Connect Then
    ' proceed with database operations here...
  Else
    Var err As Text = "Could not open the database."
  End If
End If

iOSSQLiteDatabase.EncryptionKey

EncryptionKey As Text

Specifies the encryption key used to open an encrypted database or to encrypt a database.

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

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

To encrypt an existing database, use the Encrypt method after you have created or connected to the database.

To decrypt an existing database, use the Decrypt method after you have connected to the encrypted database.

AES-256 encryption is used starting with 2018r1. Before that, AES-128 encryption was used.


iOSSQLiteDatabase.LibraryVersion

LibraryVersion As Text

The version of the SQLite library.

This property is read-only.

Display the SQLite version:

Var db As New iOSSQLiteDatabase
VersionLabel.Text = db.LibraryVersion

iOSSQLiteDatabase.ThreadYieldInterval

ThreadYieldInterval As Integer

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

This is provided for advanced usage of SQLite and should not typically need to be changed.


iOSSQLiteDatabase.Timeout

Timeout As Double

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

An iOSSQLiteDatabase can be locked while it is being modified. On iOS, you will not need to worry about the timeout unless you have multiple threads accessing a database using separate connections.

Method descriptions


iOSSQLiteDatabase.AttachDatabase

AttachDatabase(file As FolderItem, databaseName As String, encryptionKey As String = "") As Boolean

Attaches the SQLite database referred to by file to the database. It gives the newly attached database the name databaseName. If the database is encrypted, be sure to specify the encryptionKey.

You can attach one or more databases to a currently connected iOSSQLiteDatabase so that you can work with all the databases at one time. This can be a useful way to copy data between databases, for example.

If tables in the attached database have the same name as tables in the primary database, you can refer to them using databaseName as the prefix.


iOSSQLiteDatabase.Connect

Connect As Boolean

Connects to the database so that you can begin using it. Before proceeding with database operations, test to be sure that Connect returns True.

To create an in-memory SQLite database, call Connect without having previously specified a DatabaseFile.

Connect to an existing database:

Var db As New iOSSQLiteDatabase

Var dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child("MyDB.sqlite")

If dbFile.Exists Then
  db.DatabaseFile = dbFile
  If db.Connect Then
    ' proceed with database operations here...
  Else
    Var err As Text = "Could not open the database."
  End If
End If

iOSSQLiteDatabase.CreateDatabaseFile

CreateDatabaseFile As Boolean

Creates a database file. Returns True if the database was successfully created.

You are automatically connected if CreateDatabase returns True, so you do not need to also call Connect.

Create a New iOSSQLiteDatabase:

Var dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child("MyDB.sqlite")

Var db As New iOSSQLiteDatabase
db.DatabaseFile = dbFile
If db.CreateDatabaseFile Then
  ' proceed with database operations here...
Else
  Var err As Text = "Database could not be created."
End If

iOSSQLiteDatabase.Decrypt

Decrypt

Removes the encryption from an encrypted database. You must be connected to the database in order to decrypt it.

Decrypting a database removes the encryption. You do not need to decrypt the database for normal use. Only use this command if you want to actually remove the encryption so that you can later connect to the database without providing the encryption key.


iOSSQLiteDatabase.DetachDatabase

DetachDatabase(databaseName As String)

Detaches the passed database that was previously attached with AttachDatabase.


iOSSQLiteDatabase.Encrypt

Encrypt(encryptionKey As String)

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

AES-256 encryption is used starting with 2018r1. Before that, AES-128 encryption was used.

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.


iOSSQLiteDatabase.LastRowID

LastRowID As Int64

Returns an Int64 containing the value of the last RowID added to any table in the database.


iOSSQLiteDatabase.SQLExecute

SQLExecute(sqlstatement As String, ParamArray values() As Variant)

Used to execute a SQL command. Use this for commands that do not return any data, such as CREATE TABLE or INSERT. You can optionally supply an array of values that will bind parameters (specified with "?") in sqlstatement.

Refer to the SQLSelect Notes for more information about prepared statements and parameter binding.

Creates a table in an iOSSQLiteDatabase:

Var sql As Text
sql = "CREATE TABLE Team (ID INTEGER, Name TEXT," + _
  " Coach TEXT, City TEXT, PRIMARY KEY(ID));"

Try
  DB.SQLExecute(sql) ' DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
  Var err As Text = e.Reason
End Try

Update data in a table:

Var sql As Text
sql = "UPDATE Team SET Name = ?1, Coach = ?2 WHERE ID = ?3;"

' Pass in values after sql instead of doing string replacement
Try
  Var name As Text = "Mud Hens"
  Var coach As Text = "Dave Roberts"
  Var ID As Integer = 100 ' Primary key for row
  DB.SQLExecute(sql, firstName, lastName, ID)
Catch e As iOSSQLiteException
    Var err As Text = e.Reason
End Try

Add data to a table:

Var sql As Text
sql = "INSERT INTO Team (Name, Coach, City) VALUES (?1, ?2, ?3);"

' Pass in values after sql instead of doing string replacement
Try
  Var name As Text = "Flying Squirrels"
  Var coach As Text = "Tim Roberts"
  Var city As Text = "Springfield"
  ' ID is created automatically because it is a primary key
  DB.SQLExecute(sql, name, coach, city)
Catch e As iOSSQLiteException
  Var err As Text = e.Reason
End Try

Remove data from a table:

Var sql As Text
sql = "DELETE FROM Team WHERE ID = ?1;"

' Pass in values after sql instead of doing string replacement
Try
  Var ID As Integer = 100 ' Primary key for row
  DB.SQLExecute(sql, ID) ' Previously connected database
Catch e As iOSSQLiteException
    Var err As Text = e.Reason
End Try

iOSSQLiteDatabase.SQLSelect

SQLSelect(sqlstatement As String, ParamArray values() As Variant) As RowSet

Used to run an SQL statement that returns an iOSSQLiteRecordSet, such as a SELECT statement. You can optionally supply a list of values that will bind to parameters (specified with "?") in sqlstatement.

Get the Name and City values for the Team table:

Var sql As Text = "SELECT Name, City FROM Team;"
Var teams As iOSSQLiteRecordSet
Try
  teams = DB.SQLSelect(sql) ' DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
  Var err As Text = e.Reason
End Try

You can also supply parameters:

Var sql As Text = "SELECT Name, City FROM Team WHERE ID = ?1;"
Var teams As iOSSQLiteRecordSet
Try
  Var ID As Integer = 100
  teams = DB.SQLSelect(sql, ID) ' DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
  Var err As Text = e.Reason
End Try

An example with a Text parameter:

Var sql As Text = "SELECT Name FROM Team WHERE City = ?1;"
Var teams As iOSSQLiteRecordSet
Try
  Var city As Text = "Boston"
  teams = DB.SQLSelect(sql, city) ' DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
  Var err As Text = e.Reason
End Try

Notes

Version used:

Xojo Version

SQLite Version

2019r3

3.29.0

2019r2

3.28.0

2019r1

3.26.0

2018r4

3.25.3

2018r3

3.24.0

2018r2

3.23.1

2018r1

3.22.0

2017r3

3.20.1

For more information about SQLite: * SQL Syntax * Pragma Commands * Unsupported SQL

If your database object goes out of scope, the database is closed. This means you are typically going to want your database object be somewhat global to your app. A common technique is to have an iOSSQLiteDatabase property on the App object that you refer throughout your app as App.DB.


Transactions

iOSSQLiteDatabase does an auto-commit after each SQL command if you do not manually start a transaction. You can start a transaction using this command:

myDB.SQLExecute("BEGIN TRANSACTION")

When the transaction is complete, you commit your changes with this command:

myDB.SQLExecute("COMMIT")

You can cancel a transaction by calling rollback with this command:

myDB.SQLExecute("ROLLBACK")

Compatibility

iOS projects on the iOS operating system.

See also

Object parent class; RowSet, DatabaseException, RowSet classes