SQLite made Simple

SQLite Overview

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 all mobile devices that AppStudio supports as well as the desktop.

• Your app can create, update and delete SQLite databases.
• Databases are kept in your app's private directory.
• Apps cannot access each other's data for security purposes.
• The exception is apps which are load from the same server.
• Databases cannot be directly imported or exported, once again, for security reasons. You can include an SQLite database with your app.
• Databases can be imported or exported using SqlExport and SqlImport We will go in depth on SQLite in the next Section.

The maximum size of an SQLite database varies by OS and device. Here's an article with more information: http://www.html5rocks.com/en/tutorials/offline/quota-research/

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.
• When it finishes, it will call a success or fail function in your program.
• In the meantime, you can do other processing: update controls on the screen or do calculations.
• Do not do a MsgBox statement while you are waiting for an SQL call to finish - you will lose the callback.

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

   Function Success_Function(transaction, result)
     console.log("Success - result received"
   End function

   Function Fail_Function(transaction, result)
     console.log("Fail")
   End function

The result will be 3 messages, in this order, in the Chrome Debugger Console:

   Before doing call
   After doing call
   Success - result received

Sample App

The following app saves names and ages to an SQLite database. A Find function can be used to locate existing entries. The current entry, once found, can be deleted.

Initialize

• We need to create the database if it does not exist.
• The variable DB is a global reference to the database.
• SqlOpenDatabase creates the database. If it already exists, it does nothing.
• CREATE TABLE is a command executed by SQLite.
• It creates a table in the database.
• If the table already exists, it does nothing.
• Sql sends an array of commands to SQLite for processing.

Dim DB

Sub Main
  DB = SqlOpenDatabase("students.db","1.0","My Student Database")
  sqlList=[]
  sqlList[0]=["CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );", _
    success, fail]
  Sql(DB, sqlList)
End Sub

Sub success()
End Sub

Sub fail()
End Sub

Save Button

• Don't save anything if name is empty.
• INSERT INTO tells SQLite to add a record into the studentData table.

Function btnSave_onclick()
  If txtName.value="" Then Exit Function
  sqlList = []
  sqlList[0]=["INSERT INTO studentData (name,age) VALUES (?,?)", [txtName.value, TxtAge.value]]
  Sql(DB, sqlList)
End Function

Find Button

• SELECT searches the table for matching records.
• It calls the nameFound() function when it is done.
• The results (there could be more than one) are returned in an array.
• If there are no rows in the array, nothing was found.

Function btnFind_onclick()
  sqlList=[]
  sqlList[0]=["SELECT * FROM studentData WHERE name=?", txtFind.value, nameFound]
    txtFind.value & "'", nameFound]
  Sql(DB, sqlList)
End Function

Function nameFound(transaction, results)
  If results.rows.length>0 Then
    MsgBox results.rows.item(0).name & " is " & results.rows.item(0).age
  Else
    MsgBox "Name not found"
  End If
End Function

Delete Button

• DELETE removes matching records from the table
• We call nameDeleted() on successful deletion.
• If the deletion is unsuccessful, nothing happens.

Function btnDelete_onclick()
  sqlList = []
  sqlList[0]=["DELETE FROM studentData WHERE name=?", txtFind.value, nameDeleted]
  Sql(DB, sqlList)
End Function

Function nameDeleted()
  MsgBox txtFind.value & " Deleted"
End Function