Technical Articles
SAP Cloud Integration (CPI/HCI) || JDBC Adapter (Part 2) || Update the DB using JDBC adapter via a Stored Procedure (Fields as parameter)
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
Part 3: Update the DB using JDBC adapter via a Stored Procedure (Parameter as XML Type)
And, this is Part 2:
In my previous blog 🖐, we discussed all necessary configurations that is a must if you want to use JDBC Adapter. To sum up, the previous blog covered, Cloud Connector configuration, Uploading driver, creating a Data Source, and a sample call to the DB to check if the configurations done are working fine or not.
In this blog, we will see how to update a table using a stored procedure.
To call the stored procedure, you will need an XML SQL format schema. Basically the idea is to use this schema as a Target Structure in the Message Mapping to map the incoming Payload from the Source.
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="zElementName">
<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="FIELD1" maxOccurs="1" minOccurs="0">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute type="xs:string" name="type"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="FIELD2" maxOccurs="1" minOccurs="0">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute type="xs:string" name="type"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="FIELD3" maxOccurs="1" minOccurs="0">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute type="xs:string" name="type"/>
</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>
StatementName > is where you pass the item node coming from the Source Structure, basically the repeating node,
@action > Constant: EXECUTE
table > Constant: storedProdecureName
FIELD1, FIELD2, FIELD3 > these are basically the fields which you would be updating the DB and the sequence and the case (upper/ low) should be same as it’s written in the Stored Procedure.
@type > this information also, you can collect from the Stored Procedure, to understand the DataType VARCHAR/ DATE/ anything else.
Here is what the paylaod which goes to Receiver looks like:
<?xml version='1.0' encoding='UTF-8'?>
<ElementName>
<StatementName>
<storedProcedureName action="EXECUTE">
<table>storedProcedureName</table>
<Field1 type="VARCHAR">Value1</Field1>
<Field2 type="VARCHAR">Value2</Field2>
<Field3 type="VARCHAR">Value3</Field3>
</storedProcedureName>
</StatementName>
</ElementName>
Now, the QUESTION is, if we put a splitter, basically system will insert one record at a time. Can we post multiple recordsets at a time?
The ANSWER is, YES, we can! Stay tuned for the next blogpost.
To be honest, inserting one record at a time is super-time-consuming. Hence, if the volume is huge, I would suggest you implement the next part.
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,
Hi Sookriti,
Can you please share the 3rd blog in which we will not use splitter and directly use stored procedure.