SAP CPI - JDBC Adapter - Upsert Single & Bulk using Direct SQL, Splitter and Stored Procedure with XML Payload without Looping

[yeeloon-khoo]

Hello CPI Integrators,

This blog post focus on SAP CPI JDBC UPSERT operation, below videos show example on how to perform single upsert and bulk upsert, Direct SQL and stored procedure approach, and Splitter looping and Stored procedure XML Bulk Upsert without Looping (even inside stored procedure).

SAP CPI - JDBC Adapter - UPSERT Single & Bulk using Direct SQL & Stored Procedure with XML Payload:



SAP CPI - JDBC Adapter - Performance Upsert Each Row using Splitter vs Upsert using XML Bulk Upsert:



Below are SQL/Stored procedure used in above videos:

CREATE TABLE - Direct SQL (you can use this to create same table to try out examples):

CREATE TABLE [dbo].[SpecialCustomers](

	[CustomerID] [nchar](5) NOT NULL,

	[CompanyName] [nvarchar](40) NOT NULL

 CONSTRAINT [PK_SpecialCustomers] PRIMARY KEY CLUSTERED 

(

	[CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

UPSERT - Single Record - Direct SQL:

UPDATE SpecialCustomers SET CompanyName = 'A Name from Update'

WHERE CustomerID = 'AAAAA';

IF @@ROWCOUNT = 0

BEGIN

    INSERT INTO SpecialCustomers (CustomerID, CompanyName)

    VALUES ('AAAAA', 'A Name from Insert');

END

 

Create Stored Procedure Upsert_SpecialCustomer:

CREATE PROCEDURE Upsert_SpecialCustomer

	@CustomerID varchar(5),

	@CompanyName varchar(40)

AS

BEGIN

	UPDATE SpecialCustomers SET CompanyName = @CompanyName

	WHERE CustomerID = @CustomerID;

	IF @@ROWCOUNT = 0

	BEGIN

		INSERT INTO SpecialCustomers (CustomerID, CompanyName)

		VALUES (@CustomerID, @CompanyName);

	END

END

 

EXECUTE UPSERT - Single Record - Direct SQL :

Upsert_SpecialCustomer @CustomerID = 'AAAAA', @CompanyName = 'A Name Upsert';

 

EXECUTE UPSERT - Single Record - XML SQL :

<root>

    <statement>

        <storedProcedureName action="EXECUTE">

            <table>Upsert_SpecialCustomer</table>

            <CustomerID isInput="true" type="VARCHAR">AAAAA</CustomerID>

            <CompanyName isInput="true" type="VARCHAR">A Name from SP</CompanyName>

        </storedProcedureName>

    </statement>

</root>

 

Create Stored Procedure XMLBatchUpsert_SpecialCustomers :

CREATE PROCEDURE XMLBatchUpsert_SpecialCustomers

	@XMLData XML

AS 

BEGIN 

	SELECT

	  CUST.item.value('(CustomerID)[1]', 'varchar(5)') as CustomerID, 

	  CUST.item.value('(CompanyName)[1]', 'varchar(40)') as CompanyName

	INTO #TempSpecialCustomers

	FROM 

	  @XMLData.nodes('/root/item') as CUST(item)



	UPDATE SpecialCustomers

	SET CompanyName = T.CompanyName

	FROM SpecialCustomers AS C, #TempSpecialCustomers AS T

	WHERE C.CustomerID = T.CustomerID



	INSERT INTO SpecialCustomers (CustomerID, CompanyName)

	SELECT CustomerID, CompanyName

	FROM #TempSpecialCustomers AS T

	WHERE NOT EXISTS(SELECT * FROM SpecialCustomers WHERE CustomerID = T.CustomerID)

END

 

EXECUTE UPSERT - Multiple Record - XML SQL:

<root>

    <statement>

        <storedProcedureName action="EXECUTE">

            <table>XMLBatchUpsert_SpecialCustomers</table>

            <XMLData isInput="true" type="CLOB"><![CDATA[<root>

	<item>

		<CustomerID>AAAAA</CustomerID>

		<CompanyName>AA Name</CompanyName>

	</item>

	<item>

		<CustomerID>BBBBB</CustomerID>

		<CompanyName>BB Name</CompanyName>

	</item>

	<item>

		<CustomerID>CCCCC</CustomerID>

		<CompanyName>CC Name</CompanyName>

	</item>

    <item>

		<CustomerID>DDDDD</CustomerID>

		<CompanyName>DD Name</CompanyName>

	</item>

</root>]]></XMLData>

        </storedProcedureName>

    </statement>

</root>

 

Hope you found above content useful.

 

Above videos from my published SAP CPI course. If interested on other CPI JDBC lessons or CPI topics, can check out the course at this link. Thanks.

[celo_berger]

Hi Yee,

 

Thank you for your blog, this is exactly what we're looking to implement, but with CPI sending data to HANA Cloud.

 

Do you happen to have an example of the  Create Stored Procedure XMLBatchUpsert_SpecialCustomers but specifically created in HANA, as it seems the syntax is slightly different in HANA from the one you provide.

 

Much appreciated,

 

Marcelo

[yeeloon-khoo]

Hi Marcelo,

Not done in SAP Hana db/stored prodecure before, yes, syntax and way to handle xml is different between mssql and hana db.

Tried check a bit, below might be relevant to apply same concept:

 

Create local temp table in hana db:

http://www.hanaexam.com/p/sap-hana-temporary-tables.html?m=1

 

Read xml string to xml table in hana db:

https://blogs.sap.com/2017/08/14/processing-xml-data-in-sap-hana/

 

Possible can dynamic create temp table by select from xml table?

https://www.kodyaz.com/sap-abap/create-table-on-hana-database-using-select-statement-dynamically.asp...

 

then the rest is standard SQL stuff.

 

Above is what i can find out although not tried before, hope it help u somehow.

 

Regards,

Yee Loon

[celo_berger]

Thanks Yee,

 

I had seen the xml to string to xml table blog earlier today, but it didn't have an example of how to encapsulate the logic within the stored procedure, which is what I need.

 

That is the only missing piece for now. I'll continue to dig around, and if you happen to play around with it in a HANA DB and figure it out, please share your findings 🙂

 

Thank you!

[philippeaddor]

Hi Yee Loon

That's very helpful information! Exactly what I needed. Excellent work.

 

Thank you so much,

Philippe

 

 

[Chaitanya444]

Hi Yee Loon,

 

What if the structure of the source xml changes? Is there a way to create the table structure dynamically depending on the xml structure?

 

Regards,

Chaitanya.

[yeeloon-khoo]

Hi Chaitanya,

You looking for dynamically create table and columns based on input xml? Don't think this is possible, since example here those table and columns are fixed and already pre-defined, but just insert or update rows only.

[Rutuja_Thakre]

Hi Yee Loon Khoo,

Your blog is very informative.

I have applied the same for my interface where i need to do upsert and delete.

I have applied the same logic as you mentioned in this blog but I am facing one issue while sending the date field.Its basically the conversion issue.

the format i am sending the date field in the stored procedure : Record.item.value('(CHANGE_DATE)[1]', 'datetime') as CHANGE_DATE 

error : CallableStatementCallback; uncategorized SQLException; SQL state [S0001]; error code [241]; Conversion failed when converting date and/or time from character string.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

Regards,

Rutuja

[JunwooPark]

Hi Yee,

 

I take your course in Udemy and I upload QnA.

 

Recently, there seems to have been a change to the MS SQL connection using JDBC in CLOUD CONNECTOR.

the JDBC environment settings are different and I'm still getting the error.

Please check and answer my question

 

Best Regards

Leo

[rahma_benabdallah61]

Hello marceloberger18

 

We're also looking to implement with CPI sending data to HANA Cloud,

Have you found a solution to upsert data from CPI to HANA DB ?

 

Thank you in advance,

Rahma.

 

[joel_langoyan]

Hi rahma_benabdallah61,

Were you able to figure this out? Need to do the same for one of my scenario.

Thanks,

Joel