B4J Tutorial Web API Server (v3)

aeric · Oct 30, 2024

Part #2: Configure Settings

B4J generate a config.ini when you compile the project for the first time in Debug (or Release) mode.
You can open the file using Notepad or any Text Editor.
After you made any changes, you need to compile the project again for the settings to take effect.

B4X:

# Define App Constants
HOME_TITLE=WEB API SERVER
APP_TITLE=Web API Server
APP_TRADEMARK=B4X
APP_COPYRIGHT=Copyright Computerise System Solutions 2024

You can edit these settings.
It will applies to the text displayed on the front-end and API documentation page.
Similar to config.properties, the values does not support UTF8. You may want to set text for non-English characters in the code.

B4X:

# Server Path
ROOT_URL=http://127.0.0.1
ROOT_PATH=
API_NAME=api
API_VERSIONING=False

ROOT_URL (default=http://127.0.0.1) is for setting the server URL. You can edit to your domain name in production release.
Note: It seems Chrome is no longer allowed http://localhost so it is better to stick with http://127.0.0.1 during development.
ROOT_PATH (default=<empty>) can be set to other name e.g /web
API_NAME (default=api) is better to leave as default. You need to change the values in code, html templates and JavaScript files if you edit it.
API_VERSIONING (default=False) can be set to e.g /v1 or /preprod

B4X:

# Java server port
ServerPort=8080
SSLPort=0

It is important to set the correct number for B4J server port.
Make sure the port number is not used by another service or blocked by the firewall.
You can set the port e.g 80
It is recommended to use SSL protocol in production. If SSLPort number is set to 0, SSL is disabled.
You can set it to e.g 443

B4X:

# SSL Keystores
# Generate a keystore from Windows CMD
# C:\Java\jdk-11.0.1\bin\keytool -keystore keystore -alias jetty -genkey -keyalg RSA
# Copy keystore file to Objects folder of the B4J project
SSL_KEYSTORE_DIR=/etc/letsencrypt/live/mydomain.com
SSL_KEYSTORE_FILE=keystore
SSL_KEYSTORE_PASSWORD=xxxxxxxxx

If SSLPort is not set to 0 (enabled), you need to set the keystore settings.
During development, you can use CMD to call keytool in the JDK to generate a self sign keystore file.
Put this file in Objects folder and leave the SSL_KEYSTORE_DIR setting as <empty>.
During production, you can use a real certificate or generate one using LetsEncrypt.

B4X:

# DATABASE CONFIGURATION

## SQLite configuration:
DbType=SQLite
DbFile=webapi3.db
DbDir=
DriverClass=org.sqlite.JDBC
JdbcUrl=jdbc:sqlite:{DbDir}/{DbFile}

SQLite is set as default DbType.
You can change the DbFile as you like e.g server.sqlite
You can leave DbDir as <empty> to store the file inside Objects directory.
If you want to save the file in a different location, you need to set it using forward slash or escape character e.g C:/server/db or C:\\server\\db
It is recommended to leave the values for DbType, DriverClass and JdbcUrl as default.

If you want to use MySQL as backend database, uncomment the settings like the following example.

B4X:

## MySQL configuration:
DbType=MySQL
DbName=webapi3
DbHost=localhost
DbPort=
DriverClass=com.mysql.cj.jdbc.Driver
JdbcUrl=jdbc:mysql://{DbHost}:{DbPort}/{DbName}?characterEncoding=utf8&useSSL=false
User=root
Password=password
MaxPoolSize=100

You can leave DbPort as <empty> if MySQL is running on default port 3306.
You can edit the DbName, User, Password, MaxPoolSize or other settings according to your needs.

This is the end of tutorial for Part #2. Thanks for taking your time to read it.

Always start a new question with the name Web API Server 3 if you have any question on this tutorial.

aeric · Oct 30, 2024

Part #3: How Does the Project Work?

First thing to know, this is a B4J server app. Therefore, it depends on the B4J Server library.
You also need to understand, it is a non-UI or console app. It doesn't have native UI library or component.
You can't add any B4J library that depends on XUI or jFX.
After the project is compiled as Release, you can run the binary file on Windows CMD or Terminal on Linux.
To host the server app, it is recommended to find a VPS that you have full access to run the binary using a remote SSH terminal.
You also need to have a JDK on the server for you to call the .jar file.
It is recommended that you put the compiled binary file e.g server.jar, together with the www directory inside a location that you have write permission.
The server will have to write it's access log and database file if you are using SQLite to keep inside the location same as the binary file.
You also need to make sure the server can read the config.ini file and contents inside www directory.
You can follow other tutorials about running B4J Server on this forum.

Starting the Server

To start the server, you Initialize the object as global in Main module and call the method Start.
We should configure the settings before we start the server.
The default server port number for B4J server is 8080.
We can change this setting using InitServer sub in the Main module.

Initializing Server Configuration

In this project, we use config.ini to make changes to the server configuration without needing to recompile the project.
The settings in config.ini has been explained in the previous tutorial.
This project has embedded with a config.example file in the assets or Files directory.
If you have deleted the config.ini file, a new config.ini file will be copied from the example template.
InitServer sub will read config.ini file for the configuration settings.
All the configuration will be assigned to a Map object name ctx where it can be read from anywhere of the project and also use by HTML template engine, such as for displaying the title label and version number.

EnableCORS

This setting must be enabled to allow another application (from different origin) to access our API especially when using JavaScript.
If you have another web app hosted with different port number e.g Vue app running on port 3000 and this server is running on port 8000 then you need to enable CORS or otherwise you will get errors in web browser.
You can disable this setting if you are sure there are no other application sharing the API.
This is not applied to mobile or desktop native client apps want to access the API.

EnableSSL

This setting is recommended to be set to True during production to protect the data transfer being encrypted between the server and client.
As explained in previous tutorial, you need to configure the SSLPort to number other than 0 and provide information of the keystore.

EnableHelp

Basically this setting is use to hide the API documentation link to be visible to the end user.
You can also use Conditional symbol to add or disable the HelpHandler during production.

SimpleResponse

This setting allows you to choose the API output in different JSON format.
By default, it is not enabled.
This means the output of the JSON Response follows a predefined structure as set inside ReturnHttpResponse sub of WebApiUtils library.
Learn more about SimpleResponse in next tutorial.

StaticFilesFolder

Usually the static files folder is set as www inside Objects during development.
It is recommended to leave the folder name as www or you may want to change as public.
After compiled as Release, it will become the same level as the jar file.

StaticFilesBrowsable

You may want to restrict the client to list the contents of your www assets directory from the browser.

You can set StaticFilesBrowsable to False which is by default also set as False.

B4X:

Config.StaticFilesFolder = File.Combine(File.DirApp, "www")
Config.StaticFilesBrowsable = False

Version

The VERSION_NAME is set in Process_Globals which is just for displaying purpose in front end and terminal log as "welcome message".
B4X:
```
Config.Version = VERSION_NAME
```

Applying Server Configuration

The ApplySettings sub will take care of applying the settings if you have override any inside InitServer sub.
You must not modify any code inside ApplySettings sub.

Initializing Database Configuration

The InitDatabase sub will take care of applying the settings read from the config.ini file.
You must not modify any code inside InitDatabase sub.

Connecting to the Database

Based on the Build Configuration that you selected, the project is connecting to SQLite by default.
You need to select MySQL from the dropdown list if you want to use MySQL database.
CreateConnection sub will take care of applying the settings and attempt to connect to the database.
It will also check if database is exist or else call CreateDatabase sub.

Creating the Demo Database

If no database is present, the CreateDatabase sub will create a new database, generate the tables and insert some dummy data.
As this template is using MiniORMUtils library, the same code is use to accomplish this task, regardless of whether you choose SQLite or MySQL.
The SQL queries are handled internally by the library despite the differences between the 2 databases.
You may want to learn about using MiniORMUtils in another tutorial or post a new question on this library.

Configure Cross Origin (CORS)

If you have set Config.EnableCORS = True, then you may want to configure the Cross Origin settings on this server accessed from another origin or server inside the ConfigureCORS sub.
This is the case when you have another application that is using JavaScripts to make API calls to this server.

By default, there is already an example added for you.

B4X:

Paths.Add(CreateMap("path": "/api/*", "origins": "http://localhost, http://127.0.0.1:3000", "methods": "POST,PUT,DELETE", "headers": "*"))

This mean you are allowing another application specified in the list of "origins" to access our /api "path" and its derivatives using the "methods" POST, PUT and DELETE.

Configure SSL for the Server

The ConfigureSSL sub will take care of setting up the SSL. You must not change any code in this sub.
This sub will display some messages in the Logs depending on the settings for EnableSSL and the settings in config.ini file.

Configure Static Files

The ConfigureStaticFiles sub will take care of setting up the directory name and browse permission of static file folder. You must not change any code in this sub.

Switching of Database

If you have decided to switch to another database by selecting a different item from the Build Configurations dropdown list, make sure you have commented the SQLite configuration explained in the previous tutorial Part #2.
If the configuration does not match, the ShowBuildConfigurationNotMatch sub will display a warning message and the application will be terminated immediately.

Commonly Use Subs

You can add extra subs to the Main module so they are reusable in any of the server handler classes.
There are 3 subs added for you for communicating with the database.

Note that DBConnector is part of the MiniORMUtils library.

B4X:

Public Sub DBEngine As String
    Return DBConnector.DBEngine
End Sub

Public Sub DBOpen As SQL
    Return DBConnector.DBOpen
End Sub

Public Sub DBClose
    DBConnector.DBClose
End Sub

This is the end of tutorial for Part #3. Thanks for taking your time to read it.

Always start a new question with the name Web API Server 3 if you have any question on this tutorial.

aeric · Oct 30, 2024

Part #4: Server Handlers

There are 4 types of Class for writing code in B4J.

Standard Class
Server Filter
Server Handler
Server WebSocket

In this tutorial, we will focus on Server Handler.
This is the class that we use most of the time to write all of the business logic for the API.

Conventions

For this Web API Server template, we use the following naming convention to differentiate the purpose of the handler class.
- Api Handler - to read the Request from the URI contains the path /api and output the Response as JSON
- Web Handler - to read the Request from the URI without the /api path and output the Response as HTML
For e.g there are 2 handlers for Categories i.e CategoriesApiHandler and CategoriesWebHandler.
You can either have Api handler and/or Web handler.
As you can guess, CategoriesApiHandler is use for taking care of the API endpoints for Categories while CategoriesWebHandler is use to display the web page to manage the Categories.
IndexWebHandler is use to display the index page when a user browse to the root path.
The index page calls the API endpoints from ProductsApiHandler and FindApiHandler.
HelpHandler is use to display the API documentation page. It will be use to call any API endpoint that you have created.
Each Web handler is linked to its respective JavaScript file to make AJAX calls and process the returned JSON Response from an Api handler.

Adding Server Handler

There are 2 ways to add a Server Handler.
We can click on the menu Project, Add New Module, Class Module,
However, I recommend another way. Right click on the Handlers group in Modules tab and select Server Handler.
By this way, we don't need to drag the class into the group later.
Enter the name of the handler and click Ok.

An empty class with a Handle sub is generated.

B4X:

'Handler class
Sub Class_Globals
   
End Sub

Public Sub Initialize
   
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
   
End Sub

Please continue to read tutorial Part #6 on how to use Code Snippets to add boilerplate code for the handler.

This is the end of tutorial for Part #4. Thanks for taking your time to read it.

Always start a new question with the name Web API Server 3 if you have any question on this tutorial.

aeric · Oct 30, 2024

About SimpleResponse
This tutorial is extended from Part #3 Initializing Server Configuration.

SimpleResponse.Enable = False

By default, a predefined structure is return when using ReturnHttpResponse sub of WebApiUtils to output the JSON response.

I recommend to use the default because it covers the response status code, message and error message without the need to worry whether the response should be an array or object. The response data is always returned as an array (list).
Here is an example of the output:

JSON:

{
  "a": 200,
  "r": [
    {
      "deleted_date": null,
      "category_id": 2,
      "id": 3,
      "product_price": 1000,
      "created_date": "2024-10-30 08:33:13",
      "product_code": "T002",
      "modified_date": null,
      "product_name": "Optimus Prime"
    }
  ],
  "s": "ok",
  "e": null,
  "m": "Success"
}

You won't notice this format in API documentation because only the response 'r' item is show. A small label is display below the response box results from the combination of 'a' and 'm' / 'e' items.

For details, you can check the JavaScript code of help.js file inside scripts folder of www/assets.

JavaScript:

success: function (data) {
    if (data.s == "ok" || data.s == "success") {
        var content = JSON.stringify(data.r, undefined, 2)
        $("#response" + id).val(content)
        $("#alert" + id).html(data.a + ' ' + data.m)
        $("#alert" + id).removeClass("alert-danger")
        $("#alert" + id).addClass("alert-success")
        $("#alert" + id).fadeIn()
    }
    else {
        var content = JSON.stringify(data.r, undefined, 2)
        $("#response" + id).val(content)
        $("#alert" + id).html(data.a + ' ' + data.e)
        $("#alert" + id).removeClass("alert-success")
        $("#alert" + id).addClass("alert-danger")
        $("#alert" + id).fadeIn()
    }
}

SimpleResponse.Enable = True

You will notice in this version, the setting is set to True inside the code.
B4X:
```
Config.SimpleResponse.Enable = True
```
Note: You can comment the code if you want.
This is the case where you want to create API that follow the structure you want.

When this setting is set to True, the JSON format as showed above is now show with just the 'r' part, exactly as what you see in the Response text area in API documentation page.

JSON:

{
    "deleted_date": null,
    "category_id": 2,
    "id": 3,
    "product_price": 1000,
    "created_date": "2024-10-30 08:33:13",
    "product_code": "T002",
    "modified_date": null,
    "product_name": "Optimus Prime"
}

You can actually compare the difference by viewing the actual output by pasting the API end point to the browser address.
Take note that you can only see the output for GET request in browser.

Debugging using Developer Tools

We can use the Developer Tool in web browser for debugging the API response.
First we need to write console.log() inside the JavaScript for AJAX call.
This is useful for debugging other methods such as POST, PUT and DELETE.
When Config.SimpleResponse.Enable = True, the response is in simple format.
The page for API documentation will load help.simple.js file to make AJAX calls.
Open help.simple.js using a Text Editor and add the following code at line #88.
console.log(data)

The code will look like the following:

JavaScript:

case element.hasClass("get"):
    return {
        type: "GET",
        headers: headers,
        success: function (data, textStatus, xhr) {
            console.log(data)
            var content = JSON.stringify(data, undefined, 2)
            $("#response" + id).val(content)
            $("#alert" + id).html(xhr.status + ' ' + textStatus)
            $("#alert" + id).removeClass("alert-danger")
            $("#alert" + id).addClass("alert-success")
            $("#alert" + id).fadeIn()
        },
        error: function (xhr, textStatus, errorThrown) {
            var content = xhr.responseText
            $("#response" + id).val(content)
            $("#alert" + id).html(xhr.status + ' ' + errorThrown)
            $("#alert" + id).removeClass("alert-success")
            $("#alert" + id).addClass("alert-danger")
            $("#alert" + id).fadeIn()
        }
    }
    break

After changes is saved, you need to refresh the JavaScript source by right clicking on the page and choose view page source (Ctrl+U).
Scroll down to the bottom of the page until you see
<script src="http://127.0.0.1:8080/assets/scripts/help.simple.js"></script>
Click on the link to open it on a new tab and right-click Reload (Ctrl+R).
Open the Dev Tools by right clicking on the page (on Google Chrome) and choose Inspect (usually you need to scroll to bottom of the pop up menu) or just press combo keys of Ctrl+Shift+I.
The Dev Tools panel will appear. Usually it is focusing on the Console tab. Otherwise you need to click on the Console tab.
Now try to make a new GET request.
You should see an arrow with text Object appeared and on the left showing 1 user message.
On the right you see the name of the JavaScript file colon the line number on the code that produce the message log.
Click the Object to expand it. You can see the content of the JSON object return from the API.
If it is not working, probably you need to restart the server. Stop the debug and click run again. Then try again.
The default JSON Format for SimpleResponse is "Auto".
You can change the Format by setting it to "Map" to return as object (or "List" to return as array) for all the API responses.
By changing it to Map, you need to specify the key of the "Map" using the DataKey property.
If you don't specify a key name, it will use "data" as the default key.
B4X:
```
Config.SimpleResponse.Initialize
Config.SimpleResponse.Enable = True
Config.SimpleResponse.Format = "Map"
Config.SimpleResponse.DataKey = "result"
```
After made this changes, now try to restart the debug (F11) and see the difference of output but this time we test on the API end point that should return an array (list) which is the /api/products end point.
So you see the response is now return as object (wrapped in curly brackets) instead as an array (wrapped in square brackets) and it is using the key that we specified.

Conclusion

There are 4 types of output for SimpleResponse:

Enable	Format	Return data
False	(Fixed)	object with r data always an array
True	Auto (default)	depends whether we initialize object or data
True	Map	always as object (with optional customize key name)
True	List	always as array

It depends on your use case which type is suitable.
You can build more complex JSON response by nesting the objects and arrays.

This is the end of tutorial explaining about SimpleResponse. Thanks for taking your time to read it.

Always start a new question with the name Web API Server 3 if you have any question on this tutorial.

aeric · Nov 1, 2024

Part #5: Add Boilerplate Code to Handler Using Code Snippets

We have learned in Part #4 that we need to write our code inside Server Handlers.
We can save time by generating some boilerplate code using Code Snippets included inside WebApiUtils.
We continue to work with UsersApiHandler.
Let's delete all the code but left the Class_Globals sub.

Note: We can also use a Standard class.
Put your cursor inside Class_Globals sub, now start typing "globals".

Select the "Api Class Globals" Code Snippet using the Up/Down key and press Enter.

The result is the following:

B4X:

Sub Class_Globals
        Private Request As ServletRequest
        Private Response As ServletResponse
        Private HRM As HttpResponseMessage
        Private DB As MiniORM
        Private Method As String
        Private Elements() As String
        Private ElementId As Int
End Sub

The variable names are quite self explanatory. We will be using HttpResponseMessage from WebApiUtils and MiniORM class.
Put the cursor after the Class_Globals sub and start typing "handler".
Use the Up/Down key to select "Api Handler" code snippet then press Enter.
The default code is added. You will notice there are some texts are highlighted.
Replace "EndPoints" with "Users" for plural word and "EndPoint" with "User" for singular word.
Press Tab key to switch to next occurrences if they exist.
Here, we also need to change the "TableName" to the name of your users table.
When the cursor returned to the first highlighted text, we can press Enter to accept the changes.
Now the handler is ready. You can make further customization to this handler.

Take note that we still need to create the users table and add the handler to the server.

Main:

Sub AppStart (Args() As String)    srvr.Initialize("")
    srvr.AddHandler("", "IndexWebHandler", False)                           ' Home handler
    srvr.AddHandler("/categories/*", "CategoriesWebHandler", False)         ' Web handler
    srvr.AddHandler("/api/categories/*", "CategoriesApiHandler", False)     ' API handler
    srvr.AddHandler("/api/products/*", "ProductsApiHandler", False)         ' API handler
    srvr.AddHandler("/api/users/*", "UsersApiHandler", False)               ' API handler
    srvr.AddHandler("/api/find/*", "FindApiHandler", False)                 ' API handler
    srvr.AddHandler("/help", "HelpHandler", False)                          ' Help handler

To add the endpoints to API documentation, add the code inside ReadHandlers sub.

HelpHandler:

Public Sub ReadHandlers (FileDir As String) As String
    Dim strHtml As String
    Log(TAB)
    Log("Generating Help page ...")
    Dim verbs(4) As String = Array As String("GET", "POST", "PUT", "DELETE")
    Dim DocumentedHandlers As List
    DocumentedHandlers.Initialize
    Dim Handlers As List
    Handlers.Initialize
    Handlers.Add("CategoriesApiHandler")
    Handlers.Add("ProductsApiHandler")
    Handlers.Add("UsersApiHandler")
    Handlers.Add("FindApiHandler")

The steps are similar for adding code snippets for Web handler.

This is the end of tutorial for Part #5. Thanks for taking your time to read it.

Always start a new question with the name Web API Server 3 if you have any question on this tutorial.

aeric · Nov 3, 2024

Next, check on the tutorial on using MiniORMUtils.

Using MiniORMUtils in Web API Server 3

Introduction At the time of writing, MiniORMUtils is version 1.14 and Web API Server 3 is version 3.00. MiniORMUtils support SQLite and MySQL databases. We don't need to write different SQL queries to work with SQLite and later migrate to MySQL. How to Use MiniORMUtils When we run Web API...

www.b4x.com

aeric · Nov 8, 2024

Part #6: Add a New Field

This is a tutorial copied from the conversation between a member and I in a private message.
I would like to share this for benefit of other members.
This is a guide to add an Integer field for "opening stock".
In this example, we make it compulsory to supply the value during Add new product in index page.

Modify Product Table

Go to CreateDatabase sub in Main module.

We need to add the following code

B4X:

MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "opening_stock", "Type": MDB.INTEGER, "Default": "0")))

