Running MsSQL Stored procedures in Mule

Running MsSQL Stored procedures in Mule

In this Project we are connecting to MsSQL Stored Procedure to fetch the Result Set in JSON Format.

Pre-requisites:

1. Anypoint Studio
2. MsSQL database and Stored Procedure details
3. JDK 7

Project Structure:

Mule Flow:

Generic Database Configuration:

Data Source Configuration:

Database Component Configuration:

Logger Component Configuration:

Http Component Configuration:

Mule Configuration Flow:

<?xml version="1.0" encoding="UTF-8"?> <mule xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:json="http://www.mulesoft.org/schema/mule/json"xmlns:db="http://www.mulesoft.org/schema/mule/db"  xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"  xmlns:spring="http://www.springframework.org/schema/beans" version="EE-3.5.2"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:schemaLocation="http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd"> <spring:beans>  <spring:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"  name="mssqlDataSource" destroy-method="close">  <spring:property name="username" value="${DS_DB_USER}" />  <spring:property name="password" value="${DS_DB_PASSWORD}" />  <spring:property name="url" value="${DS_DB_URL}" />  <spring:property name="driverClassName" value="${DB_DRIVER}" />  <spring:property name="removeAbandoned" value="true" />  <spring:property name="initialSize" value="1" />  <spring:property name="maxActive" value="3" />  <spring:property name="maxIdle" value="1" />  <spring:property name="maxWait" value="5000" />  </spring:bean>  </spring:beans>  <db:generic-config name="Generic_Database_Configuration"  dataSource-ref="dataSource" doc:name="Generic Database Configuration">  <db:pooling-profile />  </db:generic-config> <flow name="mssqltestFlow1" doc:name="mssqltestFlow1"> <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" doc:name="HTTP"/> <db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Database"> <db:parameterized-query><![CDATA[{call test.fetchResults(:id)}]]></db:parameterized-query> <db:in-param name="id" type="INTEGER" value="#[payload]"/> </db:stored-procedure> <logger message="#[payload.resultSet1]" level="INFO" doc:name="Logger"/> <json:object-to-json-transformer doc:name="Object to JSON"/> </flow> </mule>