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 two 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.
    Code:
    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:

    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.
    Code:
    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 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
    I think both 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 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
     
    Last edited: Dec 3, 2018
  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.
Loading...
  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