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. Using the SQLite (WASM) library, it is usable on almost all platforms and browsers.

• Your app can create, update and delete SQLite databases.
• Databases are kept in your app's localStorage.
• Apps cannot access each other's data for security purposes.
• The exception is apps which are loaded 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. It is limited by the maximum size of localStorage, generally about 5 or 10 megs.

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.

JavaScriptBASIC

var DB;

function Main() {
  DB = SqlOpenDatabase("localStorage");
  sqlList = [];
  sqlList[0] = [
    "CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );",
    success,
    fail,
  ];
  Sql(DB, sqlList);
}

function success() {
  console.log("success");
}

function fail(error) {
  NSB.MsgBox(error);
}

Dim DB

Sub Main
  DB = SqlOpenDatabase("localStorage")
  sqlList=[]
  sqlList[0]=["CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );", _
  success, fail]
  Sql(DB, sqlList)
End Sub

Sub success()
  console.log("success")
End Sub

Sub fail(error)
  MsgBox error
End Sub

Save Button

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

JavaScriptBASIC

btnSave.onclick = function () {
  if (txtName.value == "") {
    return;
  }
  sqlList = [];
  sqlList[0] = [
    "INSERT INTO studentData (name,age) VALUES (?,?)",
    [txtName.value, txtAge.value],
    success,
    fail,
  ];
  Sql(DB, sqlList);
};

Function btnSave_onclick()
  If txtName.value="" Then Exit Function
  sqlList = []
  sqlList[0]=["INSERT INTO studentData (name,age) VALUES (?,?)", _
  [txtName.value, txtAge.value], success, fail]
  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.

JavaScriptBASIC

btnFind.onclick = function () {
  sqlList = [];
  sqlList[0] = [
    "SELECT * FROM studentData WHERE name=?",
    txtFind.value,
    nameFound,
  ];
  Sql(DB, sqlList);
};

function nameFound(transaction, results) {
  if (results.rows.length > 0) {
    NSB.MsgBox(results.rows.item(0).name + " is " + results.rows.item(0).age);
  } else {
    NSB.MsgBox("Name not found");
  }
  txtName.value = results.rows.item(0).name;
  txtAge.value = results.rows.item(0).age;
}

Function btnFind_onclick()
  sqlList=[]
  sqlList[0]=["SELECT * FROM studentData WHERE name=?", 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
  txtName.value = results.rows.item(0).name
  txtAge.value = results.rows.item(0).age
End Function

Delete Button

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

JavaScriptBASIC

btnDelete.onclick = function () {
  sqlList = [];
  sqlList[0] = [
    "DELETE FROM studentData WHERE name=?",
    txtFind.value,
    nameDeleted,
  ];
  Sql(DB, sqlList);
  txtName.value = "";
  txtAge.value = "";
};

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

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