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

SnapLogic is the leading self-service enterprise-grade integration platform. Download the 2018 GartnerMagic Quadrant for Enterprise iPaaS or play around on the platform, risk free, for 30 days.

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.

With SnapLogic’s integration platform you can save millions of dollars, increase integrator productivity by 5X, and reduce integration time to value by 90%. Sign up for our risk-free 30-day trial!

Topics:
azure sql ,mule ,sql server

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}