B4J Tutorial [BANano] Working with BANanoSQL

BANanoSQL makes it possible to use 'normal' SQL queries on the IndexedDB database located in the browser.

There are three ways on how to work with BANanoSQL:

1. Event Driven
This is the advised mode to use as it follows the IndexedDB rules. Every open and execute command results in an event, and from there on, you can take the following step. The 'Tag' in the methods is used to guide the process.

Advantage: it can raise an Error Event in case e.g. an SQL is malformed
Disadvantage: if can become quite complex if many queries have to run

The results are posted in events.

Sub BANano_Ready()   
   SQL.Open("SQL", "bananodb")
End Sub

Sub SQL_SQLOpened()
   SQL.Execute("CREATE TABLE IF NOT EXISTS flights (flightNo INT, fromCity STRING, toCity STRING, isInTheAir BOOL)", Null, "CREATE")
End Sub

Sub SQL_SQLExecuteError(Tag As String, Reason As String)
   Log("ERROR for TAG:" & Tag & " Reason:" & Reason)
End Sub

Sub SQL_SQLExecuteResult(Tag As String, Result As List)
   Select Case Tag
       Case "CREATE"
           SQL.Execute("SELECT COUNT(*) AS myCount FROM flights;", Null, "COUNT")       
       Case "COUNT"
           Dim myCount As Int = 0
           If Result.Size > 0 Then
               Dim m As Map = Result.Get(0)
               myCount = m.Get("myCount")
               Log("myCount: " & myCount)
           End If
           myCount = myCount + 1       
           SQL.Execute("INSERT INTO flights (flightNo, fromcity, toCity, isInTheAir) VALUES (?, ?, ?, ?)", Array(myCount, "Brussels", "Paris", True), "INSERT")
       Case "INSERT"
           Log("We are done")
   End Select
End Sub

The process here is:

Open -> SQLOpened -> Execute (Tag="CREATE") -> SQLExecuteResult (Case "CREATE") -> Execute (Tag="COUNT) -> SQLExecuteResult (Case "COUNT") -> Execute (Tag="INSERT") -> SQLExecuteResult (Case "INSERT")

2. Wait (from version 1.01)
This is the alternative. It does not raise events but waits until the query has finished. This method has wrappers around the IndexedDB system to call the alaSQL methods async.

Advantage: easy to read and follow in your code
Disadvantages:
- As it waits, and a long query runs, your app may appear to hang. It is unresponsive while waiting
- the calling method can NOT return a value

Does the same as method 1.

Sub BANano_Ready()
   SQL.OpenWait("SQL", "bananodb")
 
   SQL.ExecuteWait("CREATE TABLE IF NOT EXISTS flights (flightNo INT, fromCity STRING, toCity STRING, isInTheAir BOOL)", Null)
   Dim ret As List = SQL.ExecuteWait("SELECT COUNT(*) AS myCount FROM flights;", Null)
   Dim MyCount As Int = SingleResult(ret, "myCount")
   Log("MyCount Before: " & MyCount)
   MyCount = MyCount + 1
   SQL.ExecuteWait("INSERT INTO flights (flightNo, fromcity, toCity, isInTheAir) VALUES (?, ?, ?, ?)", Array(MyCount, "Brussels", "Paris", True))
End Sub

' helper method
public Sub SingleResult(lst As List, fieldName As String) As String
   If lst <> Null Then
       If lst.Size > 0 Then
           Dim rec As Map = lst.Get(0)
           Return rec.Get(fieldName)       
       End If
   End If
   Return "0"
End Sub

3. Event driven with your own callback (2.15+)

This is just like type 1, except you give it your own callback method.

Sub SQL_SQLOpened()   SQL.ExecuteCallback("CREATE TABLE IF NOT EXISTS flights (flightNo INT, fromCity STRING, toCity STRING, isInTheAir BOOL)", Null, Me, "mycreate")
End Sub

' MUST be this definition.  No params can be added or removed!
Sub MyCreate(success As Boolean, Result As List, Reason As String) 'ignore
   Log("Creation: " & success)
   SQL.Execute("SELECT COUNT(*) AS myCount FROM flights;", Null, "COUNT")
End Sub

I think all methods have a reason to exist. The second method may be useful to setup your database, or do a lot of INSERT queries, or if the flow is really complex.

The first and third one will give your users the best experience, as while the app is doing complex queries, he can still continue to work with it.

Your choice!

Alain

 

[alwaysbusy]

Update 1.02: ExecuteWait does now return null if the execute failed. In the browsers log, more info on what went wrong can be seen.

 

[alwaysbusy]

Updated with a third execute method.