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