How To Connect To Azure SQL Database With MuleSoft

If you would like to understand the basics of MuleSoft, refer to this article before going next:

• Getting Started MuleSoft

Database Schema and Procs

This is the database payout which will be queried out from the SQL server to MuleSoft.

Getting Started,

Start Anypoint Studio to get started.

Create a new Mule Project from the File tab.

Provide the project name and leave everything default and click Finish.

Now drag and drop HTTP Listener from Mule Palette to designer view.

Now configure Listener to set some properties. Click on Listeners and click on Add button to set the Connector configuration.

Keep everything the same, click the Test connection and OK button.

Enter Path value.

Setup JDBC

You'll need to do the following to connect the MS SQL server with Mulesoft.

Download and install the sqljdbc4.2.jar file. It is required for you to connect to the MS SQL database. http://www.java2s.com/Code/Jar/s/Downloadsqljdbc420jar.htm

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

Add sqljdbc4.2.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.

Go to Java Build Path> Libraries > JRE System Library (JDK 8 (Embedded)) > Add External JARs…

Locate the .jar file from the downloaded location and click Open.

Click Appy and close button.

Setup SQL Server

Search the database in Mule Palette, click on database > select, and drag and drop on Message flow.

Select database > Select and go to properties.

Configure the database connection and other required fields, and click the ADD button.

Enter required details like Name, connection, Host, Port, User Password, and Database name. I am using the Azure SQL server database for this sample, and click test connection and click OK.

Enter select query in SQL Query Text.

Search for Transform Message in Mule Palette,

Drag and drop Transform Message in Message Flow.

The last thing we must configure is the Transform Message to convert the data from the database to JSON. Use the following DataWeave script.

Run and deploy the application.

Open postman to see the API result set.

As you can see database output is in JSON format.

Conclusion

In this article, we learned how to connect MuleSoft with the Azure SQL database and get and display data in JSON format.