Here we are adding a column of type integer and default the value to 0.

Now the code will look like the following:

B4X:

MDB.Table = "tbl_products"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_id", "Type": MDB.INTEGER)))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "product_code", "Length": "12")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "product_name")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "product_price", "Type": MDB.DECIMAL, "Length": "10,2", "Default": "0.00")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "opening_stock", "Type": MDB.INTEGER, "Default": "0")))
MDB.Foreign("category_id", "id", "tbl_categories", "", "")
MDB.Create

Next, we open ProductsApiHandler class to modify the code for POST and PUT endpoints.

In PostProduct sub we update the #Body comment to accept a new item with key "stock".

B4X:

' #Body = {<br>&nbsp;"cat_id": category_id,<br>&nbsp;"code": "product_code",<br>&nbsp;"name": "product_name",<br>&nbsp;"price": product_price,<br>&nbsp;"stock": 0<br>}

This item will be map to our column "opening_stock".
We add a code to check the "stock" key in request body or json payload and replace it with the correct table column.
B4X:
```
If data.ContainsKey("stock") Then
    data.Put("opening_stock", data.Get("stock"))
    data.Remove("stock")
End If
```

Add the column as required keys.

B4X:

' Check whether required keys are provided
Dim RequiredKeys As List = Array As String("category_id", "product_code", "product_name", "opening_stock") ' "product_price" is optional
For Each requiredkey As String In RequiredKeys
    If data.ContainsKey(requiredkey) = False Then
        HRM.ResponseCode = 400
        HRM.ResponseError = $"Key '${requiredkey}' not found"$
        ReturnApiResponse
        Return
    End If
