Cloud to RDBMS using WSO2 Enterprise Service Bus
- Malaka Silva
- Senior Technical Lead - WSO2
Applies to
| WSO2 ESB | 4.8.0 |
Table of contents
Introduction
The rising trend of Software-as-a-Service (SaaS), and other cloud services demand greater integration between enterprises and cloud platforms. Moderns enterprise use cases often require cloud to enterprise/on-premise integration, such as Salesforce and SAP, or cloud to cloud integration, such as Salesforce and Google Docs. An Enterprise Service Bus (ESB) is widely used in such integration scenarios and the latest WSO2 ESB 4.8 comes with inbuilt support for such integration by leveraging ESB connectors. WSO2 ESB Connectors provide ready-made and convenient tools for reaching publicly available Web APIs like SalesForce and Google spreadsheets. WSO2 connectors also eliminate the need to learn complex APIs, thereby enabling enterprises to focus on their core businesses. However, there still are requirements to store cloud data on premise for off-line processing and batch processing required by some systems.
Use case
ABC Jobs is a company that helps candidates who are seeking jobs and bringing top talent to employers. ABC Jobs is using Salesforce for their internal organization needs. They have a requirement to give candidates the ability to register them self and apply for various events and jobs. Basically, they will need a web portal for this. As a solution ABC Jobs can create their web portal on salesforce. However, the licensing for portals are quite high and Salesforce itself has some limits when uploading files (this will become an issue when candidates upload their CV at the time of registering). But, ABC Jobs needs all the data to be available on Salesforce for processing.
Since all the data is stored on Salesforce, the portal can access Salesforce using API when there is a data write or read. However, not only this will slow the portal's performance, Salesforce itself has a limitation on the number of API calls for 24 hours. As a solution, we are going to sync the Salesforce data to RDBMS periodically using WSO2 ESB and use the RDBMS for the web portal. This is a real-world use case that most organizations face on a daily basis. In this article, we are going to show how easy it is to get data from Salesforce to RDBMS using the Salesforce connector. This can be further developed to support updates based on timestamp and RDBMS to Salesforce transfer.
Implementing the scenario
Firstly download the WSO2 ESB 4.8.0 from download page.
Data Services Server feature is not shipped default with WSO2 ESB. Users need to manually install it. Follow the steps below to install it.
Start the ESB and type https://localhost:9443/carbon/ on your browser. Log in to the server using "admin" as username and password.
Then it will lead you to the WSO2 ESB management console.
Install data services server on ESB
Click on the "configure" tab on the left side as marked on the above picture. Then the menu list the pallet will change as below and click on the "Features" item as shown in the image.
It will lead you to the feature management console. To install DSS on ESB the user needs to have two features on ESB 1. Data Service Hosting 4.2.0 2. Data Source management 4.2.0 User can see the installed features by clicking the "Installed Features " tab. Data Source Management feature is already installed on ESB 4.8.0. There fore you do not need to install it from the repository.
Data Service Hosting feature is the only one we have to install Go back to the Available features tab and click on the "Add Repository" button.
Give a name to the repository. Select location as URL, and give the location of the carbon p2 repo as https://dist.wso2.org/p2/carbon/releases/turing.
Click on the "Add" button.
After successfully added the repository, click on the "Find Features" button as shown in the below image.
It will show you a list of available features to be installed. It contains the feature "Data Service Hosting 4.2.0".
Put a tick on the check box relevant to the feature and click the install button at the bottom of the list. Go through the feature installation wizard. After the installation, restart the server for newly installed features to activate. After restarting the server you will see the new features appear in the left side pallet under the main tab as shown in the below image.
Now you have successfully installed WSO2 DSS features on WSO2 ESB.
Refer the following link for more information
Since we are going to host both data service and proxy on the same server, we need to enable local transport for service inter communication.
The steps are explained n the following link.
Setup non blocking local transport on wso2 ESB
Enable the non blocking local transport and restart the ESB.
Setup MySQL Database
In order to do the scenario explained in this tutorial we need to have a mysql server installed locally or on premises.
Create the following table to store Salesforce contacts. This will be used by the data service.
CREATE TABLE `contact` (
`id` VARCHAR(18) NOT NULL ,
`name` VARCHAR(255) NULL ,
`Account` VARCHAR(255) NULL ,
`AssistantName` VARCHAR(255) NULL ,
`AssistantPhone` VARCHAR(45) NULL ,
`CreatedBy` VARCHAR(45) NULL ,
`Department` VARCHAR(255) NULL ,
`Description` VARCHAR(3000) NULL ,
`Email` VARCHAR(100) NULL ,
`HomePhone` VARCHAR(45) NULL ,
`LastModifiedBy` VARCHAR(45) NULL ,
`MobilePhone` VARCHAR(45) NULL ,
`Title` VARCHAR(10) NULL ,
PRIMARY KEY (`id`) );
[DB Script]
Copy the MySQL driver to ESB_HOME/repository/components/lib and restart the ESB.
Creating the data dervice
This can be done using the wizard provided by ESB console. Click 'Create' under Services -> Add - > Data Service.
Give the service name as 'mysql' and namespace as 'https://test.com/test'. Tick 'Enable Batch Requests'
Click next -> next and finish.
The above step will create a template for the dataservice. Go to the service created under proxy list.
Click 'Edit Data Service (XML Edit)'
Copy the following dataservice to insert contacts and save.
<data enableBatchRequests="true" enableBoxcarring="false" enableDTP="false" name="mysql" serviceNamespace="https://test.com/test">
<config id="mysql">
<property name="driverClassName">com.mysql.jdbc.Driver</property> <property name="url">jdbc:mysql://localhost:3306/test</property> <property name="username">root</property> <property name="password">root</property> </config> <query id="CreateContact" useConfig="mysql"> <sql>insert into contact (id,name,Account,AssistantName,AssistantPhone,CreatedBy,Department,Description,Email,HomePhone,LastModifiedBy,MobilePhone,Title) values (?,?,?,?,?,?,?,?,?,?,?,?,?)</sql> <param name="Id" sqlType="STRING" /> <param name="Name" sqlType="STRING" /> <param name="AccountName" sqlType="STRING" /> <param name="AssistantName" sqlType="STRING" /> <param name="AssistantPhone" sqlType="STRING" /> <param name="CreatedBy" sqlType="STRING" /> <param name="Department" sqlType="STRING" /> <param name="Desc" sqlType="STRING" /> <param name="Email" sqlType="STRING" /> <param name="HomePhone" sqlType="STRING" /> <param name="LastModifiedBy" sqlType="STRING" /> <param name="MobilePhone" sqlType="STRING" /> <param name="Title" sqlType="STRING" /> </query> <operation name="CreateContact"> <call-query href="CreateContact"> <with-param name="Id" query-param="Id" /> <with-param name="Name" query-param="Name" /> <with-param name="AccountName" query-param="AccountName" /> <with-param name="AssistantName" query-param="AssistantName" /> <with-param name="AssistantPhone" query-param="AssistantPhone" /> <with-param name="CreatedBy" query-param="CreatedBy" /> <with-param name="Department" query-param="Department" /> <with-param name="Desc" query-param="Desc" /> <with-param name="Email" query-param="Email" /> <with-param name="HomePhone" query-param="HomePhone" /> <with-param name="LastModifiedBy" query-param="LastModifiedBy" /> <with-param name="MobilePhone" query-param="MobilePhone" /> <with-param name="Title" query-param="Title" /> </call-query> </operation> </data>
Setup Salesforce connector
Download the Salesforce connector from the following location.
Upload the Salesforce connector to ESB as show in the following diagram.
After uploading is complete go to the list under connectors and enable the Salesforce connector as shown in the following diagram.
Adding the synapse configuration
Finally, add the following proxy and sequence that will query Salesforce data and send to data service. Configurations can easily be created using WSO2 Developer Studio as shown in the following diagrams.
[Proxy Service]
[Sequence used by proxy]
Following are the sources of above configuration.
<proxy xmlns="http://ws.apache.org/ns/synapse" name="query" transports="https http" startOnLoad="true" trace="disable">
<target>
<inSequence> <salesforce.init> <username>[email protected]</username> <password>xxxxxxx</password> <loginUrl>https://login.salesforce.com/services/Soap/u/27.0</loginUrl> </salesforce.init> <salesforce.queryAll> <batchSize>200</batchSize> <queryString>select Id,name,Account.name,AssistantName,AssistantPhone,Birthdate,CreatedBy.name,Department,Description,Email,HomePhone,LastModifiedBy.Name,MobilePhone,Title from Contact</queryString> </salesforce.queryAll> <sequence key="generatebatch"/> <iterate xmlns:sfdc="https://wso2.org/salesforce/adaptor" continueParent="true" expression="//sfdc:iterator"> <target> <sequence> <salesforce.queryMore> <batchSize>200</batchSize> </salesforce.queryMore> <sequence key="generatebatch"/> </sequence> </target> </iterate> <respond/> </inSequence> <outSequence/> </target> </proxy>
<sequence xmlns="http://ws.apache.org/ns/synapse" name="generatebatch">
<enrich>
<source type="body" clone="true"/> <target type="property" property="INIT_MSG_PAYLOAD"/> </enrich> <payloadFactory media-type="xml"> <format> <p:CreateContact_batch_req xmlns:p="https://test.com/test"/> </format> <args/> </payloadFactory> <property xmlns:ns="https://org.apache.synapse/xsd" xmlns:p="https://test.com/test" name="DB_BATCH_MSG_PAYLOAD" expression="//p:CreateContact_batch_req" scope="operation" type="OM"/> <enrich> <source type="property" clone="true" property="INIT_MSG_PAYLOAD"/> <target type="body"/> </enrich> <iterate xmlns:rec="urn:partner.soap.sforce.com" xmlns:ns="https://org.apache.synapse/xsd" continueParent="true" preservePayload="false" expression="//rec:records" sequential="true"> <target> <sequence> <property name="DB_BATCH_MSG_PAYLOAD" expression="get-property('operation','DB_BATCH_MSG_PAYLOAD')" scope="default" type="OM"/> <payloadFactory media-type="xml"> <format> <p:CreateContact xmlns:p="https://test.com/test"> <p:Id>$1</p:Id> <p:Name>$2</p:Name> <p:AccountName>$3</p:AccountName> <p:AssistantName>$4</p:AssistantName> <p:AssistantPhone>$5</p:AssistantPhone> <p:CreatedBy>$6</p:CreatedBy> <p:Department>$7</p:Department> <p:Desc>$8</p:Desc> <p:Email>$9</p:Email> <p:HomePhone>$10</p:HomePhone> <p:LastModifiedBy>$11</p:LastModifiedBy> <p:MobilePhone>$12</p:MobilePhone> <p:Title>$13</p:Title> </p:CreateContact> </format> <args> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Id[1]/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Name/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Account/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:AssistantName/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:AssistantPhone/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:CreatedBy/Name/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Department/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Description/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Email/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:HomePhone/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:LastModifiedBy/Name/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:MobilePhone/text()"/> <arg xmlns:sf="urn:sobject.partner.soap.sforce.com" xmlns:p="https://test.com/test" evaluator="xml" expression="//sf:Title/text()"/> </args> </payloadFactory> <enrich> <source type="body" clone="true"/> <target type="property" property="DB_MSG_PAYLOAD"/> </enrich> <property xmlns:p="https://test.com/test" name="DB_MSG_PAYLOAD" expression="//p:CreateContact" scope="default" type="OM"/> <enrich> <source type="property" clone="true" property="DB_BATCH_MSG_PAYLOAD"/> <target type="body"/> </enrich> <enrich> <source type="property" clone="true" property="DB_MSG_PAYLOAD"/> <target xmlns:p="https://test.com/test" action="child" xpath="//p:CreateContact_batch_req"/> </enrich> <property xmlns:ns="https://org.apache.synapse/xsd" xmlns:p="https://test.com/test" name="DB_BATCH_MSG_PAYLOAD" expression="//p:CreateContact_batch_req" scope="operation" type="OM"/> </sequence> </target> </iterate> <property name="DB_BATCH_MSG_PAYLOAD" expression="get-property('operation','DB_BATCH_MSG_PAYLOAD')" scope="default" type="OM"/> <enrich> <source type="property" clone="true" property="DB_BATCH_MSG_PAYLOAD"/> <target type="body"/> </enrich> <callout serviceURL="https://localhost:8280/services/mysql" action="CreateContact_batch_req" useServerConfig="false"> <source xpath="$body/child::*[fn:position()=1]"/> <target xpath="$body/child::*[fn:position()=1]"/> </callout> </sequence>
Invoking the proxy
Finally, to try the service, go to the proxy list and click try it under Salesforce proxy.
Click the send button to execute the proxy. In a few seconds, your MySQL table will be filled with Salesforce contacts.
Conclusion
The latest trend of cloud services provides the facility to store user data on cloud. However, cloud services have limitations and sometimes licensing is quite high. Some businesses may require cloud data to be stored on premise for some applications to be accessed and for various other requirements. This can be easily achieved with connectors introduced in WSO2 ESB 4.8.0.