B4J Tutorial [BANano] Working with BANanoSQL

Discussion in 'B4J Tutorials' started by alwaysbusy, Nov 27, 2018.

  1. alwaysbusy

    alwaysbusy Expert Licensed User

    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()   
    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(
    Log("myCount: " & myCount)
    End If
               myCount = myCount + 
    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:

    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
    - 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.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 + 
    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 StringAs 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!

    Last edited: Jan 27, 2019
  2. alwaysbusy

    alwaysbusy Expert Licensed User

    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.
    Kiffi likes this.
  3. alwaysbusy

    alwaysbusy Expert Licensed User

    Updated with a third execute method.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice