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

WSO2 is the only open source vendor to be named a leader in The Forrester Wave™: API Management Solutions, Q4 2018 Report. Download the report now or try out our product for free.

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.

If you need to set table type name because of the different schema,

records.setTvpName("[communication].tblcustomers");
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', 'Mindtr', 'pup', 'helllo', '', '');  


your flow looks something like below.

<?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://A4LNKML17080;database=myown;user=sa;password=Admin123!;instanceName=Bizsql;" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8088" doc:name="HTTP Listener 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.setTvpName("[communication].tblcustomers");
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', 'Mindtr', 'pup', 'helllo', '', '');  ]]></expression-component>
        <db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Connect-azure-sqlserver-db">
            <db:parameterized-query><![CDATA[{CALL [communication].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>


Read the WSO2 Methodology for Agility to see how you can transform your integration projects from semi-agile to a scalable continuous agile approach.

Topics:
azure sql ,mule ,sql server

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}