Next

Add new column to the Columns array of MiniORM.

B4X:

' Insert new row
DB.Reset
DB.Columns = Array("category_id", _
"product_code", _
"product_name", _
"product_price", _
"opening_stock", _
"created_date")
DB.Parameters = Array(data.Get("category_id"), _
data.Get("product_code"), _
data.Get("product_name"), _
data.GetDefault("product_price", 0), _
data.GetDefault("opening_stock", 0), _
data.GetDefault("created_date", WebApiUtils.CurrentDateTime))
DB.Save

Similarly in PutProductById sub. The completed code should look like the following.

B4X:

Private Sub PutProductById (Id As Int)
    ' #Desc = Update Product by id
    ' #Body = {<br>&nbsp;"cat_id": category_id,<br>&nbsp;"code": "product_code",<br>&nbsp;"name": "product_name",<br>&nbsp;"price": product_price,<br>&nbsp;"stock": 0<br>}
    ' #Elements = [":id"]
    Dim data As Map = WebApiUtils.RequestData(Request)
    If Not(data.IsInitialized) Then
        HRM.ResponseCode = 400
        HRM.ResponseError = "Invalid json object"
        ReturnApiResponse
        Return
    End If

    ' Deprecated: Make it compatible with Web API Client v1 (will be removed)
    If data.ContainsKey("cat_id") Then
        data.Put("category_id", data.Get("cat_id"))
        data.Remove("cat_id")
    End If
    If data.ContainsKey("code") Then
        data.Put("product_code", data.Get("code"))
        data.Remove("code")
    End If
    If data.ContainsKey("name") Then
        data.Put("product_name", data.Get("name"))
        data.Remove("name")
    End If
    If data.ContainsKey("price") Then
        data.Put("product_price", data.Get("price"))
        data.Remove("price")
    End If
    If data.ContainsKey("stock") Then
        data.Put("opening_stock", data.Get("stock"))
        data.Remove("stock")
    End If
  
    ' Check conflict product code
    DB.Table = "tbl_products"
    DB.Where = Array("product_code = ?", "id <> ?")
    DB.Parameters = Array As String(data.Get("product_code"), Id)
    DB.Query
    If DB.Found Then
        HRM.ResponseCode = 409
        HRM.ResponseError = "Product Code already exist"
        ReturnApiResponse
        DB.Close
        Return
    End If
  
    DB.Find(Id)
    If DB.Found = False Then
        HRM.ResponseCode = 404
        HRM.ResponseError = "Product not found"
        ReturnApiResponse
        DB.Close
        Return
    End If

    DB.Reset
    DB.Columns = Array("category_id", _
    "product_code", _
    "product_name", _
    "product_price", _
    "opening_stock", _
    "modified_date")
    DB.Parameters = Array(data.Get("category_id"), _
    data.Get("product_code"), _
    data.Get("product_name"), _
    data.GetDefault("product_price", 0), _
    data.GetDefault("opening_stock", 0), _
    data.GetDefault("modified_date", WebApiUtils.CurrentDateTime))
    DB.Id = Id
    DB.Save

    HRM.ResponseCode = 200
    HRM.ResponseMessage = "Product updated successfully"
    HRM.ResponseObject = DB.First
    ReturnApiResponse
    DB.Close
