` | sqlstatement As `String`, `ParamArray` values() As `Variant` | `RowSet` | |
## Property descriptions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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:
``` xojo
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
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
iOSSQLiteDatabase.LibraryVersion
**LibraryVersion** As `Text`
The version of the SQLite library.
This property is read-only.
Display the SQLite version:
``` xojo
Var db As New iOSSQLiteDatabase
VersionLabel.Text = db.LibraryVersion
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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:
``` xojo
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
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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\`:
``` xojo
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
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
iOSSQLiteDatabase.DetachDatabase
**DetachDatabase**(databaseName As `String`)
Detaches the passed database that was previously attached with AttachDatabase.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
iOSSQLiteDatabase.LastRowID
**LastRowID** As `Int64`
Returns an Int64 containing the value of the last RowID added to any table in the database.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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\`:
``` xojo
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:
``` xojo
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:
``` xojo
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:
``` xojo
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
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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:
``` xojo
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:
``` xojo
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:
``` xojo
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](http://sqlite.org/lang.html) \* [Pragma Commands](http://sqlite.org/pragma.html) \* [Unsupported SQL](http://sqlite.org/omitted.html)
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:
``` xojo
myDB.SQLExecute("BEGIN TRANSACTION")
```
When the transaction is complete, you commit your changes with this command:
``` xojo
myDB.SQLExecute("COMMIT")
```
You can cancel a transaction by calling rollback with this command:
``` xojo
myDB.SQLExecute("ROLLBACK")
```
## Compatibility
iOS projects on the iOS operating system.
## See also
`Object` parent class; `RowSet`, `DatabaseException`, `RowSet` classes