Skip to Content
Technical Articles
Author's profile photo Rahul Yadav

SAP CPI – Executing Stored Procedure in JDBC Adapter

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/88be64412f1b46d684dfba11f2767c5b.html

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/447b72b2fde93673e10000000a114a6b.html

<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

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mark Chuen Teck CHING
      Mark Chuen Teck CHING

      Hi Rahul,

       

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

       

      Thanks & Regards,

      Mark

      Author's profile photo Bharat Kumar
      Bharat Kumar

      Hi Rahul,

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

      Regards,

      Bharath Kumar

      Author's profile photo xyz pqr
      xyz pqr

      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?

      Author's profile photo Rahul Yadav
      Rahul Yadav
      Blog Post Author

      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.

      Author's profile photo Vikas Kumar
      Vikas Kumar

      Hi Rahul Yadav,

      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

      Author's profile photo Rahul Yadav
      Rahul Yadav
      Blog Post Author

      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

      Author's profile photo Sonam Ramsinghani
      Sonam Ramsinghani

      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.

      Author's profile photo Vikas Kumar Singh
      Vikas Kumar Singh

      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

      Author's profile photo Rahul Yadav
      Rahul Yadav
      Blog Post Author

      Thanks VIkas !!

      Author's profile photo Frank Scherie
      Frank Scherie

      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