End Sub

Backend API is done. Now we go to front end.

Modify the Front End

Open the Files folder of the project with a text editor.
Open the index.html file.
We will focus on the modal form for "new" and "edit".
We can copy the code for price and replace the word "price" to "stock".
We also copy this code for "edit" but we need to change the id to "stock1" because the id must be unique.
Now we move on to the JavaScript part.
Because this template supports 3 types of JSON output as set in SimpleResponse, 3 different JS files are used.
If you are use the default, the server will load the search.simple.js file when loading index.html page.
The code for jQuery Validate is currently checking for product code and product name which are set as compulsory.
Category id is not checked because it is from drop down list, user must select an item.
Product Price also not checked because we let it as not compulsory. If user did not enter anything to input box then the value is default to 0.
Now we will add a code to validate user input to enter "opening stock".
This step is optional if you don't want to set it as compulsory or required field.
We will leave the #update part as not compulsory for "opening stock" field.
Next, we write the code for displaying the search result table.
Find the code for #btnsearch click.

We need to add a new column "Stock" after "Price" to the html table header.

B4X:

tbl_head = "<thead class=\"bg-light\"><th style=\"text-align: right; width: 60px\">#</th><th>Code</th><th>Category</th><th>Name</th><th style=\"text-align: right\">Price</th><th style=\"text-align: right\">Stock</th><th style=\"width: 90px\">Actions</th></thead>"

