Use case for Bridge: Connect with remote databases using Bridge
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
Zylker 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 Orchestration with required fields for 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’s instance and the client’s application inside firewall.
Create a bridge and configure the bridge’s agent from your Window’s 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
Create two messages in bridge to hold basic details like module used, database query, bridge values, etc. We will later use these messages to communicate with the database. Click here to learn about Execution of Messages in bridge.
Message 1: Fetch Employee Details
Message 2: Update Remaining Leaves
Step 4: Create a Connection
Create a Connection using the following scopes:
- Orchestly.job.ALL
- Orchestly.message.CREATE
- Orchestly.message.UPDATE
Step 4: Create Custom Functions
The Function has to be executed 'on-create' stage of a card by configuring in Business Rule.
Code custom functions to-
Fetch data from MySql server
-
Update data inside MySql server
Here’s the sample code to retrieve employee details from SQL database:
- //Fetching selected employee details from local database
- params = Map();
- data = '{"task_name": "db_task","credential": {"name": "mssql1"},"task_details": {"database": "Employee","db_query":
- "SELECT * FROM Employee where Employee_id =' + "'" + emp_id + "'" + '","port": 1433,"query_timeout": 10000,"host":
- "localhost","sql_type": 3,"connection_timeout": 10000,"allow_multiple_columns": true}}';
- params.put("data",data);
- resp = invokeurl
- [
- url :" https://core.qntrl.com/blueprint/api/spacebugs/message/2403000000770088"
- 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_shorttext24",name);
- email = d.get(2);
- job_params.put("customfield_shorttext26",email);
- remaining_leaves = toNumber(d.get(3));
- job_params.put("customfield_integer10",remaining_leaves);
- r = invokeurl
- [
- url :" https://core.qntrl.com/blueprint/api/spacebugs/job/" + job_id
- type :POST
- parameters:job_params
- connection:"<connection_name>"
- ];
- //check for leave count. if leave count exceeds available leaves then 'Rejected' transition will be performed.
- leave_days = daysBetween(from_date,to_date);
- if(leave_days > remaining_leaves)
- {
- trans_params = Map();
- trans_params.put("transition_id","2403000000763175");
- trans_params.put("is_form","true");
- trans_params.put("content","Leave count exceeded.");
- r = invokeurl
- [
- url :" https://core.qntrl.com/blueprint/api/spacebugs/job/transition/" + job_id
- type :POST
- parameters:trans_params
- connection:"<connection_name>"
- ];
- }
- }
Here’s the sample code to update employee details to SQL database:
- leave_days = daysBetween(from_date,to_date);
- remaining_days = remaining_leaves.toLong() - leave_days +1;
- params = Map();
- data = '{"task_name": "db_dml_task","credential": {"name": "mssql1"},"task_details": {"database": "Employee","db_query": {"update":["UPDATE Employee set Remaining_leaves = ' + "'" + remaining_days + "'" + ' where Employee_id =' + "'" + emp_id + "'" + '"]},"port": 1433,"query_timeout": 10000,"host": "localhost","sql_type": 3,"connection_timeout": 10000}}';
- params.put("data",data);
- resp = invokeurl
- [
- //Upadte org name and message ID here
- url :" https://core.qntrl.com/blueprint/api/<org-name>/message/<message_ID>"
- type :POST
- parameters:params
- connection:"<connection_name>"
- ];
- info resp;
- job_params = Map();
- job_params.put("<parameter-for-custom-field-remaining leaves>",remaining_days);
- r = invokeurl
- [
- //Upadte org name here
- url :" https://core.qntrl.com/blueprint/api/<org-name>/job/" + job_id
- type :POST
- parameters:job_params
- connection:"<connection_name>"
- ];
- info r;
Step 5: Create a Card in the Leave Request Orchestration
Initiate leave request by creating a new card within the orchestration. Refer to the resources for guidance on creating new cards within the orchestration interface. Here is the sample card template to get the employee inputs for Leave Request devised by Zylker.
The Leave Request card contains fields like ‘Employee ID’, ‘Date from’, ‘Date to’, and ‘Reason for leave’. When the employee ID is filled, other employee details are fetched automatically from the database.
On filling ‘Date from’ and ‘Date to’ fields, the difference between them is calculated and validated with the ‘Remaining Leaves’ field. If the difference is lesser than or equal to the remaining leaves, the card is sent to the manager for approval. Else, the card is rejected automatically.
Once the manager approves the card, the Remaining Leaves field is updated and also, the second custom function gets triggered to update the same value in the client’s database.
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Business case 2: Use client scripts to auto-populate fields based on user input
While designing forms, we would sometimes require to display different sets of fields for different user responses. Such automatic field population can be handled using client scripts. Business Scenario Helen creates a sign-up form for her product ...Business case 1: Use client scripts to perform mathematical calculations and validations on data
While designing forms to replicate finance related documents like purchase order, invoice, or reimbursement bills, our workflows might require the calculation of total cost or sum of expenses incurred. To automate such mathematical calculations and ...DB Engine
DB Engine state enables a connection with the database hosted on a private or local network and executes the defined SQL query in the database. Prerequisite An active Bridge agent. Refer here to install and configure Bridge. Create the necessary ...Qntrl Marketplace - An Overview
Qntrl Marketplace is an online store with solutions for specific business requirements while working with Qntrl. This is the perfect platform to search for any Qntrl extensions or showcase the extension you have built, which extends the capabilities ...HIPAA Compliance in Qntrl
Introduction The Health Insurance Portability and Accountability Act (including the Privacy Rule, Security Rule, Breach notification Rule, and Health Information Technology for Economic and Clinical Health Act) ("HIPAA"), requires Covered Entities ...
New to Zoho LandingPage?
Zoho LandingPage Resources