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>

After you have saved the above in FileName.xsd, the below is what the structure would look like in the message where you would mapping the Source with this newly created Target structure:

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.

Once, your structure is ready, and the message mapping is done, it’s time to use a General Splitter, so as to split the records, and here is what your Iflow should look like the below. 

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,

Sookriti Mishra