Then we add new variables for the column and value.
Add the following code to assign the value to table cell and temporary variable stock.

Add the temporary variable for stock to col_edit so it can pass the data to the modal form when edit button is clicked.

JavaScript:

col_edit = "<td><a href=\"#edit\" class=\"text-primary mx-2\" data-toggle=\"modal\"><i class=\"edit fa fa-pen\" data-toggle=\"tooltip\" data-id=\"" + id + "\" data-code=\"" + code + "\" data-category=\"" + catid + "\" data-name=\"" + name + "\" data-price=\"" + price + "\" data-stock=\"" + stock + "\" title=\"Edit\"></i></a> <a href=\"#delete\" class=\"text-danger mx-2\" data-toggle=\"modal\"><i class=\"delete fa fa-trash\" data-toggle=\"tooltip\" data-id=\"" + id + "\" data-code=\"" + code + "\" data-category=\"" + catid + "\" data-name=\"" + name + "\" title=\"Delete\"></i></a></td>"

Then add col_stock to tbl_body.

JavaScript:

tbl_body += "<tr>" + col_id + col_code + col_category + col_name + col_price + col_stock + col_edit + "</tr>"

We also need to update the click event for the inline edit button.
This is to load the value from result table into the edit modal form.
The search button code is done but there is a function to load the table when the page loads without clicking on button search.
There is a function using GET request to get all the rows by default using $.getJSON("/api/find") ajax call.
This part will be almost the same for the #btnSearch.

