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>

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>

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>