Tech Note 15:
Using SQLite to save data

July 05, 2012

© 2013 NS BASIC Corporation. All rights reserved.

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 NSB/AppStudio supports.

If you just need to save some simple data, without needing an index, have a look at 'localStorage' in the NSB/AppStudio Handbook.

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. To import a database, create a series of NSB/AppStudio or JavaScript statements which contain the data and can populate the database. To export a database, you will need to POST it to a server.

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

Asynchronous Results

The results of calls to the database are return 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 functions 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)

1. 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=0 Then Return

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.

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

3. Add records to the database and select them

  For j = 0 to databaseSize-1
    sqlList[j+2]="INSERT INTO customerData (name, age, sales) VALUES ( " & "'cust" & j & "', " + j + ", " &	j*10 & ");"
  Next
  sqlList[databaseSize+2]=["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 SQL Documentation.

In the SELECT command, we're specifying that we want to run a special function when the SELECT completes. The function datahandler 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 dataHandler, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.

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

5. Delete a record

This is pretty easy:

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

The command sent to SQLite resolves to:

  DELETE FROM customerData WHERE lastname = "Kemeny"

6. Closing the Database

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

  DB.close

Complete Sample