App Connect

MySQL is an open-source relational database management system that is based on structured query language (SQL). MySQL is a fast, reliable, scalable, and easy-to-use database system with querying and connectivity capabilities that offers robust data structure and platform integration.

Building a flow in IBM App Connect with MySQL

Use App Connect to build flows that integrate with MySQL and other applications. The connector is displayed as MySQL on the App Connect User Interface (UI). 

To allow App Connect to connect to your MySQL account, you need to fill in the connection fields that you see in the App Connect Designer Catalog page or flow editor.

Supported authorization type: BASIC OAUTH

For detailed information about different authorization types, how to generate connection field values, and how to use the Templates gallery, see How to use IBM App Connect with MySQL on the IBM Documentation page.

Supported objects in MySQL

The following are the MySQL objects that can be run in App Connect.

* The MySQL custom objects can be viewed by expanding the Show more option from the object list once you have connected App Connect to your MySQL account.

Scenario: Create a new row in a Google Sheets worksheet for each new opportunity retrieved from the MySQL record

Consider the following scenario, where you use App Connect to create a new row in a Google Sheets worksheet for each new opportunity retrieved from the MySQL record.

In this example, opportunity details are obtained by executing a custom SQL query, retrieving information about the opportunities from a specific MySQL database table. The details for each opportunity retrieved from MySQL are added to a worksheet in Google Sheets.

In this flow:

1. You run a scheduler-based flow at 15-minute intervals.
2. A Set variable node is added to define the previous runtime value 
   1. You can add a date for the previousruntime set variable in ISO 8601 UTC format, YYYY-MM-DDTHH:MM:SS.SSSZ. For example, the set variable property is set to ‘2023-12-03 08:00:00:00’.
   2. Optionally, you can map the previous event time date from the scheduler. For example, choose ‘Previous event time’. When the flow runs periodically, the previous event time will be consumed as the set variable value.
3. Execute a custom SQL query to retrieve opportunity details based on the opportunity close date. For example, the following is a snippet of the SQL query:

   SELECT * FROM demo.usecase1 where CloseDate> {{$Setvariable.variable.previousruntime}}. In this example, opportunity details are retrieved for close dates that occur after the value specified in the 'previousruntime = 2023-12-03 08:00:00.00’ variable property up to the current date

   
   

4. For each new opportunity record retrieved from MySQL, the information is parsed into JSON strings.
   1. A JSON Parser node from the App Connect toolbox is used to parse the Object properties and output them into JSON strings.
   2. Create a row in a Google Sheets worksheet for each new opportunity by adding the information through the Google Sheets ‘Create row’ operation based on the parsed data. For example:

  The following is an example of the parsed JSON properties that can be used to insert into the worksheet.

In summary, let's consider a scenario where, during a marketing campaign event, new Salesforce opportunities are continuously created in the MySQL database. To effectively track the progress of converting these opportunities into customers, the marketing team utilizes MySQL's Execute custom SQL operation. They periodically run a job to retrieve data created after a specified timeframe and append customer details to their working Google Sheets worksheet.

Useful resources

You can view this template and other useful templates in the Templates gallery of your App Connect Designer instance.

Get started with a free IBM App Connect Enterprise as a Service trial for 30 days to try out all our templates, visit https://ibm.biz/app-trial.

If you are running a containerized instance of IBM App Connect, use the following URL to directly access the template:

• Create a new row in a Google Sheets worksheet for each new opportunity retrieved from the MySQL record: https://<your-instance-id>/templates/Create%20a%20new%20row%20in%20a%20Google%20Sheets%20worksheet%20for%20each%20new%20opportunity%20retrieved%20from%20the%20MySQL%20record

            Note: Replace <your-instance-id> in the URL with your custom instance ID.

You must enable the Designer AI features in your containerized environment to access the App Connect templates. For more information, see The preloaded IBM App Connect templates.

You also have the option to import the .yaml file for this template directly into your App Connect Designer instance. These templates are available in a public GitHub repository.

#MySQL#googlesheets