We're currently adding a Mule Studio based version of this tutorial which will be available soon!

Integrating Salesforce.com with an On-Premise Database

This tutorial demonstrates how to configure and deploy a iON application that reads data from Salesforce.com and writes it to an on-premise MySQL database. This tutorial will walk you through the following steps:

Create the Test Database
Download the Example Project
Update the Mule Configuration
Build the Example Application
Deploy the Application to iON
Use the Application

Prerequisites

If you have not already reviewed the documentation and downloaded the Secure Data Gateway, please do so before getting started with this tutorial.
This example requires an existing Salesforce.com account. To create a new developer account, see Salesforce.com Developer Account Setup. After setting up a Salesforce.com account, a security token will be emailed to you. Be sure to save this, as you will need it later in this tutorial.

Create the Test Database

In order to run the ion-sfdc-test application and integrate Salesforce.com data with a private MySQL database, the expected database and table needs to be present. Run the following SQL commands on the database server referenced by the privateHost configuration element in the SDG conf/mappings.xml file.

CREATE DATABASE `ion_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `ion_sfdc_test_table` (
  `id` varchar(30) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Queries from iON applications will be executed as the user specified in the Mule configuration. If necessary, ensure that the server running the SDG Client has the authority to connect to and issue queries against the database as that user.

Download the Example Project

Download the ion-sfdc-test-project-1.0.zip package and expand it.

Update the Mule Configuration

The ion-sfdc-test/src/main/app/mule-config.xm is shown below:

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:spring="http://www.springframework.org/schema/beans"
      xmlns:http="http://www.mulesoft.org/schema/mule/http"
      xmlns:jdbc="http://www.mulesoft.org/schema/mule/ee/jdbc"
      xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
      xmlns:mule-xml="http://www.mulesoft.org/schema/mule/xml"
      xmlns:client="http://www.mulesoft.org/schema/mule/client"
      xmlns:salesforce="http://www.mulesoft.org/schema/mule/salesforce"
      xmlns:context="http://www.springframework.org/schema/context"

    xsi:schemaLocation="
        http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.1/mule.xsd
        http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/3.1/mule-http.xsd
        http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/3.1/mule-jdbc.xsd
        http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.1/mule-vm.xsd
        http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/3.1/mule-xml.xsd
        http://www.mulesoft.org/schema/mule/client http://www.mulesoft.org/schema/mule/client/3.1/mule-client.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.mulesoft.org/schema/mule/salesforce http://www.mulesoft.org/schema/mule/salesforce/3.1/mule-salesforce.xsd
        http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/3.1/mule-jdbc-ee.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <context:property-placeholder location="mule.properties"/>

    <!-- The ${sdg.port}, ${db.username} and ${db.password} environment variables 
    below are not provided by iON. These values need to be specified in an
    accompanying mule.properties file or configured at deploy time in the
    iON Console. See the next section regarding the mule.properties file
    packaged with this example. -->

    <spring:bean id="dataSource"
        class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <spring:property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <spring:property name="url" value="jdbc:mysql://localhost:${sdg.port}/test"/>
        <spring:property name="username" value="${db.username}"/>
        <spring:property name="password" value="${db.password}"/>
    </spring:bean>

    <jdbc:connector name="jdbc" dataSource-ref="dataSource" queryTimeout="10"
                transactionPerMessage="true">
                <jdbc:query key="saveaccounts"
                        value="INSERT INTO ion_test.ion_sfdc_test_table(Id,Name) VALUES (#[map-payload:Id],#[map-payload:Name])" />
        </jdbc:connector>

        <salesforce:config name="sfconfig" username="${sf.username}" password="${sf.password}" securityToken="${sf.security.token}"/>

    <flow name="sfdc2db">
        <http:inbound-endpoint host="localhost" port="${http.port}" path="listaccount" method="GET" contentType="text/html" keep-alive="false" />
        <response>
            <mule-xml:object-to-xml-transformer acceptMuleMessage="false" />
            <mule-xml:xslt-transformer name="ListToHtml" xsl-file="SFAccountList.xsl" maxIdleTransformers="2" maxActiveTransformers="5" />
        </response>
        <salesforce:query query="SELECT Id, Name FROM Account" batchsize="100" config-ref="sfconfig"/>
        <logger message="payload #[groovy:payload]" level="ERROR"/>
        <jdbc:outbound-endpoint connector-ref="jdbc"
                        queryKey="saveaccounts" exchange-pattern="one-way" />
    </flow>
</mule>

Update mule.properties

Notice the ${db.username}, ${db.password} and ${sdg.port} variables in the mule-config.xml file.

The ${db.username} and ${db.password} parameters are credentials for the private MySQL database. The ${sdg.port} setting should match the remotePort value in the SDG conf/mappings.xml file. The ion-sfdc-test-app application will use this local port to communicate with the SDG and private database.

The ${sf.username} ${sf.password} and ${sf.security.token} variables need to be set for connecting to Salesforce.com. The username is the email address used to set up the account and the security token should be the one emailed to that address.

Update these parameter values in ion-sfdc-test-app/src/main/resources/mule.properties.

Build the Example Application

The example application can be built using the Maven pom.xml included in the download.

To build the application, run the following command at the root level of the ion-sfdc-test-app project:

$ cd ion-sfdc-test-app
$ mvn package

When the build completes, the application is packaged for deployment and located at ion-sfdc-test-app/target/ion-sfdc-test-app-1.0.zip.

Deploy the Application to iON

Follow the steps here in order to deploy your application ion-sfdc-test-app-1.0.zip to iON. Click on Logs on the left-hand side to verify that your application has been successfully deployed. You should see the following text:

INFO	05-20-11 14:02:59  [DeploymentService]
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Started app 'ion-sfdc-test-app' +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INFO	05-20-11 14:02:59	[DefaultMuleContext]
**********************************************************************
* Application: ion-sfdc-test-app *
* OS encoding: ANSI_X3.4-1968, Mule encoding: UTF-8 *
* *
* Agents Running: *
* JMX Agent *
**********************************************************************
INFO	05-20-11 14:02:59  [ApplicationServiceImpl] Successfully deployed [ion-sfdc-test-app]
SYSTEM	05-20-11 14:03:05  Worker deployed!

Use the Application

Once your application has been deployed, you can use it to transfer data from Salesforce.com to a private MySQL database with the application URL: http://YOURAPPNAME.muleion.com/listaccount.

The application port variable ${http.port} is specified by iON and is load-balanced for you, so there is no need to specify port information in your application URL.

The first time the application is used, it will execute more slowly than subsequent invocations. This is because the Salesforce and JDBC connectors must initialize and establish connections.

Note: Make sure that the Secure Data Gateway is running on the private network before using the application URL. If the SDG is note properly connected to iON, you will see database connection errors in the iON logs.

Behind the Scenes

This configuration instructs Mule to listen for HTTP requests on the http://localhost:$\{http.port}/listaccount inbound endpoint.

The ${http.port} variable is dynamically provided by iON and should be left parameterized as in this example. The server should also be left to localhost, as the iON load balancer will handle directing traffic to the application.

When the HTTP inbound endpoint is invoked, a query is issued to Salesforce.com for the Id and Name of an Account.

An outbound JDBC connector is configured to insert this data into a on_test.ion_sfdc_test_table MySQL table. The JDBC datasource url spring property references the private database over the ${sdg.port}, which is securely connected to the SDG Client running on the enterprise network. The SDG Client, in turn, brokers the connection between iON and the private MySQL database.

Note: be sure this ${sdg.port} matches the remotePort mapping in the SDG conf/config.xml file.

For more information on application deployment, see the Getting Started guide.

Primary links

Mule iON - Contents