SQLite is a fast, popular and easy to use database. Its speed and easy of use make it a practical choice for use when you need an indexed file system for an app. Databases you create can be accessed, without change, on any platform SQLite supports. It is built into mobile devices that AppStudio supports.

If you just need to save some simple data, without needing an index, have a look at using [[localStorage]].

SQLite is a zero-configuration, in-process, SQL database engine. But don't let this description scare you: SQLite is an easy way for your app to store and retrieve data.

Your app can create, update and delete SQLite databases. The databases are kept in your app's private directory. Generally, an app is not allowed to access databases belonging to other apps for security reasons. However, apps deployed from the same server can share databases. This allows you to create a family of apps which share data, so long as they all come from the same server. The security protects apps from changing or stealing information from other app's databases.

Databases cannot be directly imported or exported, once again, for security reasons. However, it is possible to [[Distributing_your_App#The_Manifest_File|include an SQLite database]] with your application. Add the name of the database to the manifest in [[Properties Window]] and it will be automatically converted in to a format that can be exported from the server. The app will then import the database automatically when the application starts for the first time. To do this, the [[SqlExport|SQLExport]] and [[SqlImport|SQLImport]] methods are called internally. These methods are also available for use at any time.

There are two sets of commands: the ones AppStudio uses to call SQLite and the SQLite commands themselves. Documentation on AppStudio's commands are in this Tech Note; documentation on SQLite itself is in the [[SQLite Reference]].

=== Persisting Updates to the Database ===

To ensure that updates to your database are persisted to the database, one needs to ensure that the "manifest" property of the project is properly set. When the database is specified in the manifest property, its uploaded to the application when the application is first run just like the SQLImport statement.

The SQLImport function uses this statement for example, SQLImport(JSON, [DB], [callback], [overwrite]), and your "manifest" property should be in line with this for your updates to be considered when your application is run, an example would be 'mydb.db,dbObj,callBackMethod,NSB.overwriteIfVersionDifferent'.

=== Asynchronous Results ===

The results of calls to the database are returned asynchronously. That means that after a statement involving the database is executed, the next statement in the program is executed - even though the database operation may not be complete. The database operation continues on its own. When it finishes, it will call a function you specify in your program, depending on the success or failure of the operation. In the meantime, you can do other processing: update controls on the screen or do calculations. You cannot do a [[MsgBox]] statement while you are waiting for an SQL call to finish - you will lose the callback.

Consider the following code:

MsgBox "Before doing call"
sqlList="CREATE TABLE myData('name', 'age')"
sql(DB, [sqlList, Success_Function, Fail_Function])
Msgbox "After doing call"

Function Success_Function(transaction, result)
Msgbox "Success - result received"
End function

Function Fail_Function(transaction, result)
Msgbox "Fail"
End function

The result will be 3 messages, in this order:

"Before doing call"
"After doing call"
"Success - result received"

=== Transactions ===

When doing multiple operations on a database, for example, adding a number of new records, it is much faster to group them into a transaction. A transaction is a list of database operations that are performed as a set: each of the operations on the list has to successful. If any one step fails, the entire transaction can be aborted without change to the database.

A transaction is implemented as an array, with one operation in each element of the array.

Example: Add 3 new records into customerData

sqlList = []
sqlList[0]="INSERT INTO customerData (name,age,sales) VALUES ('Haley',16,121)"
sqlList[1]="INSERT INTO customerData (name,age,sales) VALUES ('Alex',12,80)"
sqlList[2]="INSERT INTO customerData (name,age,sales) VALUES ('Luke',7,65)"

Each operation in a transaction can have optional success and fail callbacks. In this case, the element in transaction array is itself an array:

[''operation'', ''success'', ''fail''] , where ''operation'' is a string. ''success'' and ''fail'' are functions in the program. You are required to have at least a ''success'' function defined in your program.

Example:

sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function)

==== Opening the database file using SqlOpenDatabase() ====

SQLite keeps the entire database in a single physical file. Use the [[SqlOpenDatabase]] method to open the file. If it does not exist, it is created:

DB = SqlOpenDatabase("customers.db","1.0","My Customer Database")
If DB.version<>"" Then Print "Database Opened"

The return value, DB, will be used to identify the database in subsequent calls. If the database does not exist, it is created. Here is the full syntax of SqlOpenDatabase:

res = '''SqlOpenDatabase('''''filename, version, fullname, maxsize''''')'''

where

* ''filename'' is the actual name of the file
* ''version'' is the version number (optional). Used on file creation. Set to "1.0" if no value supplied.
* ''fullname'' is a long description of the file. (optional). Used on file creation. Set to ''filename'' if no value supplied.
* ''maxsize'' is the maximum number of records (optional). Used on file creation. Set to 1,000,000 if no value supplied.

==== Create a table in the database ====

After step 1 creates a new database file, it is empty. A database is made up of tables. A table is simply a collection of records with a defined format. Let's create the table, but first, let's get rid of any existing table with the same name.

sqlList=[]
sqlList[0]=["DROP TABLE customerData;",,skipError]
sqlList[1]=["CREATE TABLE customerData('name', 'age', 'sales', PRIMARY KEY('name') );"]

Sql(DB, sqlList)

We're creating a table with three columns, indexed by the first name: each record therefore has 3 fields. The [[Sql]] statement is used to send a transaction (a list of SQL commands) to SQLite. The syntax of Sql is:

'''Sql'''(''db, sqlList'')

where

* ''db'' is the reference to the database returned by SqlOpenDatabase
* ''sqList'' is an SQLite command or an array of SQLite commands.

==== Add records to the database and select them ====

For j = 0 to databaseSize-1
sqlList[j]="INSERT INTO customerData (name, age, sales) VALUES ( " & "'cust" & j & "', " & j & ", " & j*10 & ");"
Next
sqlList[databaseSize]=["SELECT * from customerData ORDER BY name;", dataHandler]

Sql(DB, sqlList)

The SELECT command is very powerful - you can use it to collect all kinds of different sets of records. Read more about it in the [SQLite.htm SQL Documentation].

In the SELECT command, we're specifying that we want to run a special function when the SELECT completes. The function <code>datahandler</code> will be called. It gives the result of the SELECT:

Function dataHandler(transaction, results)
// Called on completion of SQL command
DBRecords = results
message.innerHTML = "Records created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & " milliseconds."
End Function

In <code>dataHandler</code>, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.

==== Replace a record in the table ====

cmd="INSERT OR REPLACE INTO ""customerData"" VALUES (""" & tbFirstName.text & """,""" & _
tbLastName.text &""",""" & tbAge.text & """)"
Sql(DB, cmd)

Once again, all the quotes get resolved in the command. The actual command sent to SQLite is:

INSERT OR REPLACE INTO "customerData" VALUES("John","Kemeny","80")

Using the last name as the key, the record is added or replaced. Once again, there are many more options on this command: we're just trying to get you started here. Once you have added the record, you'll need to do your selection again. It won't be automatically added to the existing selection.

==== Update a record ====

Let's start with the simplest case:
<pre>
Function HeaderBar1_onclick(button)
s=Array(["UPDATE Customers SET CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid])
Sql(DB,s)
End Function
</pre>
You need to extend it in two ways: update more fields, and update more rows.

To update more fields, add more to the SET clause:
<pre>
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])
</pre>
To update more rows, add a new array element for each row:
<pre>
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])
s.push(["UPDATE Customers SET CompanyName='NewCo2', City='New York' WHERE CustomerID='ANATR';", loadGrid])
</pre>

==== Delete a record ====

This is pretty easy. Do not use success or fail routine with DELETE.

cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """"
Sql(DB, cmd)

The command sent to SQLite resolves to:

DELETE FROM customerData WHERE lastname = "Kemeny"

==== Closing the Database ====

When you are done using a database, you can close it.

DB.close

==== Complete Sample ====

'This sample shows how to access SQLite from AppStudio. It can also be found in the Samples folder 2: it is SQLSample1.
<pre>
Dim databaseSize 'number of records in the database
Dim DB 'the database itself
Dim startTime 'used for timer
Dim lastSQL 'the last SQL statement. Useful for debugging.
Dim total 'use to total sales from customers
Dim DBRecords 'the last set of records selected from DB

databaseSize=1000

Function btnCreate_onclick()
total=0
initDB
End Function

Function btnCount_onclick()
total=0
startTime=SysInfo(10)
For i=0 To DBRecords.rows.length-1
total=total + DBRecords.rows.item(i)["sales"]
Next
txtMessage.value = "Total of " & DBRecords.rows.length & " recs is " & total & " in " & _
(Sysinfo(10)-startTime) & " milliseconds."
End Function

Function btnRandom_onclick()
Dim sqlList
startTime=SysInfo(10)
total=0
sqlList = []
For j=0 To databaseSize-1
sqlList[j]=["SELECT * FROM customerData WHERE name='cust" & Fix(Rnd * databaseSize) & "'",Random_Total]
Next

sqlList[databaseSize-1][1]=Random_Complete
Sql(DB, sqlList)
End Function

Function Random_Total(transaction, results)
total=total + results.rows.item(0)["sales"]
End Function

Function Random_Complete(transaction, results)
txtMessage.value = "Total of " & databaseSize & " random recs is " & total & " in " & _
(Sysinfo(10)-startTime) & " milliseconds."
End Function

Function initDB()
Dim sqlList
DB = SqlOpenDatabase("customerdb.db","1.0","My Customer Database")
If DB<>0 Then
startTime=SysInfo(10)
nTables = -1
sqlList=[]
sqlList[0]=["SELECT COUNT(*) AS tabCount FROM sqlite_master WHERE type='table' AND name='customerData';",tableExistCheck,masterErr]
Sql(DB, sqlList)
End If
End Function

Function dataHandler(transaction, results)
// Called On completion of Sql command
DBRecords = results
txtMessage.value = "Recs created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & _
" milliseconds."
End Function

Function skipError(transaction, error)
//Called On failure of Sql command
MsgBox "SQL Error: " & error.message & " (" & error.code & ")"
End Function

Function btnDelete_onclick()
Dim SQList
SQList=[]
'Note no success or fail callbacks!
SQList[0]=["DELETE FROM customerData"]
SQList[1]=["SELECT * from customerData ORDER BY name;", dataHandler]
Sql(DB, SQList)
txtMessage.value = "All rows deleted."
End Function
</pre>

=== Using the Chrome Debugger to examine databases ===

By opening the Chrome Debugger and using the Resource Tab, you can examine the database. The following example shows a database named customers with a single record containing John Doe's name, age and sales:

[[File:Sqldebugger.png]]

=== Clearing a Locally-Deployed App Incorporating an SQLite Database From a PC ===

Clearing a remotely-deployed app's data and SQLite DB from a browser simply involves clearing the browser's cached app data or content for that app's domain via the settings panel. However, for a locally-deployed app the process is more involved because the various pieces are stored in multiple locations on the PC.

Here's a way to eliminate all traces of a locally-deployed app so that it will behave as a freshly installed app on the next deploy:

1. In Chrome Debugger's console, with the app currently deployed locally, delete all localStorage variables with the localStorage.clear() command. Use the Resources tab, Local Storage, file:// to confirm success.

2. Find the special location where Windows stores SQLite (and other) databases and delete that folder with its contents:

* Windows Vista or 7: \Users\_username_\AppData\Local\Google\Chrome\User Data\Default\databases
* Windows XP: \Documents and Settings\_username_\Local Settings\Application Data\Google\Chrome\User Data\Default\databases
* Mac OS X: ~/Library/Application Support/Google/Chrome/Default/databases
* Linux: ~/.config/google-chrome/Default/databases

3. Delete the app files themselves (html, /nsb folder, etc.) from the Windows system. The location for these is found in the URL/Address window of the Chrome browser itself. Just drill down using the file explorer and delete the temp folder being used.

''Contributed by c185driver''