B4J Tutorial [BANanoSQL] Understanding CRUD logic with ExecuteCallBack

Discussion in 'B4J Tutorials' started by Mashiane, May 13, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Ola

    Some time ago, I wrote a piece here on... [BANano]: BANanoSQL CRUD-ing around with BANanoSQLUtils.

    That approach followed, SQLExecuteResult methodology of doing things, where there was a single method to rule them all, i.e. all the database functions are managed by one function. From version 2.15+ of BANano, there was a callback functionality that was added to BANanoSQL due a request I made. What a Marvel!

    I've been exploring the logic behind this methodology and its quite impressive and rather simple. The ExecuteCallBack BANanoSQL functionality should be thought of as some kind of pairing functionality. 1. You execute an INSERT, UPDATE, DELETE, SELECT and then 2. You process the result of that command. So for every SELECT, INSERT, UPDATE and DELETE, you need to have a callback function.

    This callback function follows a specific variable specification in its construct as depicted below.

    Code:
    Sub fields_update(success As Boolean, Result As List, Reason As String'ignore
    where fields_update is the name of my callback function and this SHOULD be specified on ExecuteCallBack for any of my commands.

    NB: BANanoSQL uses INDEXDB which stores content in the browser. You will have to have some way to persist the data somewhere else if you need to.

    So in this thread we will do a couple of things..


    1. Create a database + Open Event
    2. Create a table + ExecuteCallBack
    3. Look at (CREATE/INSERT + ExecuteCallBack) and (UPDATE + ExecuteCallBack)
    4. Look at READ + ExecuteCallBack
    5. Look at DELETE + ExecuteCallBack

    For a background, please take a look at the first link in this post. What will differ here is the removal of SQLExecuteResult and just additional CallBacks for each SQL command, the approach is very much similar.

    Other Related Topics
    Create a CRUD app using LocalStorage BackEnd and Working with BANanoSQL
     
    Last edited: May 13, 2019
  2. Mashiane

    Mashiane Expert Licensed User

    1. Creating a database

    To be able to do this, you can fire up B4J and create a new UX project and then reference BANano. In Process_Globals, define your SQL database variable.

    Code:
    Public MyFamilySQL As BANanoSQL
    You will need to ensure this database is created, so in your BANano_Ready sub, initialize the database. We will not use Wait for this exercise.

    Code:
    MyFamilySQL.Open("MyFamily""MyFamily")
    As soon as your app runs, a database names MyFamily will be created, if using Opera, goto developer mode, applications and you can find it there. Opening the database method like this expects an event prefixed with MyFamily. Then define your callback for the database opening exercise.

    Code:
    Sub MyFamilySQL_SQLOpened()
     
    'create the family table
     CreateTable_family
     
    End Sub
    Basically, when the database is created (if it does not exist), we want to call the CreateTable_Family method. This is a method that we have defined our table structure. We will create one table for now.
     
    joulongleu likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    2. Create a table

    This method builds up the CREATE TABLE command for this table using the pre-defined field types usable in BANanoSQL. These are, INT, STRING, DATE and BOOL.
    We use the DBUtils like mapping methodology to create the table. This was discussed in the BANanoSQLUtils exercise. Sorry wont recap.

    Code:
    Sub CreateTable_family
    Dim tbc As Map
    tbc.Initialize
    tbc.Put(
    "id", App.EnumFieldTypes.DB_INT)
    tbc.Put(
    "names", App.EnumFieldTypes.DB_STRING)
    tbc.Put(
    "lastname", App.EnumFieldTypes.DB_STRING)
    tbc.Put(
    "birthdate", App.EnumFieldTypes.DB_DATE)
    tbc.Put(
    "birthplace", App.EnumFieldTypes.DB_STRING)
    tbc.Put(
    "isliving", App.EnumFieldTypes.DB_BOOL)
    tbc.Put(
    "age", App.EnumFieldTypes.DB_INT)
    tbc.Put(
    "deathplace", App.EnumFieldTypes.DB_STRING)
    tbc.Put(
    "deathdate", App.EnumFieldTypes.DB_DATE)
    tbc.Put(
    "gender", App.EnumFieldTypes.DB_STRING)
    'define the command to run to build the table
         Dim sql As String = App.SQL_CreateTable("family",tbc,"id")
    'execute the command to create the table and trap with a callback
         MyFamilySQL.ExecuteCallback(sqlNull, Me, "tablecreate_family")
    End Sub


    Sub tablecreate_family(success As Boolean, Result As List, Reason As String)
     
    If success Then
     
    'call the next process here when the table is created
    'BuildPage
     Else
     
    'process the error here
     Log(Reason)
     
    End If
     
    End Sub
    The CREATE TABLE command is created and then we execute a callback that will trap "tablecreate_family" when the CREATE TABLE command has been executed. The result of this command can be a success or a failure. If successfull we run other necessary code, this could be building up the UX of our page etc. In this case it would be proper because we would want to use the table and add, update, delete our records as soon as our database structure is built.

    As you will note, this is the first ExecuteCallBack pair for BANanoSQL that we have done. The Reason for error come in handy during debug for example. This can be verbose so you can tweak your error messages yourself.
     
  4. Mashiane

    Mashiane Expert Licensed User

    3. Create / Insert & Update a record

    Let's just assume that we have created our beautiful UX based on the above table structure above. We will possibility have some buttons also for Add, Update, Delete on our page. The code snippet below is a theoritical approach on how a key value pair can be build based on each component/element on the page for the family table we created. For the sake of validity, some values are required so we add some-thing along those lines too.

    It should be noted that Add & Update will almost act identical. Form validation and then add / update the record in the table. For this we will define a variable, let's call it Action. When a user clicks Add, the action will be "ADD" and when a user clicks "UPDATE" the action will be "EDIT", after all we are processing the same record, so...

    So clicking Add or Update, the underlying code will be Insert_Family.

    Code:
    Sub Insert_family
    'check any compulsory fields
             Dim bRequired As Boolean = Page.Required(Array("names","lastname","birthdate","birthplace"))
    If bRequired Then
    'we have missing fields, inform user
             App.ToastError("There are some values that need to be speciefied to continue!")
    Return
    End If
    'get all the contents of the elements
         Dim recordM As Map = Page.GetValues(Array("id","names","lastname","birthdate","birthplace","isliving","age","deathplace","deathdate","gender")))
    Dim id As String = recordM.get("id")
    Select Case Action
         
    Case "ADD"
             
    'we are adding a new record, we need to run an insert comment, lets build it
             Dim sqlm As Map = App.SQL_InsertMap("family",recordM)
             
    'get the command
             Dim sql As String = sqlm.Get("sql")
             
    'get the arguements for the insert
             Dim args As List = sqlm.Get("args")
             
    'execute the insert command and trap with a callback
             MyFamilySQL.ExecuteCallback(sql, args, Me, "family_insert")
         
    Case "EDIT"
             
    'we are editing a record, we need an update command, let's build it
             Dim sqlm As Map = App.SQL_UpdateWhereMap("family",recordM,CreateMap("id":id))
             
    'lets get the update command
             Dim sql As String = sqlm.Get("sql")
             
    'let's get the arguements for the update command
                     Dim args As List = sqlm.Get("args")
             
    'execute the update command and trap with a callback
             MyFamilySQL.ExecuteCallback(sql, args, Me, "family_update")
         
    End Select
    End Sub
    What we do here is do form validation by checking if all required fields are specified, then read all the values per each element. If some fields are not specified, we show a toast, one can also use the SweetAlert dialog as per provided library or BANano.Alert method.

    When the Action is Add, its a new record, we create the INSERT COMMAND and arguements being the values of each component and then Execute a callback linked to the "family_insert" sub. Same with Update, when clicked we change the action to "EDIT" and then Call Family_Insert.

    EDIT builds up an UPDATE COMMAND, but then for it we specify the primary key, being id so that we update the right record. For this too we execute a callback that gets linked to "family_update" function.

    Code:
    Sub family_insert(success As Boolean, Result As List, Reason As String)
         
    If success Then
         
    'do something else
         Else
             
    Log(Reason)
             App.ToastError(
    "TablRecord could NOT be created successfully, please try again!")
         
    End If
     
    End Sub
    What can then happen after a success family_insert is that records can be read and displayed in a table or some other sub called that will do something different.

    Code:
    Sub family_update(success As Boolean, Result As List, Reason As String)
         
    If success Then
             
    'do something else
         Else
             
    Log(Reason)
             App.ToastError(
    "TablRecord could NOT be updated successfully, please try again!")
         
    End If
     
    End Sub
     
    joulongleu likes this.
  5. Mashiane

    Mashiane Expert Licensed User

    4. Reading records

    The assumption made here is that records can be read from the db using the primary key (id in this case) or all of them can be read. To achieve this, we will create a method to read the record from the table using the defined primary key.

    This is achieved by using SelectWhereMap where the table and then the field to use for the search and then a sort order. Again, we create the SELECT command and then execute a callback that is linked to the "family_read" function.

    So assuming that we had a family member stored using id=1, our method call will be

    Code:
    Read_family("1")
    Code:
    Sub Read_family(pk As string)
    'we are reading a record from the table using the primary key
         'lets define the select WHERE command
         Dim sqlm As Map = App.SQL_SelectWhereMap("family",CreateMap("id":pk),Array("id"))
         
    'lets get the select command
         Dim sql As String = sqlm.Get("sql")
         
    'let's get the arguements for the select
         Dim args As List = sqlm.Get("args")
         
    'let's execute the select and trap with a callback
         MyFamilySQL.ExecuteCallback(sql, args, Me, "family_read")
    End Sub

    Sub family_read(success As Boolean, Result As List, Reason As String)
         
    If success Then
             
    'display record when found
             'clear the contents of the page
             Page.ClearValues(Array("id","names","lastname","birthdate","birthplace","isliving","age","deathplace","deathdate","gender"))
             
    If Result.Size > 0 Then
         
    'read the first record in the list
                 Dim recordM As Map = Result.Get(0)
         
    'set the element values from the map
                 Page.SetValues(recordM)
             
    End If
         
    Else
             
    Log(Reason)
             App.ToastError(
    "Record could NOT be read successfully, please try again!")
         
    End If
     
    End Sub
    When the family_read methods runs a success, here we assumed that we have a UX thats created, we clear all the values in it and then set the values based on the first found record in the results we wanted.

    ClearValues is just a loop that does something like this

    Code:
    BANano.GetElement("#id").SetValue("")
    for each of the specified array elements.

    Each record in the database is saved as an object i.e. a map, thus we assign one with recordM = Result.Get(0) and then setValues.

    SetValues loops each record and get the key and value, each key is linked to the elementID on the page and then assigns the value with .GetElement().SetValue.
     
    joulongleu likes this.
  6. Mashiane

    Mashiane Expert Licensed User

    5. Deleting Records

    Again, to delete a record from our web database using BANanoSQL ExecuteCallBack, we need to define the DELETE COMMAND, then execute a callback that we trap and then run any other code we want in that callback.

    So below, assuming you have a family member stored as 2, you would call

    Code:
    Delete_family("2")
    Code:
    Sub Delete_family(pk As string)
    'we are deleting a record from the table using the primary key
         'lets define the delete WHERE command, note the EQUAL sign in the create map
         Dim sqlm As Map = App.SQL_DeleteWhereMap("family",CreateMap("id =":id))
         
    'lets get the delete command
         Dim sql As String = sqlm.Get("sql")
         
    'let's get the arguements for the delete
         Dim args As List = sqlm.Get("args")
         
    'let's execute the delete and trap with a callback
         MyFamilySQL.ExecuteCallback(sql, args, Me, "family_delete")
    End Sub

    Sub family_delete(success As Boolean, Result As List, Reason As String)
         
    If success Then
         
    'do something when record is deleted
         Else
             
    Log(Reason)
             App.ToastError(
    "Record could NOT be deleted successfully, please try again!")
         
    End If
     
    End Sub
    With DeleteWhereMap, we define the SQL command to run and also the arguments that we need to pass to that SQL statement. For example, a delete statement might use >=, <=, <>, so for our method here we have specified an equal sign for our command to be precise. The arguements are passed with the sql statement to the executecallback that gets trapped with family_delete after execution. There one can then execute other important code functionality...
     
    joulongleu likes this.
  7. Mashiane

    Mashiane Expert Licensed User

    Reference Code

    Here are some snippets that have been used here for more clarity. These again are greatly discussed here

    Code:
    Public const DB_BOOL As String = "BOOL"
        
    Public const DB_INT As String = "INT"
        
    Public const DB_STRING As String = "STRING"
        
    Public const DB_DATE As String = "DATE"
    Code:
    'return a sql command to create the table
    public Sub SQL_CreateTable(tblName As String, tblFields As Map, PK As StringAs String
        
    If tblFields.Size = 0 Then
            
    Log($"CreateTableSQL: ${tblName} - tblFields map empty!"$)
            
    Return Null
        
    End If
        
    Dim fldName As String
        
    Dim fldType As String
        
    Dim fldTot As Int
        
    Dim fldCnt As Int
        fldTot = tblFields.Size - 
    1
        
    Dim sb As StringBuilder
        sb.Initialize
        sb.Append(
    "(")
        
    For fldCnt = 0 To fldTot
            fldName = tblFields.GetKeyAt(fldCnt)
            fldType = tblFields.Get(fldName)
            
    If fldCnt > 0 Then
                sb.Append(
    ", ")
            
    End If
            sb.Append(EscapeField(fldName))
            sb.Append(
    " ")
            sb.Append(fldType)
            
    If fldName.EqualsIgnoreCase(PK) Then
                sb.Append(
    " PRIMARY KEY")
            
    End If
        
    Next
        sb.Append(
    ")")
        
    'define the qry to execute
        Dim query As String = "CREATE TABLE IF NOT EXISTS " & EscapeField(tblName) & " " & sb.ToString
        
    Return query
    End Sub

    'return a sql map to insert record to table. sql = query string, values = list of values
    Sub SQL_InsertMap(tblName As String, tblFields As MapAs Map
        
    If tblFields.Size = 0 Then
            
    Log($"InsertSQLMap: ${tblName} - tblFields map empty!"$)
            
    Return Null
        
    End If
        
    Dim sb As StringBuilder
        
    Dim columns As StringBuilder
        
    Dim values As StringBuilder
        
    Dim listOfValues As List
        
    Dim iCnt As Int
        
    Dim iTot As Int
        sb.Initialize
        columns.Initialize
        values.Initialize
        listOfValues.Initialize
        sb.Append(
    $"INSERT INTO ${EscapeField(tblName)} ("$)
        iTot = tblFields.Size - 
    1
        
    For iCnt = 0 To iTot
            
    Dim col As String = tblFields.GetKeyAt(iCnt)
            
    Dim value As String = tblFields.Get(col)
            
    If iCnt > 0 Then
                columns.Append(
    ", ")
                values.Append(
    ", ")
            
    End If
            columns.Append(EscapeField(col))
            values.Append(
    "?")
            listOfValues.Add(value)
        
    Next
        sb.Append(columns.ToString)
        sb.Append(
    ") VALUES (")
        sb.Append(values.ToString)
        sb.Append(
    ")")
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.ToString)
        m.Put(
    "args", listOfValues)
        
    Return m
    End Sub

    'return a sql to update record of table where one exists
    Sub SQL_UpdateWhereMap(tblName As String, tblFields As Map, tblWhere As MapAs Map
        
    If tblWhere.Size = 0 Then
            
    Log($"UpdateWhereSQLMap: ${tblName} - tblWhere map empty!"$)
            
    Return Null
        
    End If
        
    If tblFields.Size = 0 Then
            
    Log($"UpdateWhereSQLMap: ${tblName} - tblFields map empty!"$)
            
    Return Null
        
    End If
        
    Dim sb As StringBuilder
        
    Dim args As List
        sb.Initialize
        args.Initialize
        sb.Append(
    $"UPDATE ${EscapeField(tblName)} SET "$)
        
    Dim i As Int
        
    Dim iTot As Int = tblFields.Size - 1
        
    For i = 0 To iTot
            
    Dim col As String = tblFields.GetKeyAt(i)
            
    Dim colvalue As String = tblFields.Get(col)
            sb.Append(col)
            
    If i <> iTot Then
                sb.Append(
    "=?,")
            
    Else
                sb.Append(
    "=?")
            
    End If
            args.Add(colvalue)
        
    Next
        sb.Append(
    " WHERE ")
        
    Dim iWhere As Int = tblWhere.Size - 1
        
    For i = 0 To iWhere
            
    If i > 0 Then
                sb.Append(
    " AND ")
            
    End If
            
    Dim colwhere As String = tblWhere.GetKeyAt(i)
            
    Dim colvalue As String = tblWhere.Get(colwhere)
            sb.Append(colwhere)
            sb.Append(
    " = ?")
            args.Add(colvalue)
        
    Next
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.tostring)
        m.Put(
    "args", args)
        
    Return m
    End Sub

    'return a sql to delete record of table where one exists
    Sub SQL_DeleteWhereMap(tblName As String, tblWhere As MapAs Map
        
    If tblWhere.Size = 0 Then
            
    Log($"DeleteWhereSQLMap: ${tblName} - tblWhere map empty!"$)
            
    Return Null
        
    End If
        
    Dim sb As StringBuilder
        
    Dim args As List
        sb.Initialize
        args.Initialize
        sb.Append(
    $"DELETE FROM ${EscapeField(tblName)} WHERE "$)
        
    Dim i As Int
        
    Dim iWhere As Int = tblWhere.Size - 1
        
    For i = 0 To iWhere
            
    If i > 0 Then
                sb.Append(
    " AND ")
            
    End If
            
    Dim colwhere As String = tblWhere.GetKeyAt(i)
            
    Dim colvalue As String = tblWhere.Get(colwhere)
            sb.Append(colwhere)
            sb.Append(
    " ? ")
            args.Add(colvalue)
        
    Next
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.tostring)
        m.Put(
    "args", args)
        
    Return m
    End Sub

    'return a sql to update all records of table
    Sub SQL_UpdateMap(tblName As String, tblFields As MapAs Map
        
    If tblFields.Size = 0 Then
            
    Log($"UpdateSQLMap: ${tblName} - tblFields map empty!"$)
            
    Return Null
        
    End If
        
    Dim sb As StringBuilder
        
    Dim args As List
        sb.Initialize
        args.Initialize
        sb.Append(
    $"UPDATE ${EscapeField(tblName)} SET "$)
        
    Dim i As Int
        
    Dim iTot As Int = tblFields.Size - 1
        
    For i = 0 To iTot
            
    Dim col As String = tblFields.GetKeyAt(i)
            
    Dim colvalue As String = tblFields.Get(col)
            sb.Append(col)
            
    If i <> iTot Then
                sb.Append(
    "=?,")
            
    Else
                sb.Append(
    "=?")
            
    End If
            args.Add(colvalue)
        
    Next
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.tostring)
        m.Put(
    "args", args)
        
    Return m
    End Sub

    'return a sql to select record of table where one exists
    Sub SQL_SelectWhereMap(tblName As String, tblWhere As Map,orderBy As ListAs Map
        
    If tblWhere.Size = 0 Then
            
    Log($"SelectWhereSQLMap: ${tblName} - tblWhere map empty!"$)
            
    Return Null
        
    End If
        
    Dim sb As StringBuilder
        
    Dim args As List
        sb.Initialize
        args.Initialize
        sb.Append(
    $"SELECT * FROM ${EscapeField(tblName)} WHERE "$)
        
    Dim i As Int
        
    Dim iWhere As Int = tblWhere.Size - 1
        
    For i = 0 To iWhere
            
    If i > 0 Then
                sb.Append(
    " AND ")
            
    End If
            
    Dim colwhere As String = tblWhere.GetKeyAt(i)
            
    Dim colvalue As String = tblWhere.Get(colwhere)
            sb.Append(colwhere)
            sb.Append(
    " = ?")
            args.Add(colvalue)
        
    Next
        
    If orderBy <> Null Then
            
    'order by
            Dim stro As String = Join(",", orderBy)
            
    If stro.Length > 0 Then
                sb.Append(
    " ORDER BY ").Append(stro)
            
    End If
        
    End If
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.tostring)
        m.Put(
    "args", args)
        
    Return m
    End Sub


    'return a sql to select record of table where one exists
    Sub SQL_Select(tblName As String, flds As List, orderBy As ListAs Map
        
    Dim sb As StringBuilder
        
    Dim args As List
        sb.Initialize
        args.Initialize
        
    'define flds
        Dim strf As String = Join(",", flds)
        sb.Append(
    $"SELECT ${strf} FROM ${EscapeField(tblName)}"$)
        
    If orderBy <> Null Then
            
    'order by
            Dim stro As String = Join(",", orderBy)
            
    If stro.Length > 0 Then
                sb.Append(
    " ORDER BY ").Append(stro)
            
    End If
        
    End If
        
    Dim m As Map
        m.Initialize
        m.Put(
    "sql", sb.tostring)
        m.Put(
    "args", args)
        
    Return m
    End Sub
    Ta!

    #HelpingOthersToSucceed
     
    joulongleu 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