Use case for Bridge: Connect with remote databases using Bridge | Online Help | Qntrl

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 Board 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:
  1. Orchestly.job.ALL
  2. Orchestly.message.CREATE
  3. 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
  1. Fetch data from MySql server
  2. Update data inside MySql server


Here’s the sample code to retrieve employee details from SQL database:
  1. //Fetching selected employee details from local database
  2. params = Map();
  3. data = '{"task_name": "db_task","credential": {"name": "mssql1"},"task_details": {"database": "Employee","db_query": 
  4. "SELECT * FROM Employee where Employee_id =' + "'" + emp_id + "'" + '","port": 1433,"query_timeout": 10000,"host": 
  5. "localhost","sql_type": 3,"connection_timeout": 10000,"allow_multiple_columns": true}}';
  6. params.put("data",data);
  7. resp = invokeurl
  8. [
  9.  url :" https://core.qntrl.com/blueprint/api/spacebugs/message/2403000000770088"
  10.  type :POST
  11.  parameters:params
  12.  connection:"<connection_name>"
  13. ];
  14. info resp;
  15. response = resp.get("response_to").getJSON("results");
  16. //Updating employee details in the current job
  17. if(response != null)
  18. {
  19.  d = response.get(0);
  20.  job_params = Map();
  21.  name = d.get(1);
  22.  job_params.put("customfield_shorttext24",name);
  23.  email = d.get(2);
  24.  job_params.put("customfield_shorttext26",email);
  25.  remaining_leaves = toNumber(d.get(3));
  26.  job_params.put("customfield_integer10",remaining_leaves);
  27.  r = invokeurl
  28.  [
  29.   url :" https://core.qntrl.com/blueprint/api/spacebugs/job/" + job_id
  30.   type :POST
  31.   parameters:job_params
  32.   connection:"<connection_name>"
  33.  ];
  34.  //check for leave count. if leave count exceeds available leaves then 'Rejected' transition will be performed.
  35.  leave_days = daysBetween(from_date,to_date);
  36.  if(leave_days > remaining_leaves)
  37.  {
  38.   trans_params = Map();
  39.   trans_params.put("transition_id","2403000000763175");
  40.   trans_params.put("is_form","true");
  41.   trans_params.put("content","Leave count exceeded.");
  42.   r = invokeurl
  43.   [
  44.    url :" https://core.qntrl.com/blueprint/api/spacebugs/job/transition/" + job_id
  45.    type :POST
  46.    parameters:trans_params
  47.    connection:"<connection_name>"
  48.   ];
  49.  }
  50. }

Here’s the sample code to update employee details to SQL database:

  1. leave_days = daysBetween(from_date,to_date);
  2. remaining_days = remaining_leaves.toLong() - leave_days +1;
  3. params = Map();
  4. 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}}';
  5. params.put("data",data);
  6. resp = invokeurl
  7. [
  8. //Upadte org name and message ID here
  9. url :" https://core.qntrl.com/blueprint/api/<org-name>/message/<message_ID>"
  10. type :POST
  11. parameters:params
  12. connection:"<connection_name>"
  13. ];
  14. info resp;
  15. job_params = Map();
  16. job_params.put("<parameter-for-custom-field-remaining leaves>",remaining_days);
  17. r = invokeurl
  18. [
  19. //Upadte org name here
  20. url :" https://core.qntrl.com/blueprint/api/<org-name>/job/" + job_id
  21. type :POST
  22. parameters:job_params
  23. connection:"<connection_name>"
  24. ];
  25. info r;

Step 5: Create a Card in the Leave Request Orchestration

Initiate leave request by creating a new card within the Board. Refer to the resources for guidance on creating new cards within the Board 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.  




    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 CRM Training
      Redefine the way you work
      with Zoho Workplace

        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 CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Zoho Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION








                                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.




                                    Manage your brands on social media

                                      Zoho Desk Resources

                                      • Desk Community Learning Series


                                      • Digest


                                      • Functions


                                      • Meetups


                                      • Kbase


                                      • Resources


                                      • Glossary


                                      • Desk Marketplace


                                      • MVP Corner


                                      • Word of the Day


                                        Zoho Marketing Automation

                                          Zoho Sheet Resources

                                           

                                              Zoho Forms Resources


                                                Secure your business
                                                communication with Zoho Mail


                                                Mail on the move with
                                                Zoho Mail mobile application

                                                  Stay on top of your schedule
                                                  at all times


                                                  Carry your calendar with you
                                                  Anytime, anywhere




                                                        Zoho Sign Resources

                                                          Sign, Paperless!

                                                          Sign and send business documents on the go!

                                                          Get Started Now




                                                                  Zoho TeamInbox Resources



                                                                          Zoho DataPrep Resources



                                                                            Zoho DataPrep Demo

                                                                            Get a personalized demo or POC

                                                                            REGISTER NOW


                                                                              Design. Discuss. Deliver.

                                                                              Create visually engaging stories with Zoho Show.

                                                                              Get Started Now







                                                                                            You are currently viewing the help articles of Sprints 1.0. If you are a user of 2.0, please refer here.

                                                                                            You are currently viewing the help articles of Sprints 2.0. If you are a user of 1.0, please refer here.



                                                                                                  • Related Articles

                                                                                                  • Install Bridge

                                                                                                    Follow the below step-by-step procedures to configure and use Bridge in Qntrl. Step 1: Download the Bridge agent Only one Bridge agent can be installed per machine. To download a Bridge agent in Qntrl: Navigate to >> Advanced >> Bridge >> select ...
                                                                                                  • Overview of Bridge

                                                                                                    What is a Bridge? Bridge is an installable, lightweight independent agent that can be deployed on the customer’s local network. It is compatible both on Windows and Linux machines with 32 and 64-bit OS. Its role is to facilitate communication between ...
                                                                                                  • DB Engine

                                                                                                    DB Engine is used to connect with the database in the client's machine or client's network and perform READ/INSERT/UPDATE operations in the database. The DB Engine configured in Bridge can be utilized in the following two distinct modules within ...
                                                                                                  • Bridge agent settings

                                                                                                    View Bridge agent configuration Once the Bridge agent is set up, you will be able to view and modify the allowed Configuration when required. In Qntrl, navigate to (settings) >> Advanced >> Bridge >> select Bridge. Hover your cursor over the Bridge ...
                                                                                                  • Connections in Qntrl

                                                                                                    Qntrl allows users to integrate with internal Zoho applications and other third-party applications using Connections. Connections can be established in Qntrl by configuring the  custom functions. Please create relevant custom functions before ...
                                                                                                    Wherever you are is as good as
                                                                                                    your workplace

                                                                                                      Resources

                                                                                                      Videos

                                                                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                                      eBooks

                                                                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                                      Webinars

                                                                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                                      CRM Tips

                                                                                                      Make the most of Zoho CRM with these useful tips.



                                                                                                        Zoho Show Resources