DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • The Ultimate Guide on DB-Generated IDs in JPA Entities
  • Reference Architecture: Deploying WSO2 API Manager on Microsoft Azure

Trending

  • From ETL to ELT to Real-Time: Modern Data Engineering with Databricks Lakehouse
  • Misunderstanding Agile: Bridging The Gap With A Kaizen Mindset
  • From Monolith to Containers: Real-World Migration Blueprint
  • Modernizing Financial Systems: The Critical Role of Cloud-Based Microservices Optimization
  1. DZone
  2. Data Engineering
  3. Databases
  4. Connecting to MS SQL Server With Mulesoft

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.

By 
Jitendra Bafna user avatar
Jitendra Bafna
DZone Core CORE ·
Mar. 16, 17 · Tutorial
Likes (5)
Comment
Save
Tweet
Share
45.3K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Microsoft SQL Server MySQL Relational database MuleSoft application

Opinions expressed by DZone contributors are their own.

Related

  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • The Ultimate Guide on DB-Generated IDs in JPA Entities
  • Reference Architecture: Deploying WSO2 API Manager on Microsoft Azure

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: