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.
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.
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"
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="INSERT INTO customerData (name,age,sales) VALUES ('Haley',16,121)" sqlList="INSERT INTO customerData (name,age,sales) VALUES ('Alex',12,80)" sqlList="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.
sqlList=array("Select * from customerData", Success_Function, Fail_Function)
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)
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=["DROP TABLE customerData;",,skipError] sqlList=["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
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
dataHandler, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.
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.
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"
When you are done using a database, you can close it.