SAP CPI - Executing Stored Procedure in JDBC Adapt...

former_member183249 · ‎07-05-2020

I was working on JDBC Scenarios , where i was doing lot of UPSERT operations on HANA ASE database tables. JDBC adapter on cloud doesn't support INSERT_UPDATE (UPSERT) action. For any update you need to rely on Stored Procedure or XSJS service. XSJS service creation requires additional effort & involvement of different team for development,deployment & support. To overcome this issue i used the SAP PO schema to implement Stored Procedure in SAP Cloud Platform Integration.

Documentation for JDBC adapter on SAP Cloud Platform Integration:

https://help.sap.com/viewer/368c481cd6954bdfa5d0435479fd4eaf/Cloud/en-US/88be64412f1b46d684dfba11f27...

Creating JDBC Data Source:

https://blogs.sap.com/2019/02/19/cloud-integration-a-simple-demo-scenario-using-the-jdbc-adapter/

Advantages of JDBC Adapter with Stored Procedure:

JDBC adapter is easy to configure & manage

All operations (INSERT/UPDATE/DELETE/UPSERT) can be performed on table data

Detailed trace of the error in case of any failure/exception while execution

You can display 2,147,483,647 records fetched from adapter

You can Increase/decrease Connection Timeout/Query/Response Timeout parameters based on your requirements.

As per the documentation, no limitations on Insert/update/delete operation are mentioned.

I will cover a simple scenario where you can push the data to database tables with EXEXUTE action.

I have taken four columns for testing : {ORDERNO, ORDERDATE, MATERIAL, QUANTITY}

You need to create an XML SQL format schema as JDBC receiver adapter supports XML SQL format protocol. Below is the schema for above fields.

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="PO_REQUEST">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="StatementName" maxOccurs="unbounded" minOccurs="1">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="storedProcedureName">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element type="xs:string" name="table"/>

                    <xs:element name="ORDERNO" 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="ORDERDATE" 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="MATERIAL" 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="QUANTITY" 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>

</xs:schema>

You can also generate structure in below format with additional attributes. Refer below documentation.

https://help.sap.com/viewer/5cf7d2de571a45cc81f91261668b7361/7.5.4/en-US/447b72b2fde93673e10000000a1...

<StatementName>



<storedProcedureName action=" EXECUTE">



<table>realStoredProcedureeName</table>



<param1 [isInput="true"] [isOutput=true] type=SQLDatatype>val1</param1>



</storedProcedureName >



</StatementName>

Once your structure is ready you can perform the source & target Mapping.

record --> StatementName ( 1 - unbounded)

type -> VARCHAR ( as all columns in DB is considered as VARCHAR)

Note: You can also define the isInput="1" (Input Parameter) or isOutput="1" (Output Parameter) attribute for each column type below type attribute.

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver)

Below is the Integration Flow:

Below Input is triggered from Postman.

<?xml version="1.0" encoding="UTF-8"?>

<Root>

   <record>

      <ORDERNO>125</ORDERNO>

      <ORDERDATE>2020-12-01</ORDERDATE>

      <MATERIAL>MPAM731980</MATERIAL>

      <QUANTITY>421633</QUANTITY>

  </record>

     <record>

      <ORDERNO>126</ORDERNO>

      <ORDERDATE>2020-12-01</ORDERDATE>

      <MATERIAL>MPAM731980</MATERIAL>

      <QUANTITY>421633</QUANTITY>

  </record>

       <record>

      <ORDERNO>127</ORDERNO>

      <ORDERDATE>2020-12-01</ORDERDATE>

      <MATERIAL>MPAM731980</MATERIAL>

      <QUANTITY>421633</QUANTITY>

       <record>

      <ORDERNO>132</ORDERNO>

      <ORDERDATE>2020-12-01</ORDERDATE>

      <MATERIAL>MPAM731980</MATERIAL>

      <QUANTITY>421633</QUANTITY>

  </record>

</Root>

Sample UPSERT Stored Procedure Code for your reference.

CREATE PROCEDURE UPSERT_ORDERDETAIL_DEMO(

    IN "ORDERNO" varchar(100),

    IN "ORDERDATE" varchar(50),

    IN "MATERIAL" VARCHAR(150),

    IN "QUANTITY" DOUBLE

)

LANGUAGE SQLSCRIPT AS



BEGIN

     DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;

    DECLARE EXIT HANDLER FOR MYCOND

     BEGIN

    	update  "PACCAR_DB"."ORDERDETAIL_DEMO" set   QUANTITY = QUANTITY+:QUANTITY where

    	ORDERNO = :ORDERNO;

	 end;   

   	

   	INSERT INTO "PACCAR_DB"."ORDERDETAIL_DEMO"

   	VALUES (

	:ORDERNO,

	:ORDERDATE,

	:MATERIAL,

	:QUANTITY);

	

END

Data is successfully inserted into DB table once you trigger the payload from source:

Data can be seen in target table.

