Database based config property in WebMethods Part-1

The aim of this article is to create a database-based config property that can be maintained from the front end. We can also work with file-based config property, which we will see in the upcoming article. We will split this article into three parts.

Part-1, we’ll create a package, a database table, a cache manager, a cache, global variables, and JDBC adapters.
Part-2, we’ll design the user interface for managing the Config table.
Part-3 involves writing flow services, which can be called from the front end or a DSP page.

Prerequisite

Basic knowledge of the DSP page(Click here to check out our article on the DSP page).
Basic knowledge of HTML CSS and Javascript.
Basic knowledge of flow services.
Basic knowledge of SQL.
Basic knowledge of JDBC Adapter usage (Click here to check out our article on JDBC Adapter).

Code Area

Package Creation

Create a package with any name. Ex- HGUtil.
Create the below folder structure in your package, note:- the top-level folder you can name according to your package name.

Table creation

Run below SQL query below in your Oracle database to create the config utility table.

CREATE TABLE HARMONIGATE_CONFIG_PROPERTY(
PROJECT_NAME VARCHAR2(50),
CONFIG_KEY VARCHAR2(100),
CONFIG_VALUE CLOB,
CONSTRAINT CONFIG_KEY_PK PRIMARY KEY (CONFIG_KEY)
);

We have kept the config key field as the primary key because it should be always unique to retrieve its respective config value.
We have kept the config value filed as clob because we don’t know what will be the maximum length of a particular value in the future.

Configure Cache

Since we’re storing our configuration in the database, we’ll be using the keys to fetch their corresponding values. However, making repeated calls to the database for each key can significantly reduce performance. To optimize this, we’ll pull the data from the database once and store it in the cache for efficient retrieval.

Create Cache Manager:

Login to your integration server admin page and click on Cache under the Settings tab.
Click on ‘Add Cache Manager’ and give a name to the Cahe manager(Ex – HARMONIGATE_CONFIG_PROPERTY), and click the save changes button.
Note: If you have a terracotta server running and you want to make this Cache shared across the IS then you can give the terracotta URL and create the cache manager.
Click on the cache manager that you have created now and click on Add cache.
Fill in the below details and create the cache.
For more information on each field and how they work click here.

Create Global Variable:

We will create the global variables to store the Cache manager name and the cache name so that in the future if we need to update the cache manager or cache name, we don’t need to update the code instead we can update it in the integration server page itself.

Login to your IS page and click on the ‘Global Variables’ option under the ‘Settings’ tab.
Click on the ‘Add Global Variable’ option.
Fill Key as ‘cacheManagerName’ and value with your exact Cache Manager name.
In the same way, create one more global variable to hold your Cache name with kay ‘cacheName’ and value with your exact Cache name that we created earlier.

Create JDBC Adapter :

We’ll develop four adapter services to facilitate essential database operations including insertion, selection, updating, and deletion.

Insert Adapter :

Open the package that we have created for this project in the designer.
Right-click on the adapter folder create a JDBC adapter using the batch insert template and give it a suitable name.
Select your config table from the current schema under the ‘Table’ tab in the JDBC Adapter.
Add all the fields in the ‘INSERT’ tab of the adapter, don’t forget to make the ‘CONFIG_VALUE’ input type to ‘java.lang.String’ type.
In the ‘Batch Result’ tab create a variable with the name ‘count’ so that we will get to know how many rows have been inserted into the table.

Select Adapter :

Right-click on the adapter folder create a JDBC adapter using the Select SQL template and give it a suitable name.
Select your config table from the current schema under the ‘Table’ tab in the JDBC Adapter.
In the select tab add all the fields of the table.
We will not put where clause here as we will extract everything.

Update Adapter :

Create an update adapter using UpdateSQL template and give it a suitable name.
Select the table name as you did in the previous step.
In the ‘UPDATE’ tab add the Config value field to update(Do not forget to change the input type to java.lang.String format) and in the ‘WHERE’ tab add the config key field using which we will update the config value.

Delete Adapter :

Create an update adapter using the DeleteSQL template and give it a suitable name.
Select the table name under the current schema.
In the ‘WHERE’ tab add the config key field using which we will delete the property entry.

We have done all the necessary setup for the first part of this project, hope you have enjoyed this article!!

Post Views: 1,585