B4J Tutorial [BANanoSQL] CRUD with grid & modal using UOENow

Discussion in 'B4J Tutorials' started by Mashiane, Jun 3, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Ola

    Creating this demo app has has been done in 2 phases. The first phase has been to develop the components to use with the app into a BANano based library and secondly to build the resulting CRUD app using those components.

    The elements / components are HTML based elements (built as class modules), one can also define and create these as custom view components to use with the abstract designer. Creating components to use with the abstract designer is rather far more superior and more welcoming (i have found). This is due to the fact that you can link your events, methods and designer properties when you are creating a custom components, drop these in your abstract designer and generate members as you do with b4a, b4j and b4i.

    The UOENow BANano based lib is done following a coding methodology of doing things as it does not have an abstract designer based part yet. Due to the ease of using abstract designer based components, at some stage I will add such to the lib, either way the MO for CRUD functionality will be the same, i.e. the code that knits the UX and controller code.


    BANano1.png

    I fired up UOEProcess.Show to try and explain what we are trying to achieve.

    At the heart of a BANano app is the UX part and the coding part just like any other app one develops. The UX part you can create yourself or use BANanoSkeleton that comes with BANano or KendoUI etc. My UX part is UOENow, a Bootstrap based framework from Creative Tim. The free stuff from CT was what was the base for UOENow eventually, and we had to procure their professional suite for additional components and more designs.

    When creating a BANano app, one has to initialize the BANano library in AppStart, then add the CSS and JS files their app is using. After that comes the BANano.Build process. One can build their project as a library or an as app. When built as a library, the resulting code can be used in other BANano based libraries.

    During the initialization process, an event is fired depending on the event name specified, in most cases this is just BANano and this calls BANano_Ready. All the code written in b4j will be then converted into JavaScript by BANano for your app, which then can be published to a website or you run local.

    Before we publish we run the BANanoPostProcessor. As the source code for this is also available, we added functionality to also compress the css files using clean-css. The post processor needs Node.JS to run, so we have that installed as per thread in the BP.

    Our UOENow library uses a combination of CSS and JS files, as a result, before the BANano.Build process, we tag the CSS and JS resources that we actually need for each app and after the BP ensure that any other un-needed resources are removed from our Apps.

    For CSS and JS files though, it is recommended by BANano that one uses the available CDNs for better performance of their apps as it reports when one uses more CSS and JS files than necessary.

    One of the CONS for using UOENow is that whilst we are able to merge and compress the CSS files into 1, which is absolutely great by using the BP, we can only compress the JS files and cannot merge these into a single file as it breaks the code. We are still investigating and looking for alternatives.

    The demo app we will talk about is a wine register that uses a grid, page elements and then a modal sheet for editing and updating records.



    We use BANanoSQL as a backend for this SPA. We will look into the process of creating the database, creating the table, building SQL commands for CRUD and then take a look at the UX elements.

    It should be noted that because one can build their own UX for BANano, discussing the UX for us is not so crucial but rather the logic of the code. There are very good HTML5 components that one can find and build stuff, for example we found the Bootstrap Table very impressive for our exercise and build a component for ourselves. Its PRO was the ability to export to PDF, Excel, JSON, CSV etc.

    Let's dive in...

    Please note, the source code included here is for learning purposes. Due to license restrictions of CT Now UI Kit Pro, I have limited the attachment to just be B4J project with source code. I have included the code for the post processor thats adjusted to meet our needs.

    The BP needs NodeJS to be installed. For other tools, run..

    Code:
    npm install clean-css-cli -g
    and

    Code:
    npm install terser -g
    Related Thread:

    CRU-ding around with BANanoSQLUtils
     

    Attached Files:

    Last edited: Jun 3, 2019
    Don Oso and José J. Aguilar like this.
  2. Mashiane

    Mashiane Expert Licensed User

    Step 1: A sequence of fortunate events

    When the app start we want to show a grid with available records, the entry screen should also be built including the modal sheet that we will use for the entry screen.

    BANano2.png

    When BANano is ready (BANano_Ready) [1], we open our BANanoSQL database [2], this gets created if it does not exist. One can use OpenSQLWait also but we have opted for a call back based approach here, thus the existence of _SQLOpened. When the database is opened [3], we want our table to keep records for our wines to be defined. We call CreateTable_Wine [4], indicating the schema of our table. This table will be created only if it does not exist.

    After the table is created, we start to build the look and feel of our UX with Wine_CreateForm [5], this builds up the body of the page with the input components, buttons etc including the grid. As the grid used here is a custom component, adding it to the page was done via the abstract designer. This gets loaded to a particular RC (row column) of the page using .LoadLayout.

    Now, assuming there are records that are already stored in the wine table of our database, we load these and feed the grid with Load_Wine [6], this executes a select statement from our database. Our commands to the database are always parameter based calls and the callback method raised by the select statement is Wine_select [7].

    This ends the first part of our tasks, ensuring that the UX is created and the available records are listed in the grid. Existing records can then be edited and updated and new ones added.
     
    joulongleu likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    1.1 Let's begin with our AppStart

    Code:
    Sub AppStart (Form1 As Form, Args() As String)
        
    'initialize banano for first use
        Publish = "C:\xampp\htdocs"
        
    Dim version As String = DateTime.now
        BANano.Initialize(
    "BANano", ShortName,version)
        BANano.UseServiceWorker = 
    False
        BANano.HTML_NAME = 
    "index.html"
        BANano.Header.Title = AppName
        BANano.JAVASCRIPT_NAME = 
    $"app${version}.js"$
        
    'initialize the app
        App.Initialize(AppName,ShortName,Project,True)
        App.RequireSweetModal
        App.RequireAlaSQL
        App.RequireEasyHint
        App.AddScripts
        BANano.Header.AddCSSFile(ShortName.ToLowerCase & 
    ".css")
        BANano.Build(Publish)
        
    'post processor
        BP.Initialize
        BP.PublishPath = Publish
        BP.ShortName = ShortName
        BP.AssetsPath = BANano.ASSETS_FOLDER
        BP.ScriptsPath = BANano.SCRIPTS_FOLDER
        BP.StylesPath = BANano.STYLES_FOLDER
        BP.ScriptFile = BANano.JAVASCRIPT_NAME
        BP.UsesAlaSQL = 
    True
        BP.CompressJS = 
    True
        BP.CompressCSS = 
    True
        BP.MergeCSS = 
    True
        BP.CleanProject(App.DoNotDelete,App.Resources)
        BP.UnzipFonts
        BP.Open(
    True)
        
    ExitApplication
    End Sub
    As indicated in the first post, in AppStart we initialize BANano. We also initialize our UOENow library and also tag the needed resource files so that the BP does not thrash them when cleaning the project after build. As we will consolidate our css files into one after build, we add the single css file to the header. App.AddScripts will ensure that only the needed resource files (CSS + JS) are built with the project. As we have used BANano.Header.Add() outside AppStart, the compiler will give a warning for that.

    After build we run the post processor to compress the css and also merge them. CompressJS is also turned on. There are also inbuilt font files we are using then we let these be unzipped. This is being done as we are not using CDN as yet. With CDN, one wouldnt not need most of these processes.

    There is an online method to compress CSS built inside BANano, so using the BP might also be out, its just an option we decided to follow.

    1.2. BANano_Ready etc

    Code:
    Sub BANano_Ready()
         App.Initialize(AppName,ShortName,Project,
    False)
         DemoSQL.Open(
    "DemoSQL""Demo")
     
    End Sub

    Sub DemoSQL_SQLOpened()
         
    'create the Wine table
         CreateTable_Wine
    End Sub

    Sub CreateTable_Wine
        
    Dim tbc As Map
        tbc.Initialize
        tbc.Put(
    "id", App.EnumFieldTypes.DB_INT)
        tbc.Put(
    "winemaker", App.EnumFieldTypes.DB_STRING)
        tbc.Put(
    "winename", App.EnumFieldTypes.DB_STRING)
        tbc.Put(
    "color", App.EnumFieldTypes.DB_STRING)
        tbc.Put(
    "winedescription", App.EnumFieldTypes.DB_STRING)
        tbc.Put(
    "goodyears", App.EnumFieldTypes.DB_STRING)
        tbc.Put(
    "pricebottle", App.EnumFieldTypes.DB_REAL)
        tbc.Put(
    "pricehalfbottle", App.EnumFieldTypes.DB_REAL)
        tbc.Put(
    "labelpicture", App.EnumFieldTypes.DB_STRING)
        
    'define the command to run to build the table
     
        
    'NB: DO NOT SPECIFY THE AUTO INCREMENT FIELD NAME
        Dim sql As String = App.SQL_CreateTable("Wine",tbc,"id","")
        
    'execute the command to create the table and trap with a callback
        DemoSQL.ExecuteCallback(sqlNull, Me, "tablecreate_Wine")
    End Sub

    Sub tablecreate_Wine(success As Boolean, Result As List, Reason As String)
        
    If success Then
            
    'call the next process here when the table is created
            Wine_CreateForm
        
    Else
            
    'process the error here
            Log(Reason)
        
    End If
    End Sub
    One of the complex methods is the Wine_CreateForm, which builds up the UX of our page. This could have been done more easily with an abstract designer based form though. We create our form grid, place components, link events via code.

    NB: I would not recommend that you do this. Better use an abstract designer based approach as its more easier and cleaner.

    Code:
    Sub Wine_CreateForm
        
    Page.Initialize(App,"pageWine",True,True,App.EnumThemes.Primary)
        
    Page.NavBar.AddHamBurger
        
    Page.NavBar.AddBrand("brandWine","Wine","")
        
    Page.NavBar.SetSticky
        
    Page.NavBar.JustifyContentEnd
        
    'NB: The page content here including the modal content is added for demonstration. You can comment the page code to use only the modal
        'R1
        Page.Content.AddRows(1).AddColumns(1,"12","6","6","6")
        
    'R2
        Page.Content.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R3
        Page.Content.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R4
        Page.Content.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R5
        Page.Content.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    Page.Content.AddRows(1).AddColumns4_3
        
    Page.Content.AddRows(2).AddColumns12
        
    Page.Content.AddTextBoxTitle(1,1,"id","#","","")
        
    Page.Content.AddTextBoxTitle(2,1,"winemaker","Wine Maker","","")
        
    Page.Content.AddTextBoxTitle(2,2,"winename","Wine Name","","")
        
    Page.Content.AddTextBoxTitle(3,1,"color","Color","","")
        
    Page.Content.AddTextBoxTitle(3,2,"winedescription","Description","","")
        
    Page.Content.AddTextBoxTitle(4,1,"goodyears","Good Years","","")
        
    Page.Content.AddTextBoxTitle(4,2,"pricebottle","Price Bottle","","")
        
    Page.Content.AddTextBoxTitle(5,1,"pricehalfbottle","Half Bottle Price","","")
        
    Page.Content.AddTextBoxTitle(5,2,"labelpicture","Picture","","")
        
    Page.Content.AddButton2(6,1,"btnNew","New","",False,True,False,App.EnumThemes.Info,"","")
        
    Page.Content.AddButton2(6,2,"btnSave","Save","",False,True,False,App.EnumThemes.Success,"","")
        
    Page.Content.AddButton2(6,3,"btnUpDate","Update","",False,True,False,App.EnumThemes.Default,"","")
        
    Page.Content.AddButton2(6,4,"btnDelete","Delete","",False,True,False,App.EnumThemes.Danger,"","")
        Wine_CreateModal
    'create the page
        Page.Create
        App.BindClickEvent(
    "btnSave",Me)
        App.BindClickEvent(
    "btnDelete",Me)
        App.BindClickEvent(
    "btnUpdate",Me)
        App.BindClickEvent(
    "btnNew",Me)
        App.BindClickEvent(
    "btnWineSave",Me)
        App.BindClickEvent(
    "btnWineClose",Me)
        
    Dim grdPos As String = Page.Content.GetRC(8,1)
        
    Dim grdKey As String = "#" & grdPos
        BANano.LoadLayout(grdKey,
    "vWine")
        grdWine.HasDelete = 
    True
        grdWine.HasEdit = 
    True
        grdWine.PrimaryKey = 
    "id"
        grdWine.ShowColumns = 
    True
        grdWine.ShowExport = 
    True
        grdWine.ShowHeader = 
    True
        grdWine.ShowPagination = 
    True
        grdWine.ShowToggle = 
    True
        grdWine.ShowToolbar = 
    True
        grdWine.ShowFullscreen = 
    True
        
    'add columns
        grdWine.PrimaryKey = "id"
        grdWine.AddColumn(
    "id","#","text",0,False,"left")
        grdWine.AddColumn(
    "winemaker","Wine Maker","text",0,True,"left")
        grdWine.AddColumn(
    "winename","Wine Name","text",0,False,"left")
        grdWine.AddColumn(
    "color","Color","text",0,False,"left")
        grdWine.AddColumn(
    "winedescription","Description","text",0,False,"left")
        grdWine.AddColumn(
    "goodyears","Good Years","text",0,False,"left")
        grdWine.AddColumn(
    "pricebottle","Price Bottle","text",0,False,"left")
        grdWine.AddColumn(
    "pricehalfbottle","Half Bottle Price","text",0,False,"left")
        grdWine.AddColumn(
    "labelpicture","Picture","text",0,False,"left")
     
        
    Dim Result As List
        Result.Initialize
        grdWine.SetDataSource(Result)
        grdWine.refresh
        Load_Wine
    End Sub
    This method also adds a modal sheet to the page so that our edits/updates work well. This code returns a modal component for our page which gets fed into the page content i.e. div. The modal will have a header, body and footer. We also build a grid on the body of the modal and then feed elements to it. One can either use the modal for all data entry or use the page elements. These are both included in this case for explaination purposes.

    For example, when a user clicks Add to add a new wine record, the page elements are used and dummy data is generated for the record. As soon as the record is in the grid, selecting the respective grid row fires up a modal sheet for one to update the record and save it. Also deletes, as soon as delete is fired in a row, a prompt appears and records deleted once the confirmation is positive.

    Code:
    Sub Wine_CreateModal
        mdlWine.Initialize(App,
    "mdlEntry","",False,"")
        mdlWine.LargeSize
        mdlWine.CenterHeading
        mdlWine.AddTitle(
    "Add Wine")
        
    'R1
        mdlWine.Body.AddRows(1).AddColumns(1,"12","6","6","6")
        
    'R2
        mdlWine.Body.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R3
        mdlWine.Body.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R4
        mdlWine.Body.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        
    'R5
        mdlWine.Body.AddRows(1).AddColumns(1,"12","6","6","6").AddColumns(1,"12","6","6","6")
        mdlWine.Body.AddTextBoxTitle(
    1,1,"id","#","","")
        mdlWine.Body.AddTextBoxTitle(
    2,1,"winemaker","Wine Maker","","")
        mdlWine.Body.AddTextBoxTitle(
    2,2,"winename","Wine Name","","")
        mdlWine.Body.AddTextBoxTitle(
    3,1,"color","Color","","")
        mdlWine.Body.AddTextBoxTitle(
    3,2,"winedescription","Description","","")
        mdlWine.Body.AddTextBoxTitle(
    4,1,"goodyears","Good Years","","")
        mdlWine.Body.AddTextBoxTitle(
    4,2,"pricebottle","Price Bottle","","")
        mdlWine.Body.AddTextBoxTitle(
    5,1,"pricehalfbottle","Half Bottle Price","","")
        mdlWine.Body.AddTextBoxTitle(
    5,2,"labelpicture","Picture","","")

        mdlWine.Footer.AddButton2(
    0,0,"btnWineClose","Close","",False,True,False,App.EnumThemes.Danger,"","")
        mdlWine.Footer.AddButton2(
    0,0,"btnWineSave","Apply","",False,True,False,App.EnumThemes.Primary,"","")
        
    Page.Content.AddModal(mdlWine)
    End Sub
    NB: The code to create the page elements and modal is included here for completeness, I greatly support that one rather uses the abstract designer based approach for BANano based UX. As I mentioned before, the thread is more about the logic of the code than the UX.

    After the elements are loaded, either via code / using LoadLayout, the existing wine records are loaded on the page.

    Code:
    Sub Load_Wine()
        
    Dim sqlm As Map = App.SQL_Select("Wine",Array("*"), Array("winemaker"))
        
    Dim sql As String = sqlm.Get("sql")
        
    Dim args As List = sqlm.Get("args")
        DemoSQL.ExecuteCallback(
    sql, args, Me, "Wine_select")
    End Sub
    Sub Wine_select(success As Boolean, Result As List, Reason As String)
        
    If success Then
            grdWine.LoadData(Result)
        
    Else
            
    Log(Reason)
            App.ToastError(
    "The records could NOT be selected successfully, please try again!")
        
    End If
    End Sub
    As noted, the grid structure was done using .AddColumn etc so we only need to run grdWine.LoadData now, passing it the Result of our select callback from our BANanoSQL database.

    What happens when each record in the grid is clicked?

    There are two buttons in the grid per record. One is to edit and another to delete records. The grid table events are fired with a RowClick event. The column name of the record being selected is picked up, for edit its "edit" and for delete its "delete".

    Code:
    Sub grdWine_RowClick (row As Map, element As BANanoObject, field As String)
        
    Select Case field
            
    Case "edit"
                
    Dim sid As String = row.Get("id")
                Action = 
    "EDIT"
                mdlWine.Show
                Read_Wine(sid)
            
    Case "delete"
                
    Dim sid As String = row.Get("id")
                App.SweetModalConfirm(
    "Confirm Delete","Are you sure that you want to delete this record? You will not be able to undo your changes", App.CallBack("main", _
        
    "Delete_Wine"Array(App.InSingleQuote(sid))),Null)
        
    End Select
    End Sub
    On edit, the id of the row is read, when creating the grid, we indicated that the primarykey for the grid is "id". Then the Read_Wine method is executed to read the record from the db and later display it on the form.

    On delete, a prompt for a delete is provided. One can use the BANano based SweetAlert libs for their prompts. On on, the Delete_Wine sub is called to remove the record from the DB and reload the grid.
     
    Last edited: Jun 3, 2019
  4. Mashiane

    Mashiane Expert Licensed User

    Create / Inserts / Update Records

    1. For demo purposes, when a user selects New to add a new one record, a record is created from the dummy data generator, after the input elements are cleared. One can either opt to use the modal sheet to add the records but for this purpose, we only use the modal for edits/updates.

    BAnano3.png

    Code:
    Sub btnNew_click(e As BANanoEvent)
        
    'mdlWine.show
        Action = "ADD"
        App.ClearValues(
    Array("id","winemaker","winename","color","winedescription","goodyears","pricebottle","pricehalfbottle","labelpicture"))
        
    'App.SetFocus("firstname")
        Dim dummy As UOENowData
        dummy.Initialize
        
    Dim drec As Map
        drec.Initialize
        drec.put(
    "id","id")
        drec.put(
    "winemaker","word")
        drec.put(
    "winename","word")
        drec.put(
    "color","hexcolor")
        drec.put(
    "winedescription","word")
        drec.put(
    "goodyears","year")
        drec.put(
    "pricebottle","money")
        drec.put(
    "pricehalfbottle","money")
        drec.put(
    "labelpicture","word")
        
    Dim drecs As List = dummy.GetRecordsWithStructure(drec,1)
        
    Dim dRow As Map = drecs.Get(0)
        dRow.put(
    "id",dummy.Rand_PinCode(4))
        App.SetValues(dRow)
     
    End Sub
    As soon as we press save, the code in the save button reads the element values and then saves this to the db. For new the action = "ADD".

    Code:
    Sub btnSave_click(e As BANanoEvent)
        Insert_Wine
    End Sub
    Code:
    Sub Insert_Wine
        
    'check any compulsory fields
         Dim bRequired As Boolean = App.Required(Array("id","winemaker","winename","color","winedescription","goodyears","pricebottle","pricehalfbottle","labelpicture"))
        
    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 = App.GetValues(Array("id","winemaker","winename","color","winedescription","goodyears","pricebottle","pricehalfbottle","labelpicture"))
        
    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("Wine",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
                 DemoSQL.ExecuteCallback(sql, args, Me, "Wine_insert")
             
    Case "EDIT"
                 
    'we are editing a record, we need an update command, let's build it
                 Dim sqlm As Map = App.SQL_UpdateWhereMap("Wine",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
                DemoSQL.ExecuteCallback(sql, args, Me, "Wine_update")
        
    End Select
    End Sub
    The next step of the CREATE method here happens when a user clicks the save button. The action being performed is determined whether ADD/EDIT. On ADD, there should be an INSERT call whilst when we have an EDIT, there should be an UPDATE call to the database table.

    After both the insert / update, the records should be loaded to the grid.

    BAnano4.png



    When a user selects save, the action is already set at ADD, the Insert_Wine method is called, this determines the action, when ADD, an INSERT is called and then the wine list is loaded to the grid after a SELECT command is called to get all records from the DB. This ends the process.

    When a user selects update (btnSave/btnWineSave), the option is set to EDIT, Insert_Wine is called and when the UPDATE command executes, all records are selected from the DB and loaded to the grid.
     
    Last edited: Jun 3, 2019
  5. Mashiane

    Mashiane Expert Licensed User

    Reading Records

    Once a record has been INSERT(ed) and UPDATE(d) on the table it can be READ.

    To read our records for this example, this happens when one selects the "edit" button in the grid. The id of the record on the selected row is read, this is used to execute Read_Wine using that id. We have previously indicated that the primary key for the grid is the id. So this saves internally each id for each row in the table. After the record is read, a callback, Wine_Read is called. This gets the first record in the list of records reads and set the values to update the input elements on the page.

    Code:
    Sub grdWine_RowClick (row As Map, element As BANanoObject, field As String)
        
    Select Case field
            
    Case "edit"
                
    Dim sid As String = row.Get("id")
                Action = 
    "EDIT"
                mdlWine.Show
                Read_Wine(sid)
            
    Case "delete"
                
    Dim sid As String = row.Get("id")
                App.SweetModalConfirm(
    "Confirm Delete","Are you sure that you want to delete this record? You will not be able to undo your changes", App.CallBack("main", _
        
    "Delete_Wine"Array(App.InSingleQuote(sid))),Null)
        
    End Select
    End Sub
    BANano6.png

    Code:
    Sub Read_Wine(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("Wine",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")
        
    Log("Read_Wine")
         
    'let's execute the select and trap with a callback
         DemoSQL.ExecuteCallback(sql, args, Me, "Wine_read")
    End Sub

    Sub Wine_read(success As Boolean, Result As List, Reason As String)
         
    If success Then
             
    'display record when found
             'clear the contents of the page
             App.ClearValues(Array("id","winemaker","winename","color","winedescription","goodyears","pricebottle","pricehalfbottle","labelpicture"))
             
    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
                 App.SetValues(recordM)
             
    End If
         
    Else
            
    Log(Reason)
            App.ToastError(
    "Record could NOT be read successfully, please try again!")
        
    End If
    End Sub




    Delete Records


    Above we saw how when an add / update is executed to add / update the records. When a record is selected on the grid with the edit button, the edit mode (i.e. action) is turned into EDIT mode, this enables records to be updated. This was the same with new, which turned the edit mode to ADD thus turning records for inserts.

    When deleting records, a prompt is shown for the user to confirm.

    Code:
    Sub btnDelete_click(e As BANanoEvent)
        
    Dim sid As String = App.GetValue("id")
        App.SweetModalConfirm(
    "Confirm Delete","Are you sure that you want to delete this record? You will not be able to undo your changes", App.CallBack("main", _
        
    "Delete_Wine"Array(App.InSingleQuote(sid))),Null)
    End Sub
    This calls the Delete_Wine method.

    BAnano5.png

    Delete_Wine - this builds the SQL command for the DELETE statement
    Wine_Delete - this is the callback when the DELETE statement has been fired.

    As noted, all these commands lead to Load_Wine, which runs a statement to load all existing records from the db to the table and end the process.

    Code:
    Sub Delete_Wine(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("Wine",CreateMap("id =":pk))
         
    '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
         DemoSQL.ExecuteCallback(sql, args, Me, "Wine_delete")
    End Sub

    Sub Wine_delete(success As Boolean, Result As List, Reason As String)
         
    If success Then
             
    'do something when record is deleted
             'clear the contents of the page
             'App.ClearValues(Array("id","winemaker","winename","color","winedescription","goodyears","pricebottle","pricehalfbottle","labelpicture"))
             'App.ToastSuccess("The record was deleted successfully!")
             Load_Wine
         
    Else
             
    Log(Reason)
             App.ToastError(
    "Record could NOT be deleted successfully, please try again!")
         
    End If
    End Sub
     
    Last edited: Jun 3, 2019
  6. Mashiane

    Mashiane Expert Licensed User

    Looking at the SQL Commands against BANanoSQL for our Wine Register

    This last part of the thread, we look at the actual SQL commands being executed througout the application..

    1. CREATE TABLE

    Code:
    CREATE TABLE IF NOT EXISTS [Wine] ([id] INT PRIMARY KEY, [winemaker] STRING, [winename] STRING, [color] STRING, [winedescription] STRING, [goodyears] STRING, [pricebottle] REAL, [pricehalfbottle] REAL, [labelpicture] STRING)
    2. SELECT (For loading all records)

    Code:
    SELECT * FROM [Wine] ORDER BY winemaker
    2.1 SELECT (for reading 1 single record)

    Code:
    SELECT * FROM [Wine] WHERE id = ? ORDER BY id
    3. INSERT

    Code:
    INSERT INTO [Wine] ([id], [winemaker], [winename], [color], [winedescription], [goodyears], [pricebottle], [pricehalfbottle], [labelpicture]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    4. UDPATE

    Code:
    UPDATE [Wine] SET id=?,winemaker=?,winename=?,color=?,winedescription=?,goodyears=?,pricebottle=?,pricehalfbottle=?,labelpicture=? WHERE id = ?
    5. DELETE

    Code:
    DELETE FROM [Wine] WHERE id = ?
    Ta!

    This closes my experiences with BANanoSQL. Now on my way to look at inline PHP crud for a similar exercise.

    #HelpingOthers2Succeed
     
    Don Oso 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