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

      7 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.