B4J Tutorial [BANanoSQL] Understanding CRUD logic with ExecuteCallBack

Mashiane · May 13, 2019

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.

B4X:

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.

B4X:

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.

B4X:

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.

Mashiane · May 13, 2019

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.

B4X:

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(sql, Null, 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.

Mashiane · May 13, 2019

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.

B4X:

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.

B4X:

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.

B4X:

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

Mashiane · May 13, 2019

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

B4X:

Read_family("1")

B4X:

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

B4X:

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.

Mashiane · May 13, 2019

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

B4X:

Delete_family("2")

B4X:

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...

Mashiane · May 13, 2019

Reference Code

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

B4X:

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"

B4X:

'return a sql command to create the table
public Sub SQL_CreateTable(tblName As String, tblFields As Map, PK As String) As 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 Map) As 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 Map) As 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 Map) As 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 Map) As 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 List) As 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 List) As 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

B4J Tutorial [BANanoSQL] Understanding CRUD logic with ExecuteCallBack

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert