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 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:
  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 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.  




      Create. Review. Publish.

      Write, edit, collaborate on, and publish documents to different content management platforms.

      Get Started Now


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







                              Quick LinksWorkflow AutomationData Collection
                              Web FormsEnterpriseOnline Data Collection Tool
                              Embeddable FormsBankingBegin Data Collection
                              Interactive FormsWorkplaceData Collection App
                              CRM FormsCustomer ServiceAccessible Forms
                              Digital FormsMarketingForms for Small Business
                              HTML FormsEducationForms for Enterprise
                              Contact FormsE-commerceForms for any business
                              Lead Generation FormsHealthcareForms for Startups
                              Wordpress FormsCustomer onboardingForms for Small Business
                              No Code FormsConstructionRSVP tool for holidays
                              Free FormsTravelFeatures for Order Forms
                              Prefill FormsNon-Profit
                              Intake FormsLegalMobile App
                              Form DesignerHRMobile Forms
                              Card FormsFoodOffline Forms
                              Assign FormsPhotographyMobile Forms Features
                              Translate FormsReal EstateKiosk in Mobile Forms
                              Electronic Forms
                              Drag & drop form builder

                              Notification Emails for FormsAlternativesSecurity & Compliance
                              Holiday FormsGoogle Forms alternative GDPR
                              Form to PDFJotform alternativeHIPAA Forms
                              Email FormsEncrypted Forms

                              Secure Forms

                              WCAG

                                      Create. Review. Publish.

                                      Write, edit, collaborate on, and publish documents to different content management platforms.

                                      Get Started Now






                                                        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


                                                              • Desk Community Learning Series


                                                              • Digest


                                                              • Functions


                                                              • Meetups


                                                              • Kbase


                                                              • Resources


                                                              • Glossary


                                                              • Desk Marketplace


                                                              • MVP Corner


                                                              • Word of the Day


                                                              • Ask the Experts


                                                                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 Demo

                                                                                                  Get a personalized demo or POC

                                                                                                  REGISTER NOW


                                                                                                    Design. Discuss. Deliver.

                                                                                                    Create visually engaging stories with Zoho Show.

                                                                                                    Get Started Now









                                                                                                                        • 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 ...
                                                                                                                          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