{{announcement.body}}
{{announcement.title}}

Call a Stored Procedure From Mule ESB

DZone 's Guide to

Call a Stored Procedure 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

Man talking on cell phone outside

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:

SQL
x
11
1
USE [myown] GO 
2
/****** Object: UserDefinedTableType [dbo].[tblcustomers] Script Date: 6/2/2018 4:11:08 PM ******/
3
create TYPE [dbo].[tblcustomers] AS TABLE(
4
  [Title] [nvarchar](8) NULL, 
5
  [Suffix] [nvarchar](10) NULL, 
6
  [CompanyName] [nvarchar](128) NULL, 
7
  [SalesPerson] [nvarchar](256) NULL, 
8
  [EmailAddress] [nvarchar](50) NULL, 
9
  [PasswordHash] [nvarchar](128) NOT NULL, 
10
  [PasswordSalt] [nvarchar](10) NOT NULL
11
) GO


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

SQL


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

Image title

XML

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.

Topics:
azure sql ,integration ,mule ,sql server ,tutorial

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}