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.
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:
x
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:
xxxxxxxxxx
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:
xxxxxxxxxx
<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"></db:generic>
<flow name="tabletypeflow">
<http:listener config-ref="HTTP_Listener_Configuration" path="/tabletype" allowedMethods="GET" doc:name="HTTP"></http:listener>
<set-variable variableName="records" value="#[new com.microsoft.sqlserver.jdbc.SQLServerDataTable();]" doc:name="Variable"></set>
<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', '[email protected]', '', ''); ]]>
</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:in>
</db:stored-procedure>
<json:object-to-json-transformer doc:name="Object to JSON"></json:object>
</flow>
</mule>
Mule Steps
Create a flow Variable object for the class SQLServerDataTable
Add columns to the table type object you have created.
records.addColumnMetadata("Suffix" ,java.sql.Types.NVARCHAR); — In here, Suffix is a column table and it is NVARCHAR SQL type
Once you are done adding columns, add rows to the table
records.addRow('Dr', 'Hr', 'Mindtree', 'tiptpop', '[email protected]', '', '');
Now, using database connector, call the Stored Procedure, which has table type parameter.
Your SQL Server DB connection will be:
That's it, you have your multiple records flying through to SQL Server DB in a single time.
Opinions expressed by DZone contributors are their own.