Salesforce to MySQL Database Using Batch Processing

This application illustrates how to use batch processing to synchronize Salesforce information with a database.

About Batch Processing

Batch Processing allows you to split a payload into individual elements and process each element separately. This functionality is particularly useful when you are working with streaming input or when you are engineering "near real-time" data integration between SaaS applications.

Database Connector

The Database Connector provides a standardized interface for accessing any relational database via JDBC. This connector allows you to run diverse SQL operations on a database, including Select, Insert, Update, and Delete. This connector also allows you to run stored procedures.

Prerequisites

This document assumes that you are familiar with Mule Runtime and Anypoint Studio. Further, this example assumes you are familiar with XML coding and that you have a basic understanding of Mule flows and SOAP as a Web service paradigm and the practice of WSDL-first Web service development.

This document describes the details of the example within the context of Anypoint Studio, and includes configuration details for both the visual and XML editors.

You need to have a MySQL database installed to use this example. See MySQL.com for the free
community version.

Example Use Case

This application queries a Salesforce account for new or updated contacts at a regular interval, then processes the returned payload one record at a time. It checks to see if a contact currently exists in the database, then updates an existing contact or creates a new contact accordingly. After the process is completed for an entire batch, a success message is logged.

Although you could meet this use case without using batch processing, treating the entire list of contacts returned by Salesforce as a whole, batch processing makes this process more reliable because any errors that occur in a single record will not propagate beyond the level of that record.

Set Up and Run the Example

Complete the following procedure to create and then run this example in your own instance of Anypoint Studio. You can create template applications straight out of the box in Anypoint Studio and tweak the configurations of this use case-based template to create your own customized applications in Mule.

Skip ahead to the next section if you prefer simply to examine this example via code snippets.

1. Open the Salesforce to MySQL Database using Batch Processing project in Anypoint Studio from Anypoint Exchange.
2. Set up credentials for accessing Salesforce:
   1. Log in to your Salesforce account.
   2. From your account menu (the menu is labeled with your account name), select Setup.
   3. Under the Personal Setup heading in the left navigation bar, click to expand the My Personal Information folder.
   4. Click Reset My Security Token. Salesforce resets the token and emails you the new one.
   5. Access the email that Salesforce sent and copy the new token onto your local clipboard.
3. In Anypoint Studio, click the Global Elements tab.
4. Double-click the Salesforce_Sfdc_config global element to open its Global Element Properties dialog.
5. In the Security Token field, paste the new Salesforce token you copied from the email.
6. Change the contents of the Username, Password, and Authorization URL fields to your account-specific values, then click OK to save your changes.
7. Create a database and set up credentials for accessing it:
   1. Create a new MySQL Database. If you do not have a MySQL database available for your use, you can install MySQL on your local computer. Visit dev.MySQL.com to download and install a free version. It is a good idea to also install the MySQL workbench. Also, configure a MySQL username and password for use with this project.
   2. Configure the database by running these SQL statements:

      USE company;
      CREATE TABLE contact (email varchar(255) NOT NULL,first_name varchar(255) NOT NULL,last_name varchar(255) NOT NULL,last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (email));
      INSERT INTO contacts VALUES ("leonardmule@mulesoft.com", "Leonard", "Mule")
      

      There are two different methods that you can use to run them:

      1. In MySQL Workbench, create the schema company, and then run the SQL statements from within the MySQL Workbench SQL Editor.
      2. In the MySQL command-line tool, follow these steps:
         1. Execute this command: CREATE DATABASE company;. This creates the database schema.
         2. Save the SQL statements in a text file.
         3. Run the SQL with this command: mysql company <path-and-name-of-text-file>

         In either case, you might need to create a user for that has both read and write permissions on the schema. See MySQL Documentation for more information on using the MySQL Workbench or the command-line tool to create users and grant them permissions.

8. In Anypoint Studio, click the Global Elements tab.
9. Double-click the Database Config global element to open its Global Element Properties dialog.
10. Change the contents of the Host, Port, User, Password, and Database fields to your account-specific values, then click OK to save your changes.
11. In the Package Explorer, right-click the /salesforce-to-MySQL-DB-using-Batch-Processing project name, and then select Run As > Mule Application. Studio runs the application on the embedded server.

How It Works

Unlike typical Mule projects that are organized into Flows, this project runs a Batch Process. The process is divided into three stages:

Input

Every 60 seconds, the Scheduler component sends a new request to the Salesforce connector. The Salesforce connector is set to perform the query below, where timestamp represents current time - 24 hours so that only Contacts modified in the last 24 hours are returned:

SELECT Email,first_name,LastModifiedDate,last_name FROM Contact WHERE
LastModifiedDate > :timestamp

The response returned by the Salesforce connector is a list of contacts.

Process Records

This stage of the batch job processes the records one at a time. Each record represents a single contact. If a record fails, the entire task does not fail with it; instead, Mule skips the record, moving on to process the next one.

Batch Step queryExistingContactInDbStep

In this step, the Transform component maps the fields so that they match those in the database. The Database connector issues the following query to the database:

SELECT first_name,last_name,email FROM contact WHERE email=#[payload.Email]

The response of the Database connector is stored into variable recordExists, so the payload is not overridden by the response from the database query. Thus, all of the information that originated from Salesforce is retained and can be passed on to the next step.

Batch Step insertContactsStep

Mule executes the second batch step only if the first step is successful. If a record is not found in the DB, then vars.recordExists == null. The contact must be added as a new contact. The following INSERT query is carried out in the database:

INSERT INTO contact (first_name, last_name, email) VALUES (:firstName,
:lastName, :email)

Batch Step updateContactsStep

In case the first step is successful and the record exists in the DB, then vars.recordExists != null and the contact is updated. The following UPDATE query is carried out in the database:

UPDATE contact SET first_name = :firstName, last_name = :lastName WHERE email = :email

On Complete

In this stage, a logger announces the completion of the task. This stage of the batch process runs once after all of the records have been processed, whether the processing of each record was successful, failed, or was skipped.

See Also

• Learn more about the Database Connector.
• Understand Batch Processing.
• Learn more about the DataWeave.
• Learn about Anypoint Connectors.