Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Connecting to MS SQL Server With Mulesoft

DZone's Guide to

Connecting to MS SQL Server With Mulesoft

Mulesoft provides a wide range of connectors. The MS SQL server can be used with Mulesoft to query data from a table, insert data into a table, and more.

· Integration Zone
Free Resource

Modernize your application architectures with microservices and APIs with best practices from this free virtual summit series. Brought to you in partnership with CA Technologies.

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.

Image title

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.

Image title

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

Image title

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.

Image title

Provide the Parameterized queryas per your requirements.

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

Image title

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

Image titleCode:

<?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.

Image title

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)}

Image title

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

Image title

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/.

Image title

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

The Integration Zone is proudly sponsored by CA Technologies. Learn from expert microservices and API presentations at the Modernizing Application Architectures Virtual Summit Series.

Topics:
mulesoft ,ms sql ,tutorial ,integration

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}