SQLite made Simple: Difference between revisions
No edit summary |
|||
(3 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
== SQLite Overview == | == SQLite Overview == | ||
[http://en.wikipedia.org/wiki/Sqlite 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 | [http://en.wikipedia.org/wiki/Sqlite 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. | * Your app can create, update and delete SQLite databases. | ||
Line 57: | Line 57: | ||
Sub Main | Sub Main | ||
DB = SqlOpenDatabase("students.db | DB = SqlOpenDatabase("students.db") | ||
sqlList=[] | sqlList=[] | ||
sqlList[0]=["CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );", _ | sqlList[0]=["CREATE TABLE studentData('name', 'age', PRIMARY KEY('name') );", _ |
Latest revision as of 20:43, 1 February 2021
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 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/
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") 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