Connecting to MS SQL Server With Mulesoft

Mulesoft provides a wide range of connectors, which is very handy for integrating solutions. In many scenarios, we are required to connect to the database for querying, inserting, and updating the table. Mulesoft provides a database connector that allows JDBC connectivity with the relational database to execute various SQL operations like select, insert, update, delete, and store procedures.

Mulesoft provides a database connector to connect various JDBC databases like MS SQL, Oracle, Derby, MYSQL, etc.

Prerequisites

To connect the MS SQL server with Mulesoft, you'll need to do the following.

• Download and install the sqljdbc4.jar file. It is required for you to connect to the MS SQL database.

• Ensure that the TCP/IP port is enabled in SQL configuration (default port is 1433).

• Add sqljdbc4.jar into the build path of your Mule application. Right-click on your application, then do Build Path > Configure Build Path > Libraries > Add External Jars.

Connect MS SQL Server to Query Data From Table

Place HTTP listener components at the message source in the Mule flow and configure them.

Use the flow variable to store the query parameter. This flow variable can be used in a select query as a filter expression.

Place the database component at the message processor, configureGeneric Database Configuration, and press OK.

Now, provide the Database URL and Driver Class Name. Press OK. 

Make sure you are selecting the operation. In this case, we will use the select operation.

• URL: jdbc:sqlserver://${mssql.server}:${mssql.port};databaseName=${mssql.database};user=${mssql.user};password=${mssql.password}.

• Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver.

Provide the Parameterized queryas per your requirements.

SELECT Country,Year,TotalPopulation FROM populationdata Where Country= #[flowVars.country]

Now, the select query will returnt the result as a java.util.LinkedList object. So, you need to use the Object to JSON transformer.

Code:

<?xml version="1.0" encoding="UTF-8"?>
<mule
xmlns:db="http://www.mulesoft.org/schema/mule/db"
xmlns:json="http://www.mulesoft.org/schema/mule/json"
xmlns:http="http://www.mulesoft.org/schema/mule/http"
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"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="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/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd">
<http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>
<db:generic-config name="Generic_Database_Configuration" url="jdbc:sqlserver://server:1433;databaseName=population;user=userName;password=password" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
<flow name="mssqlserver-exampleFlow">
<http:listener config-ref="HTTP_Listener_Configuration" path="/country" allowedMethods="GET" doc:name="HTTP"/>
<set-variable variableName="country" value="#[message.inboundProperties.'http.query.params'.country]" doc:name="Variable"/>
<db:select config-ref="Generic_Database_Configuration" doc:name="Database">
<db:parameterized-query>
<![CDATA[SELECT Country,Year,TotalPopulation FROM populationdata Where Country= #[flowVars.country]]]>
</db:parameterized-query>
</db:select>
<json:object-to-json-transformer doc:name="Object to JSON"/>
</flow>
</mule>

Test the Application

We will use Postman to test the application. This URL will be used to invoke the Mule flow: http://localhost:8081/country?country=India.

Connect MS SQL Server to Insert Data Into Table

Place the HTTP listener components at the message source in Mule flow and configure them. This time, we will use the POST method.

Place the database component at message processor, configure the Generic Database Configuration the same way that you did before, and press OK.

Make sure you are selecting the operation. In this case, we will use the store procedure operation.

CREATE PROCEDURE P_insertpopulation @Country         VARCHAR(100), 
                                    @Year            VARCHAR(100), 
                                    @TotalPopulation VARCHAR(20) 
AS 
  BEGIN 
      INSERT INTO populationdata 
                  (country, 
                   year, 
                   totalpopulation) 
      VALUES      (@Country, 
                   @Year, 
                   @TotalPopulation) 
  END 

Provide the Parameterized Query depending on your requirements.

{CALL p_InsertPopulation (:Country, :Year , :TotalPopulation)}

Add the Parameter and Map fields to the input JSON message.

Code:

<?xml version="1.0" encoding="UTF-8"?>
<mule
xmlns:db="http://www.mulesoft.org/schema/mule/db"
xmlns:json="http://www.mulesoft.org/schema/mule/json"
xmlns:http="http://www.mulesoft.org/schema/mule/http"
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"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="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/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd">
<http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>
<db:generic-config name="Generic_Database_Configuration" url="jdbc:sqlserver://server:1433;databaseName=population;user=userName;password=password" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
<flow name="mssqlserver-exampleFlow">
<http:listener config-ref="HTTP_Listener_Configuration" path="/country" allowedMethods="POST" doc:name="HTTP"/>
<db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Database">
<db:parameterized-query>
<![CDATA[{CALL p_InsertPopulation (:Country, :Year , :TotalPopulation)}]]>
</db:parameterized-query>
<db:in-param name="Country" type="VARCHAR" value="#[json:Country]"/>
<db:in-param name="Year" type="VARCHAR" value="#[json:Year]"/>
<db:in-param name="TotalPopulation" type="VARCHAR" value="#[json:TotalPopulation]"/>
</db:stored-procedure>
</flow>
</mule>

Input message:

{
  "Country": "India",
  "Year": "2005",
  "TotalPopulation": "1000000"
}

Test the Application

We will use Postman to test the application. The URL used to invoke the Mule flow is: http://localhost:8081/.

Now, you know how to connect the MS SQL server to perform various operations like select, insert, update, delete, and stored procedure.