Using SQLite: Difference between revisions
No edit summary |
No edit summary |
||
(47 intermediate revisions by 4 users not shown) | |||
Line 1: | Line 1: | ||
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 | 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 AppStudio supports. | ||
If you just need to save some simple data, without needing an index, have a look at using [[localStorage]]. | If you just need to save some simple data, without needing an index, have a look at using [[localStorage]]. | ||
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. | 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 | Your app can create, update and delete SQLite database tables. 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. | ||
Databases cannot be directly imported or exported, once again, for security reasons. However, it is possible to [[Distributing_your_App#The_Manifest_File|include an SQLite database]] with your application. Add the name of the database to the manifest in [[Properties Window]] and it will be automatically converted in to a format that can be exported from the server. The app will then import the database automatically when the application starts for the first time. To do this, the [[SqlExport|SQLExport]] and [[SqlImport|SQLImport]] methods are called internally. These methods are also available for use at any time. | |||
The database is created inside the browser's private storage area. It is not part of the normal file system. This prevents apps from interfering with each other's databases. | |||
There are two sets of commands: the ones AppStudio uses to call SQLite and the SQLite commands themselves. Documentation on AppStudio's commands are in this Tech Note; documentation on SQLite itself is in the [[SQLite Reference]]. | |||
=== Platforms Supported === | |||
SQLite is on all iOS and Android devices. It is in Chrome, Safari and Brave. It is not supported by other browsers, such as Internet Explorer, Edge or Firefox. | |||
The results of calls to the database are | To test if SQLite is supported, use this code: | ||
Basic: | |||
<pre> | |||
If window.openDatabase Then | |||
'SQLite can be used. | |||
Else | |||
'SQLite is not supported | |||
End if | |||
</pre> | |||
JavaScript: | |||
<pre> | |||
if (window.openDatabase) { | |||
//SQLite can be used. | |||
} else { | |||
//SQLite is not supported | |||
} | |||
</pre> | |||
=== Persisting Updates to the Database === | |||
If you have an already defined database that you want to include with your application, to ensure that updates to your database are persisted, one needs to ensure that the "manifest" property of the project is properly set. When the database is specified in the manifest property, its uploaded to the application when the application is first run just like the [[sqlimport|SQLImport]] statement. | |||
The [[sqlimport|SQLImport]] function uses this statement for example, SQLImport(JSON, [DB], [callback], [overwrite]), and your "manifest" property should be in line with this for your updates to be considered when your application is run, an example would be 'mydb.db,dbObj,callBackMethod,NSB.overwriteIfVersionDifferent'. | |||
NB: If on this property only the database name is specified that should be included with the application, any changes made during the application run, whilst they will be reflected in your application, the problem is that the next time you start the application, all your updates will not exist. | |||
=== 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. The database operation continues on its own. When it finishes, it will call a function 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. | |||
Consider the following code: | Consider the following code: | ||
Line 35: | Line 70: | ||
"Success - result received" | "Success - result received" | ||
=== Transactions === | |||
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. | 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. | ||
Line 56: | Line 91: | ||
sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function) | sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function) | ||
==== | ==== Parameters ==== | ||
Parameters allow you to simplify your coding by putting the arguments into a separate array. It's also a more secure way of handling data from users: they will not be able to do an [http://en.wikipedia.org/wiki/SQL_injection SQL Injection] attack on your database. | |||
To use parameters, put a question mark (?) for each item in the Values clause. Then, add a new item to your SQL operation which contains an array of the values. | |||
<pre> | |||
args = ['Haley',16,121] | |||
sqlList[0]=[ "INSERT INTO customerData (name,age,sales) VALUES (?,?,?)", args] | |||
</pre> | |||
You will find this method is easier to use, requires less code and is easier to read than putting the values inline. | |||
==== Opening the database file using SqlOpenDatabase() ==== | |||
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: | 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") | DB = SqlOpenDatabase("customers.db","1.0","My Customer Database") | ||
If DB | If DB.version<>"" Then Print "Database Opened" | ||
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: | 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, | res = '''SqlOpenDatabase('''''filename, version, fullname, maxsize''''')''' | ||
where | where | ||
Line 74: | Line 120: | ||
* ''maxsize'' is the maximum number of records (optional). Used on file creation. Set to 1,000,000 if no value supplied. | * ''maxsize'' is the maximum number of records (optional). Used on file creation. Set to 1,000,000 if no value supplied. | ||
==== | ==== Create a table in the database ==== | ||
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. | 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. | ||
Line 84: | Line 130: | ||
Sql(DB, sqlList) | 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: | 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'' | '''Sql'''(''db, sqlList'') | ||
where | where | ||
* ''db'' is the reference to the database returned by SqlOpenDatabase | * ''db'' is the reference to the database returned by SqlOpenDatabase | ||
* '' | * ''sqlList'' is an SQLite command or an array of SQLite commands. | ||
==== | ==== Add records to the database and select them ==== | ||
For j = 0 to databaseSize-1 | For j = 0 to databaseSize-1 | ||
sqlList[j | args=[cust, j, j*10] | ||
sqlList[j]=["INSERT INTO customerData (name, age, sales) VALUES (?,?,?);", args] | |||
Next | Next | ||
sqlList[databaseSize | sqlList[databaseSize]=["SELECT * from customerData ORDER BY name;", dataHandler] | ||
Sql(DB, sqlList) | Sql(DB, sqlList) | ||
Line 109: | Line 156: | ||
// Called on completion of SQL command | // Called on completion of SQL command | ||
DBRecords = results | DBRecords = results | ||
message.innerHTML = | message.innerHTML = "Records created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & " milliseconds." | ||
End Function | End Function | ||
In <code>dataHandler</code>, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned. | In <code>dataHandler</code>, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned. | ||
==== | ==== Replace a record in the table ==== | ||
cmd="INSERT OR REPLACE INTO | cmd="INSERT OR REPLACE INTO ""customerData"" VALUES (""" & tbFirstName.text & """,""" & _ | ||
tbLastName.text &""",""" & tbAge.text & """)" | |||
Sql(DB, cmd) | Sql(DB, cmd) | ||
Line 125: | Line 173: | ||
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. | 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. | ||
==== | ==== Update a record ==== | ||
Let's start with the simplest case: | |||
<pre> | |||
Function HeaderBar1_onclick(button) | |||
s=Array(["UPDATE Customers SET CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid]) | |||
Sql(DB,s) | |||
End Function | |||
</pre> | |||
You need to extend it in two ways: update more fields, and update more rows. | |||
To update more fields, add more to the SET clause: | |||
<pre> | |||
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid]) | |||
</pre> | |||
To update more rows, add a new array element for each row: | |||
<pre> | |||
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid]) | |||
s.push(["UPDATE Customers SET CompanyName='NewCo2', City='New York' WHERE CustomerID='ANATR';", loadGrid]) | |||
</pre> | |||
This is pretty easy | ==== Delete a record ==== | ||
This is pretty easy. Do not use success or fail routine with DELETE. | |||
cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """" | cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """" | ||
Line 136: | Line 205: | ||
DELETE FROM customerData WHERE lastname = "Kemeny" | DELETE FROM customerData WHERE lastname = "Kemeny" | ||
==== | ==== Closing the Database ==== | ||
When you are done using a database, you can close it. | When you are done using a database, you can close it. | ||
Line 144: | Line 213: | ||
==== Complete Sample ==== | ==== Complete Sample ==== | ||
'This sample shows how to access SQLite from | 'This sample shows how to access SQLite from AppStudio. It can also be found in the Samples folder 2: it is SQLSample1. | ||
<pre> | <pre> | ||
Dim databaseSize 'number of records in the database | Dim databaseSize 'number of records in the database | ||
Line 166: | Line 235: | ||
total=total + DBRecords.rows.item(i)["sales"] | total=total + DBRecords.rows.item(i)["sales"] | ||
Next | Next | ||
txtMessage.value = "Total of " & DBRecords.rows.length & " recs is " & total & " in " & (Sysinfo(10)-startTime) & " milliseconds." | txtMessage.value = "Total of " & DBRecords.rows.length & " recs is " & total & " in " & _ | ||
(Sysinfo(10)-startTime) & " milliseconds." | |||
End Function | End Function | ||
Line 175: | Line 245: | ||
sqlList = [] | sqlList = [] | ||
For j=0 To databaseSize-1 | For j=0 To databaseSize-1 | ||
sqlList[j]=["SELECT * FROM customerData WHERE name=?", "Customer" & Fix(Rnd * databaseSize), Random_Total] | |||
Next | Next | ||
Line 187: | Line 257: | ||
Function Random_Complete(transaction, results) | Function Random_Complete(transaction, results) | ||
txtMessage.value = "Total of " & databaseSize & " random recs is " & total & " in " & (Sysinfo(10)-startTime) & " milliseconds." | txtMessage.value = "Total of " & databaseSize & " random recs is " & total & " in " & _ | ||
(Sysinfo(10)-startTime) & " milliseconds." | |||
End Function | End Function | ||
Function initDB() | Function initDB() | ||
Dim sqlList | Dim sqlList | ||
DB = SqlOpenDatabase(" | DB = SqlOpenDatabase("customerdb.db","1.0","My Customer Database") | ||
If DB | If DB<>0 Then | ||
startTime=SysInfo(10) | |||
nTables = -1 | |||
sqlList=[] | |||
args = [Name, Address1, Address2, Age, Sales] | |||
sqlList[i+2]=["INSERT INTO customerData (name, address1, address2, age, sales) VALUES (?,?,?,?,?);", args] | |||
Sql(DB, sqlList) | |||
End If | |||
sqlList[ | |||
End Function | End Function | ||
Line 212: | Line 277: | ||
// Called On completion of Sql command | // Called On completion of Sql command | ||
DBRecords = results | DBRecords = results | ||
txtMessage.value = | txtMessage.value = "Recs created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & _ | ||
" milliseconds." | |||
End Function | End Function | ||
Function skipError(transaction, | Function skipError(transaction, error) | ||
//Called On failure of Sql command | //Called On failure of Sql command | ||
MsgBox "SQL Error: " & error.message & " (" & error.code & ")" | |||
End Function | |||
Function btnDelete_onclick() | |||
Dim SQList | |||
SQList=[] | |||
'Note no success or fail callbacks! | |||
SQList[0]=["DELETE FROM customerData"] | |||
SQList[1]=["SELECT * from customerData ORDER BY name;", dataHandler] | |||
Sql(DB, SQList) | |||
txtMessage.value = "All rows deleted." | |||
End Function | End Function | ||
</pre> | </pre> | ||
=== Using the Chrome Debugger to examine databases === | |||
By opening the Chrome Debugger and using the Resource Tab, you can examine the database. The following example shows a database named customers with a single record containing John Doe's name, age and sales: | |||
[[File:Sqldebugger.png]] | |||
=== Clearing a Locally-Deployed App Incorporating an SQLite Database From a PC === | |||
Clearing a remotely-deployed app's data and SQLite DB from a browser simply involves clearing the browser's cached app data or content for that app's domain via the settings panel. However, for a locally-deployed app the process is more involved because the various pieces are stored in multiple locations on the PC. | |||
Here's a way to eliminate all traces of a locally-deployed app so that it will behave as a freshly installed app on the next deploy: | |||
1. In Chrome Debugger's console, with the app currently deployed locally, delete all localStorage variables with the localStorage.clear() command. Use the Resources tab, Local Storage, file:// to confirm success. | |||
2. Find the special location where Windows stores SQLite (and other) databases and delete that folder with its contents: | |||
* Windows 7+: \Users\_username_\AppData\Local\Google\Chrome\User Data\Default\databases | |||
* Mac OS X: ~/Library/Application Support/Google/Chrome/Default/databases | |||
* Linux: ~/.config/google-chrome/Default/databases | |||
3. Delete the app files themselves (html, /nsb folder, etc.) from the Windows system. The location for these is found in the URL/Address window of the Chrome browser itself. Just drill down using the file explorer and delete the temp folder being used. | |||
''Contributed by c185driver'' |
Revision as of 14:28, 13 November 2020
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 AppStudio supports.
If you just need to save some simple data, without needing an index, have a look at using localStorage.
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 database tables. 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.
Databases cannot be directly imported or exported, once again, for security reasons. However, it is possible to include an SQLite database with your application. Add the name of the database to the manifest in Properties Window and it will be automatically converted in to a format that can be exported from the server. The app will then import the database automatically when the application starts for the first time. To do this, the SQLExport and SQLImport methods are called internally. These methods are also available for use at any time.
The database is created inside the browser's private storage area. It is not part of the normal file system. This prevents apps from interfering with each other's databases.
There are two sets of commands: the ones AppStudio uses to call SQLite and the SQLite commands themselves. Documentation on AppStudio's commands are in this Tech Note; documentation on SQLite itself is in the SQLite Reference.
Platforms Supported
SQLite is on all iOS and Android devices. It is in Chrome, Safari and Brave. It is not supported by other browsers, such as Internet Explorer, Edge or Firefox.
To test if SQLite is supported, use this code:
Basic:
If window.openDatabase Then 'SQLite can be used. Else 'SQLite is not supported End if
JavaScript:
if (window.openDatabase) { //SQLite can be used. } else { //SQLite is not supported }
Persisting Updates to the Database
If you have an already defined database that you want to include with your application, to ensure that updates to your database are persisted, one needs to ensure that the "manifest" property of the project is properly set. When the database is specified in the manifest property, its uploaded to the application when the application is first run just like the SQLImport statement.
The SQLImport function uses this statement for example, SQLImport(JSON, [DB], [callback], [overwrite]), and your "manifest" property should be in line with this for your updates to be considered when your application is run, an example would be 'mydb.db,dbObj,callBackMethod,NSB.overwriteIfVersionDifferent'.
NB: If on this property only the database name is specified that should be included with the application, any changes made during the application run, whilst they will be reflected in your application, the problem is that the next time you start the application, all your updates will not exist.
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. The database operation continues on its own. When it finishes, it will call a function 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.
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"
Transactions
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[0]="INSERT INTO customerData (name,age,sales) VALUES ('Haley',16,121)" sqlList[1]="INSERT INTO customerData (name,age,sales) VALUES ('Alex',12,80)" sqlList[2]="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.
Example:
sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function)
Parameters
Parameters allow you to simplify your coding by putting the arguments into a separate array. It's also a more secure way of handling data from users: they will not be able to do an SQL Injection attack on your database.
To use parameters, put a question mark (?) for each item in the Values clause. Then, add a new item to your SQL operation which contains an array of the values.
args = ['Haley',16,121] sqlList[0]=[ "INSERT INTO customerData (name,age,sales) VALUES (?,?,?)", args]
You will find this method is easier to use, requires less code and is easier to read than putting the values inline.
Opening the database file using SqlOpenDatabase()
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.version<>"" Then Print "Database Opened"
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)
where
- filename is the actual name of the file
- version is the version number (optional). Used on file creation. Set to "1.0" if no value supplied.
- fullname is a long description of the file. (optional). Used on file creation. Set to filename if no value supplied.
- maxsize is the maximum number of records (optional). Used on file creation. Set to 1,000,000 if no value supplied.
Create a table in the database
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[0]=["DROP TABLE customerData;",,skipError] sqlList[1]=["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)
where
- db is the reference to the database returned by SqlOpenDatabase
- sqlList is an SQLite command or an array of SQLite commands.
Add records to the database and select them
For j = 0 to databaseSize-1 args=[cust, j, j*10] sqlList[j]=["INSERT INTO customerData (name, age, sales) VALUES (?,?,?);", args] Next sqlList[databaseSize]=["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 [SQLite.htm 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
In dataHandler
, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.
Replace a record in the table
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.
Update a record
Let's start with the simplest case:
Function HeaderBar1_onclick(button) s=Array(["UPDATE Customers SET CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid]) Sql(DB,s) End Function
You need to extend it in two ways: update more fields, and update more rows.
To update more fields, add more to the SET clause:
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])
To update more rows, add a new array element for each row:
s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid]) s.push(["UPDATE Customers SET CompanyName='NewCo2', City='New York' WHERE CustomerID='ANATR';", loadGrid])
Delete a record
This is pretty easy. Do not use success or fail routine with DELETE.
cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """" Sql(DB, cmd)
The command sent to SQLite resolves to:
DELETE FROM customerData WHERE lastname = "Kemeny"
Closing the Database
When you are done using a database, you can close it.
DB.close
Complete Sample
'This sample shows how to access SQLite from AppStudio. It can also be found in the Samples folder 2: it is SQLSample1.
Dim databaseSize 'number of records in the database Dim DB 'the database itself Dim startTime 'used for timer Dim lastSQL 'the last SQL statement. Useful for debugging. Dim total 'use to total sales from customers Dim DBRecords 'the last set of records selected from DB databaseSize=1000 Function btnCreate_onclick() total=0 initDB End Function Function btnCount_onclick() total=0 startTime=SysInfo(10) For i=0 To DBRecords.rows.length-1 total=total + DBRecords.rows.item(i)["sales"] Next txtMessage.value = "Total of " & DBRecords.rows.length & " recs is " & total & " in " & _ (Sysinfo(10)-startTime) & " milliseconds." End Function Function btnRandom_onclick() Dim sqlList startTime=SysInfo(10) total=0 sqlList = [] For j=0 To databaseSize-1 sqlList[j]=["SELECT * FROM customerData WHERE name=?", "Customer" & Fix(Rnd * databaseSize), Random_Total] Next sqlList[databaseSize-1][1]=Random_Complete Sql(DB, sqlList) End Function Function Random_Total(transaction, results) total=total + results.rows.item(0)["sales"] End Function Function Random_Complete(transaction, results) txtMessage.value = "Total of " & databaseSize & " random recs is " & total & " in " & _ (Sysinfo(10)-startTime) & " milliseconds." End Function Function initDB() Dim sqlList DB = SqlOpenDatabase("customerdb.db","1.0","My Customer Database") If DB<>0 Then startTime=SysInfo(10) nTables = -1 sqlList=[] args = [Name, Address1, Address2, Age, Sales] sqlList[i+2]=["INSERT INTO customerData (name, address1, address2, age, sales) VALUES (?,?,?,?,?);", args] Sql(DB, sqlList) End If End Function Function dataHandler(transaction, results) // Called On completion of Sql command DBRecords = results txtMessage.value = "Recs created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & _ " milliseconds." End Function Function skipError(transaction, error) //Called On failure of Sql command MsgBox "SQL Error: " & error.message & " (" & error.code & ")" End Function Function btnDelete_onclick() Dim SQList SQList=[] 'Note no success or fail callbacks! SQList[0]=["DELETE FROM customerData"] SQList[1]=["SELECT * from customerData ORDER BY name;", dataHandler] Sql(DB, SQList) txtMessage.value = "All rows deleted." End Function
Using the Chrome Debugger to examine databases
By opening the Chrome Debugger and using the Resource Tab, you can examine the database. The following example shows a database named customers with a single record containing John Doe's name, age and sales:
Clearing a Locally-Deployed App Incorporating an SQLite Database From a PC
Clearing a remotely-deployed app's data and SQLite DB from a browser simply involves clearing the browser's cached app data or content for that app's domain via the settings panel. However, for a locally-deployed app the process is more involved because the various pieces are stored in multiple locations on the PC.
Here's a way to eliminate all traces of a locally-deployed app so that it will behave as a freshly installed app on the next deploy:
1. In Chrome Debugger's console, with the app currently deployed locally, delete all localStorage variables with the localStorage.clear() command. Use the Resources tab, Local Storage, file:// to confirm success.
2. Find the special location where Windows stores SQLite (and other) databases and delete that folder with its contents:
- Windows 7+: \Users\_username_\AppData\Local\Google\Chrome\User Data\Default\databases
- Mac OS X: ~/Library/Application Support/Google/Chrome/Default/databases
- Linux: ~/.config/google-chrome/Default/databases
3. Delete the app files themselves (html, /nsb folder, etc.) from the Windows system. The location for these is found in the URL/Address window of the Chrome browser itself. Just drill down using the file explorer and delete the temp folder being used.
Contributed by c185driver