One of the enhancements in NS Basic/App Studio 1.2.4 was the addition of JSON support. If you want the full details on what JSON is, read about it on Wikipedia. For our purposes, it is a fast way to wrap up a bunch of data up so it can be transmitted or received.
Using JSON, you can take a data structure or a complete SQLite database and turn it into a string which you can send to another system using HTTP POST or WebSockets. You can also receive a string the same way, which you can then turn into active data in your program or save to SQLite. You can also use JSON to encode a database which can then be installed with your app. Your app can read it in and construct the database.
It’s fast. We did a benchmark where we created an SQLite database with 20,000 customer address records. Exporting that database to JSON on an iPhone 4 took just over 1.1 seconds. Importing 1,000 records to a new database took 2.2 seconds. Tests on an Android Nexus S were also fast: import in .851 seconds and export of 20,000 records in 3.8 seconds. It’s quite practical to use on large files.
Here is how to export an SQLite database using JSON:
Function JSONExport()
Dim i
recs=DBRecords.rows.length
Dim data(recs)
For i=0 To recs-1
data[i]=DBRecords.rows.item(i)
Next
JSONExport=JSON.stringify(data)
End Function
What this code does is to copy the database into an array, with one element for each row of the database. The JSON.stringify() function converts it all to a JSON string.
Here is some code which imports an SQLite database:
Function JSONImport()
Dim data, sqlList, q
file=ReadFile("customers.json")
data=JSON.parse(file.responseText)
DB = SqlOpenDatabase("customers.db","1.0","My Customer Database")
q=Chr(34)
If DB<>0 Then
sqlList=[]
sqlList[0]=["DROP TABLE customerData;",,skipError]
sqlList[1]=["CREATE TABLE IF NOT EXISTS " & "customerData('name', 'address1', 'address2', 'age', 'sales', PRIMARY KEY('name') );"]
For i = 0 To UBound(data)-1
Name = q & data[i].name & q
Address1= q & data[i].address1 & q
Address2= q & data[i].address2 & q
Age = data[i].age
Sales = data[i].sales
s = Join([Name, Address1, Address2, Age, Sales],",")
sqlList[i+2]="INSERT INTO customerData (name, address1, address2, age, sales) VALUES ( " & s & ");"
Next
sqlList[i+2]=["SELECT * from customerData ORDER BY name;", dataHandler]
Sql(DB, sqlList)
End If
End Function
The ReadFile in the third line is interesting. “customers.json” is a file that is included in the project’s manifest and deployed with the project. ReadFile() reads the entire file in.
The fourth statement does all the work. It takes the data that was read in and turns it into an array, with one element per row. From there, it is straightforward to process it and add the records into the SQLite database.
You can try this sample out: it is called SqlSample1, and it is installed with NS Basic/App Studio starting with 1.2.4.
If you are curious about the format of the JSON file, here’s a sample:
[{"name":"Customer0","address1":"0 Winding River Lane","address2":"Anytown USA 100000","age":32,"sales":56025},{"name":"Customer1","address1":"1 Winding River Lane","address2":"Anytown USA 100001","age":75,"sales":86082},{"name":"Customer10","address1":"10 Winding River Lane","address2":"Anytown USA 1000010","age":23,"sales":52976},{"name":"Customer100","address1":"100 Winding River Lane","address2":"Anytown USA 10000100","age":87,"sales":26473},{"name":"Customer101","address1":"101 Winding River Lane","address2":"Anytown USA 10000101","age":10,"sales":45455},
...
The data is represented as an array, with one element for each row of the array. Each element is made up of a list of fieldnames in quotes and values. String values are in quotes; number values not.
