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

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.

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

npm install clean-css-cli -g

and

npm install terser -g

Related Thread:

CRU-ding around with BANanoSQLUtils

 

[Mashiane]

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.

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.

 

[Mashiane]

1.1 Let's begin with our AppStart

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

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

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.

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.

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

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.

 

[Mashiane]

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.

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

Sub btnSave_click(e As BANanoEvent)
    Insert_Wine
End Sub

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.

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.

 

[Mashiane]

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.

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

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.

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.

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.

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

 

[Mashiane]

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

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)

SELECT * FROM [Wine] ORDER BY winemaker

2.1 SELECT (for reading 1 single record)

SELECT * FROM [Wine] WHERE id = ? ORDER BY id

3. INSERT

INSERT INTO [Wine] ([id], [winemaker], [winename], [color], [winedescription], [goodyears], [pricebottle], [pricehalfbottle], [labelpicture]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

4. UDPATE

UPDATE [Wine] SET id=?,winemaker=?,winename=?,color=?,winedescription=?,goodyears=?,pricebottle=?,pricehalfbottle=?,labelpicture=? WHERE id = ?

5. DELETE

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

 

[Mashiane]

Update: The wine example has now been updated to include most UOENow input controls.

We have split the form into 2 sections of rows and columns that are spanning 6 columns each, except for the text-area. We are also able to toggle the grid, especially useful for mobile devices.