SQLite made Simple
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 most mobile devices that AppStudio supports as well as Chrome and Safari on the desktop. It is not supported by Internet Explorer or FireFox.
- 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/
- 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
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.
- 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=["CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );", _ success, fail] Sql(DB, sqlList) End Sub Sub success() End Sub Sub fail() End Sub
- 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=["INSERT INTO studentData (name,age) VALUES (?,?)", _ [txtName.value, TxtAge.value]] Sql(DB, sqlList) End Function
- 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=["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 removes matching records from the table
- We call nameDeleted() on successful deletion.
- If the deletion is unsuccessful, nothing happens.
Function btnDelete_onclick() sqlList =  sqlList=["DELETE FROM studentData WHERE name=?", txtFind.value, nameDeleted] Sql(DB, sqlList) End Function Function nameDeleted() MsgBox txtFind.value & " Deleted" End Function