I tried to execute an exception where i tried to insert material of length (161) > defined in the table structure (150), below is the detailed error.

It looks JDBC adapter is better option if you are connecting your databases via CPI. You can perform all the operation on your table with the help of Stored Procedures.

Rahul

former_member617538 · ‎01-28-2021

Hi Rahul,

Would you be able to share the integration package for reference?

Thanks & Regards,

Mark

former_member100520 · ‎04-11-2021

Hi Rahul,

Could you please provide the integration package for the above scenario.

Regards,

Bharath Kumar

former_member708359 · ‎07-06-2021

Hi Rahul,

Great Walkthrough with each and every step explained clearly. Although, I have one query. In Help.sap Portal, I can see that only one StatementName occurence is supported by CPI,

But your message has multiple StatementName tags. I am a bit confused about the same, can we have multiple Statement tags?

former_member750216 · ‎08-03-2021

Hi rahul.yadav6,

Above SQL UPSERT stored procedure is not working as expected. It is inserting duplicate entry though rather than exact UPSERT function. Can you please check it?

Regards,

Vikas

former_member183249 · ‎08-13-2021

If you are using stored procedure then you can execute multiple statements at a time but if you are using normal insert then you should use splitter before the JDBC adapter so that one statement goes to DB at a time. Currently batch is not supported by JDBC adapter.

former_member183249 · ‎08-13-2021

Hi Vikas,

I will look into it once I get time. Please try to debug with any sql developer. It was a POC so i didn't do full testing it seems.

Rahul

former_member687189 · ‎09-28-2021

HI Rahul,

I have to just insert the data into database for that i am already using splitter but the data is huge so sometime due to connectivity issue data is getting lost. so i thought of using a stored procedure instead. I tried your stored procedure but its showing syntax errors.

vicky20691 · ‎10-26-2021

This blog deserves special appreciation because in current era when we have no more "moderators" to regulate the blogs and people just write mundane, repetitive blogs just for marketing, this blog stands out to be unique and very relevant (and also first of its kind).

Not to mention SAP has mentioned this in their KB notes as well - 3073748

Kudos my friend.

Regards,

Vikas

former_member183249 · ‎03-01-2022

Thanks VIkas !!

frank_scherie · ‎02-08-2023

Hello Rahul,

we are trying to follow your approach to execute stored procedure / function inside Oracle to process a String containing XML.

create or replace function func_bulk_xml_insert_update(bulkxml VARCHAR2) 

return VARCHAR2

IS

str   VARCHAR2(4000); 

BEGIN

   str := bulkxml || 'ABC';

   return str;

END;

/



create or replace procedure proc_bulk_xml_insert_update(bulkxml VARCHAR2, res OUT VARCHAR2 ) 

IS

str   VARCHAR2(4000); 

BEGIN

   res := bulkxml || 'DEF';   

END;

/

and asking ourself if following format executed in Iflow is correct:

<?xml version="1.0" encoding="UTF-8"?>

<ElementName>

   <StatementName>

      <storedProcedureName action="EXECUTE">

         <table>proc_bulk_xml_insert_update</table>

         <bulkxml isInput="true"  type="VARCHAR">MYINPUTVALUE</bulkxml>

         <res     isOutput="true" type="VARCHAR"></res>

      </storedProcedureName>

  </StatementName>

</ElementName>

we would like to know if its is possible to execute a stored function using as last parameter the result of the stored function in the same way defining the call to stored procedure

<?xml version="1.0" encoding="UTF-8"?>

<ElementName>

   <StatementName>

      <storedProcedureName action="EXECUTE">

         <table>func_bulk_xml_insert_update</table>

         <bulkxml isInput="true"  type="VARCHAR">XYZ</bulkxml>

         <res     isOutput="true" type="VARCHAR"></res>

      </storedProcedureName>

  </StatementName>

</ElementName>

furtheron, we would like to know if it is possible to use a parameter of Type VARCHAR containing XML-content be transforming it like (caution: ampersand needs to be first replaced - otherwise we get faulty XML 🙂

&  replaced by  &amp;

"  replaced by  &quot;

'  replaced by  &apos;

<  replaced by  &lt;

>  replaced by  &gt;

converting a source like

<?xml version="1.0"?><theusers></theusers>

to

&lt;?xml version=&quot;1.0&quot;?&gt;&lt;theusers&gt;&lt;/theusers&gt;

so that final call to stored procedure would look like

<?xml version="1.0" encoding="UTF-8"?>

<ElementName>

   <StatementName>

      <storedProcedureName action="EXECUTE">

         <table>proc_bulk_xml_insert_update</table>

         <bulkxml isInput="true"  type="VARCHAR">&lt;?xml version=&quot;1.0&quot;?&gt;&lt;theusers&gt;&lt;/theusers&gt;</bulkxml>

         <res     isOutput="true" type="VARCHAR"></res>

      </storedProcedureName>

  </StatementName>

</ElementName>

kind regards
Frank Scherie

SAP CPI - Executing Stored Procedure in JDBC Adapter

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z