Hello CPI Enthusiasts,

This series covers all about JDBC Adapter, and here are list of blogs:
Part 1: Pre-requisites and Configuration
Part 2: Update the DB using JDBC adapter via a Stored Procedure (Fields as parameter)
Part 3: Update the DB using JDBC adapter via a Stored Procedure (Parameter as XML Type)

And, this is Part 3:

In Part 1, we saw how to configure a JDBC Adapter, and in Part 2, we saw how to update field value using JDBC Adapter but using one entry at a time.

Well, when I implemented Part 2, it was a disaster  and here is a before and after:

It’s sad right?

So, here is the solution. Okay, disclaimer, I am no DB expert but a friend who is an expert helped me write a Stored Procedure in which he provisioned a Parameter which was of type XML, Cloud Integration (CPI) could pass the entire payload in just one go, into the Parameter of XML type.

The stored procedure would look something like this:

USE [SID] GO 
/****** Object:  StoredProcedure [dbo].[storedProcedureName]    Script Date: 29/9/2021 8:16:52 AM ******/
SET 
  ANSI_NULLS ON GO 
SET 
  QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[storedProcedureName] @XMLParameterName as XML AS BEGIN 
SET 
  NOCOUNT ON;
begin tran begin try INSERT INTO [dbo].storedProcedureName ([FIELD1], [FIELD2], [FIELD3]) 
SELECT 
  CUST.item.value('(FIELD1)[1]', 'nvarchar(250)') as 'FIELD1', 
  CUST.item.value('(FIELD2)[1]', 'nvarchar(250)') as 'FIELD2', 
  CUST.item.value('(FIELD3)[1]', 'nvarchar(250)') as 'FIELD3', 
  GETDATE() AS [PROCESSDATE] 
FROM 
  @XMLParameterName.nodes('/ElementName/item') as CUST(item) commit tran end try begin catch DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT 
  @ErrorMessage = ERROR_MESSAGE(), 
  @ErrorSeverity = ERROR_SEVERITY(), 
  @ErrorState = ERROR_STATE();
--Print @ErrorMessage
RAISERROR (
  @ErrorMessage, @ErrorSeverity, @ErrorState
);
rollback tran end catch END

I connected with our DB Expert Baraneetharan (Click here to follow him on LinkedIn), to understand the stored procedure, and in a one-one-interview with him, here is what he has to say.

Sookriti: Hey Baranee. First of all, thank you so much for writing the stored procedure. I have a few questions, are you ready?

Baranee:

Sookriti: Haha. This meme is a delight for a “The Office” fan. So, please tell our readers a little about the stored procedure.

Baranee: The functionality behind this stored proc is to read data from payload and load to destination SQL DB. As mentioned in previous post, you can call the stored proc by passing table field inputs as parameters, then stored proc will do insert operation for you. Below is the sample stored proc format for your reference,

CREATE PROCEDURE PROCEDURE_NAME @parameter1 varchar(250), 
@ parameter2 varchar(250), 
@ parameter3 varchar(250) AS BEGIN INSERT INTO TABLE_NAME ([column1], [column2], [column3) 
VALUES 
  (
    @parameter1, @ parameter2, @ parameter3
  ) commit tran END

Above stored proc works better when you have few records to be inserted into the destination, since stored proc is called for every record set to insert. When your payload has huge record set and n number of columns to be loaded in destination table, there comes the performance impact. But no worries, we have an alternate solution to overcome the performance impact which fits better for huge column and record set.

Instead calling stored proc multiple times by passing column values as inputs parameters, we can call the stored proc only once with entire record set as single parameter. Here we pass entire payload in an XML format to stored proc as an XML String parameter. Stored proc will read the entire XML data as string, get the record set split as individual records based on the node values from string and get those records inserted into the destination in secs. Below is the sample stored proc format for your reference,

CREATE PROCEDURE PROCEDURE_NAME @XMLparameter as XML AS BEGIN 
SET 
  NOCOUNT ON;
INSERT INTO TABLE_NAME ([column1], [ column2], [ column3) 
SELECT 
  CUST.item.value('(column1)[1]', 'nvarchar(250)') as column1, 
  CUST.item.value('(column2)[1]', 'nvarchar(250)') as column2, 
  CUST.item.value('(column3)[1]', 'nvarchar(250)') as column3 
FROM 
  @ XMLparameter.nodes(
    '/node1/node2) as CUST(item)

END

After having this stored procedure written, here is what the integration flow will look like:

The target structure (XSD) to call the Stored Procedure would be (2nd Message Mapping block as mentioned in the above screenshot):

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="ElementName">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="StatementName" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="storedProcedureName">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element type="xs:string" name="table"/>
                    <xs:element name="XMLParameterName" maxOccurs="unbounded" minOccurs="0">
                      <xs:complexType>
                        <xs:simpleContent>
                          <xs:extension base="xs:string">
                            <xs:attribute type="xs:string" name="type"/>
                            <xs:attribute type="xs:string" name="isInput"/>
                          </xs:extension>
                        </xs:simpleContent>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute type="xs:string" name="action"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now, here is what we need to pass into this structure?

Here are the constants:

• /ElementName/StatementName/storedProcedureName/@action: EXECUTE
• /ElementName/StatementName/storedProcedureName/table: storedProcedureName
• /ElementName/StatementName/storedProcedureName/XMLParameterName/@type: CLOB
• /ElementName/StatementName/storedProcedureName/XMLParameterName/@isInput: true

The output payload would look something like below:

<?xml version="1.0" encoding="UTF-8"?>
<ElementName>
    <StatementName>
        <storedProcedureName action="EXECUTE">
            <table>storedProcedureName</table>
            <XMLParameterName type="CLOB">
                <![CDATA[

<ElementName>
    <item>
        <Field1>Value1.1</Field1>
        <Field2>Value2.1</Field2>
        <Field3>Value3.1</Field3>
    </item>
    <item>
        <Field1>Value1.2</Field1>
        <Field2>Value2.2</Field2>
        <Field3>Value3.2</Field3>
    </item>
    <item>
        <Field1>Value1.3</Field1>
        <Field2>Value2.3</Field2>
        <Field3>Value3.3</Field3>
    </item>
</ElementName>

]]>
            </XMLParameterName>
        </storedProcedureName>
    </StatementName>
</ElementName>

You have already seen the Before and After images, but I am posting it again for special effects. Haha

Okay, then. 
Hope you had a good read.

If you wish to know more, or have any queries on this blog, then please feel free to drop a comment.

Follow me on linked in by clicking here.

Thanks & Regards,

Sookriti Mishra