Most organizations secure their remote databases using firewalls. If you are looking for a way to navigate these firewalls to access remote databases, Bridge can be of help.

Let us try to solve a real-time business case where Bridge is used to access a MySQL database inside a private network.

Business Scenario   

Swerio Corp maintains all their data in a MySQL database inside their secure private network. They use Qntrl for automating different workflows. In one such workflow—Leave Approval, they are looking to retrieve the employees' remaining leave balance for the current year and display it in Qntrl. If the leave balance is sufficient, the card will be sent to the employee’s manager for approval. Else, if the leave balance is nil or less than the requested number of leaves, the card must be automatically rejected.

Also, once the leave is approved in Qntrl, the remaining leave balance must be updated in the client’s MySql database as well, thus maintaining uniform data throughout.

Solution   

To address this business case effectively, you can create a Board with required fields for the leave request form and utilize a combination of Bridge and Custom Function functionalities.

Implementation 

Follow the steps below to implement the solution:

Step 1: Configure Bridge   

Bridge is an installable Java application that runs on the customer’s local network to facilitate communication between a Qntrl instance and the client’s application inside the firewall.

Create a bridge and configure the Bridge’s agent from your Windows or Linux machine. Get to know the step-by-step process to run a bridge from our help documentation.

Step 2: Create Messages in Bridge   

Message 1: Retrieve Employee Details

Step 3: Create a Connection

Create a Connection. While creating a connection, use the following scopes:

1. Orchestly.job.ALL

2. Orchestly.message.CREATE

3. Orchestly.message.UPDATE
   

//Fetching selected employee details from local database
params = Map();
data = '{"task_name": "db_task","credential": {"name": "mysql"},"task_details": {"database": "DEMO","db_query": "SELECT * FROM Employees where EMP_ID =' + "'" + emp_id + "'" + '","port": 3306,"query_timeout": 10000,"host": "localhost","sql_type": 1,"connection_timeout": 10000,"allow_multiple_columns": true}}';
params.put("data",data);
resp = invokeurl
[
      url :"https://core.qntrl.in/blueprint/api/manikin001/message/6124000000050437"
      type :POST
      parameters:params
      connection:"<CONNECTION NAME>"
];
info resp;
response = resp.get("response_to").getJSON("results");
//Updating employee details in the current job
if(response != null)
{
   d = response.get(0);
   job_params = Map();
   name = d.get(1);
   job_params.put("customfield_shorttext1",name);
   email = d.get(2);
   job_params.put("customfield_shorttext2",email);
   remaining_leaves = toNumber(d.get(3));
   job_params.put("customfield_integer1",remaining_leaves);
   r = invokeurl
   [
 	url :" https://core.qntrl.in/blueprint/api/manikin001/job/" + job_id
 	type :POST
 	parameters:job_params
 	connection:"<CONNECTION NAME>"
   ];
}

remaining_days = remaining_leaves.toLong();
params = Map();
data = '{"task_name": "db_dml_task","credential": {"name": "mysql"},"task_details": {"database": "DEMO","db_query": {"update":["UPDATE Employees set REMAINING_LEAVES = ' + "'" + remaining_days + "'" + ' where EMP_ID =' + "'" + emp_id + "'" + '"]},"port": 3306,"query_timeout": 10000,"host": "localhost","sql_type": 1,"connection_timeout": 10000}}';
params.put("data",data);
resp = invokeurl
[
      url :"https://core.qntrl.in/blueprint/api/manikin001/message/6124000000050557"
      type :POST
      parameters:params
      connection:"<CONNECTION NAME>"
];
info resp;