The updated script will look like the following.

JavaScript:

  $.getJSON("/api/find", function (response) {
    var tbl_head = ""
    var tbl_body = ""
    if (response.length) {
      tbl_head = "<thead class=\"bg-light\"><th style=\"text-align: right; width: 60px\">#</th><th>Code</th><th>Category</th><th>Name</th><th style=\"text-align: right\">Price</th><th style=\"text-align: right\">Stock</th><th style=\"width: 90px\">Actions</th></thead>"
      tbl_body += "<tbody>"
      $.each(response, function () {
        var col_id = ""
        var col_code = ""
        var col_category = ""
        var col_name = ""
        var col_price = ""
        var col_stock = ""
        var col_edit = ""
        var id
        var code
        var name
        var price
        var stock
        var catid
        $.each(this, function (key, value) {
          if (key == "id") {
            col_id = "<td class=\"align-middle\" style=\"text-align: right\">" + value + "</td>"
            id = value
          }
          else if (key == "product_code") {
            col_code = "<td class=\"align-middle\">" + value + "</td>"
            code = value
          }
          else if (key == "category_name") {
            col_category = "<td class=\"align-middle\">" + value + "</td>"
          }
          else if (key == "product_name") {
            col_name = "<td class=\"align-middle\">" + value + "</td>"
            name = value
          }
          else if (key == "product_price") {
            col_price = "<td class=\"align-middle\" style=\"text-align: right\">" + value + "</td>"
            price = value
          }
          else if (key == "opening_stock") {
            col_stock = "<td class=\"align-middle\" style=\"text-align: right\">" + value + "</td>"
            stock = value
          }           
          else if (key == "category_id") {
            catid = value
          }
        })
        col_edit = "<td><a href=\"#edit\" class=\"text-primary mx-2\" data-toggle=\"modal\"><i class=\"edit fa fa-pen\" data-toggle=\"tooltip\" data-id=\"" + id + "\" data-code=\"" + code + "\" data-category=\"" + catid + "\" data-name=\"" + name + "\" data-price=\"" + price + "\" data-stock=\"" + stock + "\" title=\"Edit\"></i></a> <a href=\"#delete\" class=\"text-danger mx-2\" data-toggle=\"modal\"><i class=\"delete fa fa-trash\" data-toggle=\"tooltip\" data-id=\"" + id + "\" data-code=\"" + code + "\" data-category=\"" + catid + "\" data-name=\"" + name + "\" title=\"Delete\"></i></a></td>"
        tbl_body += "<tr>" + col_id + col_code + col_category + col_name + col_price + col_stock + col_edit + "</tr>"
      })
      tbl_body += "</tbody>"
    }
    else {
      tbl_body = "<tr><td>No results</td></tr>"
    }
    $("#results table").html(tbl_head + tbl_body)
  })
})

That's all now. Delete the sqlite database file and run the project again in debug.

B4J Tutorial Web API Server (v3)

aeric

Expert

aeric

Expert

aeric

Expert

aeric

Expert

aeric

Expert

aeric

Expert

Using MiniORMUtils in Web API Server 3

aeric

Expert

Similar Threads