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

Call Azure MS SQL Stored Procedures From Mule ESB

DZone's Guide to

Call Azure MS SQL Stored Procedures From Mule ESB

In this article, I am going to explain how to call a stored procedure that accepts custom defined table types.

· Integration Zone ·
Free Resource

The State of API Integration 2018: Get Cloud Elements’ report for the most comprehensive breakdown of the API integration industry’s past, present, and future.

In this article, I am going to explain how to call a stored procedure that accepts custom defined table types. If you have to send multiple records in a single time to the DB from your front end, that's where we use custom defined table types. 

These types allow multiple rows, which contain different data types like varchar, boolean, etc.

I have a table type below:

USE [myown]
GO

/****** Object:  UserDefinedTableType [dbo].[tblcustomers]    Script Date: 6/2/2018 4:11:08 PM ******/
create TYPE [dbo].[tblcustomers] AS TABLE(

[Title] [nvarchar](8) NULL,
[Suffix] [nvarchar](10) NULL,
[CompanyName] [nvarchar](128) NULL,
[SalesPerson] [nvarchar](256) NULL,
[EmailAddress] [nvarchar](50) NULL,
[PasswordHash] [nvarchar](128) NOT NULL,
[PasswordSalt] [nvarchar](10) NOT NULL

)
GO

A stored procedure, which has table typed parameter is as follows:

USE [myown]
GO
create PROCEDURE [dbo].[fetchtblCusomters](
@tblcustomers tblcustomers READONLY
)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here    
select * from @tblcustomers
END

Now we have to call this stored procedure in Mule, and my flow looks like below:

Image title

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" 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/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
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">
<db:generic-config name="Generic_Database_Configuration" url="jdbc:sqlserver://baraneesql.database.windows.net:1433;database=myown;user=baranee@baraneesql;password=****;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
    <flow name="tabletypeflow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/tabletype" allowedMethods="GET" doc:name="HTTP"/>
        <set-variable variableName="records" value="#[new com.microsoft.sqlserver.jdbc.SQLServerDataTable();]" doc:name="Variable"/>
        <expression-component doc:name="create-records"><![CDATA[records.addColumnMetadata("Title" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("Suffix" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("CompanyName" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("SalesPerson" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("EmailAddress" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("PasswordHash" ,java.sql.Types.NVARCHAR); 
records.addColumnMetadata("PasswordSalt" ,java.sql.Types.NVARCHAR); 

records.addRow('Dr', 'Hr', 'Mindtree', 'tiptop', 'helllo.rai@gmai.com', '', '');  ]]></expression-component>
        <db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[{CALL fetchtblCusomters(:tblcustomers )}]]></db:parameterized-query>
            <db:in-param name="tblcustomers" value="#[flowVars.records]"/>
        </db:stored-procedure>
        <json:object-to-json-transformer doc:name="Object to JSON"/>
    </flow>
</mule>

Mule Steps 

  1. Create a flow Variable object for the class SQLServerDataTable

  2. Add columns to the table type object you have created.

    1. records.addColumnMetadata("Suffix" ,java.sql.Types.NVARCHAR); — In here, Suffix is a column table and it is NVARCHAR SQL type

    2. Once you are done adding columns, add rows to the table

      1. records.addRow('Dr', 'Hr', 'Mindtree', 'tiptpop', 'helllo.rai@gmai.com', '', '');

    3. Now, using database connector, call the Stored Procedure, which has table type parameter.    

Image title

Your SQL Server DB connection will be:

Image title

That's it, you have your multiple records flying through to SQL Server DB in a single time.

Your API is not enough. Learn why (and how) leading SaaS providers are turning their products into platforms with API integration in the ebook, Build Platforms, Not Products from Cloud Elements.

Topics:
azure sql ,mule ,sql server

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}