Skip to Content
Technical Articles
Author's profile photo Ricardo Viana
Ricardo Viana
June 25, 2023 7 minute read

SAP CPI / PI – Hybrid concept to Data Base – Batch and Stored Procedure

Hello Folks,

One more interesting blog and sharing the knowledge and experience with you, about integration of SAP CPI and SQL JDBC to use batch mode and stored procedure.

Maybe comes in your mind the question, why needs stored procedure ?

If yes I will tell you later…

I’m not going to explain deep the concept of hybrid, setup of SAP PI/PO, bussiness process, stored procedure details in data base, step by step because there is many bogs about those topics that easily you can find the list below:

Worth to mention another blogs and helps in relation to this topic:

  • Stored Procedure:
  • Step by Step JDBC:

 

Introduction:

The blog is to present the overvire of integration in Hybrid concept implementation ( SAP CPI responsible for development –  SAP PI/PO Runtime responsible to receive and send the message across the applications on-premise.

Why this definition ? Advantages ?

The definition is because those applications are running on-premise under the same network, but thinking forward with possible migration from on-premise to on-cloud project.

Advantages:

  • Same network ( Avoid externalize calls between on-prem and on-cloud )
  • Avoid network issues
  • Faster
  • Security
  • Migration to Cloud
  • Reliable
  • Others…

OOOO… I almost forgot, start to think in the future with SAP about ground to ground integrations using the Edge Integration Cell – Neuron Edge

 

To-Be – Neuron Edge:

 

Neuro Edge you can provide to your customer ON-PREMISE to ON-PREMISE with public cloud for example, this service bring this capabilities all in cloud:

  • On-premise for SAP integration Suite could tenant ( Not more on-premise SAP PI )
  • Customer move from SAP PI/PO to SAP Integration Suite
  • On-premise API Gateway
  • Event security bridge from SAP “Legacy” to Event Mesh or NO-SAP event brokers

The Neuro Edge is made for groud to ground implementations where you have SAP Integration Suite in the private cloud this contains some components for integration to holding integrations Ground-Ground.

Low footprint, high performance, pervasive, reliable.

Also interesting that you can have many instances of Edge as the sample of tenant spread around the world supporting the ground to ground integrations:

I will explore this topic in future blog.

Integration architecture perspective – On-prem to On-prem:

The scenario is the integration of SAP with JDBC, as you can see in the diagram I’m still using the real SAP PI runtime and not the new SAP offering for this which they call it as “Neuron Edge” as I explained above.

AS-IS – PI Runtime:

 

The SAP PI/PO will be responsible to receive the call, proceed with the transformation of message from XML to SAP XML SQL format and decide if the stored procedure should be call or not depends of the case.

Ok, I almost forgot after this diagram, let’s remind the topic why the designer is JDBC call + stored procedure ?

It’s because the SAP JDBC adapter does not support UPDATE_INSERT operation in the HANA  ASE database tables, Microsoft SQL Service and others, note that SQL XML payload is supported in INSERT, UPDATE and DELETE modes only.

The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.JDBC adapter on cloud doesn’t support INSERT_UPDATE (UPSERT).

Solution overview:

To solve this problem using the JDBC adapter and without hard developments in SAP CPI – Groovy or JavaScript confirmations, just request the stored procedure to support INSERT_UPDATE doing first INSERT in temporary table and EXECUTE the stored procedure next call.

The stored procedure will be responsible to reproduce the INSERT_UPDATE, reading first if the next record already exist, if YES, update if NOT, create a new one.

Stored Procedure Explanation

Everything is clear until now ? Let’s start the hot hands on topic now.

 

Integration Flow developer in SAP CPI:

The Iflow below represents the integration between a one pre-flow responsible to receive the SOAP call then routing via Value Mapping the respective target Process Direct:

  • 1 – receiver call via process direct
  • 2 – Routing if exist payload
  • 3 – Execute groovy script to generate the SQL XML ( XML to XML using XmlSluper() )
  • 4 – Multi-cast sequential, first INSERT then Stored Procedure,
  • 5 – Routing if need keep Stored Procedure or Not in the future.

The groovy script why not message mapping ?

Depends of the approach of developer, in this case one groovy doing mapping, log and log exception – 1 groovy program instead of, message mapping, one groovy for log, one groovy for exception.

I just did this decision also because the mapping was quite simple without complex logic and others, just basically rebuild the XML with another tags and details required by JDBC adapter call.

Groovy dummy to create the XML SQL:

def Message YourFunctionName (Message message) {
    // Reader the input body as InputStream
    Reader reader = message.getBody(Reader)
    // Instance of XmlSlurper to parsing the input stream
    def inputXML = new XmlSlurper().parse(reader)
    // Instance of writer string function
    Writer writer = new StringWriter()
    // Instance of indentPrinter function
    def indentPrinter = new IndentPrinter(writer, ' ')
    // Instance of MakeupBuilder function with indentPrinter variable
    def builder = new MarkupBuilder(indentPrinter)
    builder.'XmlSQL'{
        // Variable to store the attibute array
        def array = inputXML.ARRAY FROM YOUR XML
        array.collect { xmlLvl -> 
        'Statment'{ 
                'dbTableName'('action':'INSERT'){
                    'table' 'Table'
                    'access'{
                            'ID' xmlLvl.@ID[0].text()
                            'ID2' xmlLvl.YOUR XML FIELDS OR ATTRIBUTES @ IN SEGUENCY.text()
                            'ID3' xmlLvl.Code.text()
                        }
                    }
                }
            }
        }
    message.setBody(writer.toString())
    return message
}

As mentioned above, the JDBC Adapter support only – INSERT, UPDATE and delete, so how is the result of this groovy:

<XmlSQL>
	<Statment>
		<dbTableName action="INSERT">
			<table>YOUR TABLE</table>
			<access>
				<ID>1</ID>
				<ID2>2</ID2>
				<ID3>3</ID3>
			</access>
			<access>
				<ID>4</ID>
				<ID2>5</ID2>
				<ID3>6</ID3>
			</access>
			<access>
				<ID>7</ID>
				<ID2>8</ID2>
				<ID3>9</ID3>
			</access>
		</dbTableName>
	</Statment>
</XmlSQL>

I believe is fast development delivery with groovy as you can see.

Multi-line or multi dimensional <access>, what this means ?

			<access>
				<ID>1</ID>
				<ID2>2</ID2>
				<ID3>3</ID3>
			</access>
			<access>
				<ID>4</ID>
				<ID2>5</ID2>
				<ID3>6</ID3>
			</access>
			<access>
				<ID>7</ID>
				<ID2>8</ID2>
				<ID3>9</ID3>
			</access>

That you should use BATCH Operation mode in the adapter JDBC to insert all register all in once, this message for example contains 3 register in the table.

In case that one single record in the SQL XML with Batch Mode selected, no issues..

There are two modes – ATOMIC and Non-ATOMIC, I really could not see differences with Microsoft SQL Serves with both actions, just in Non-Atomic the register in the table comes in the order like

Important: INSERT ACTION after the multicast sequencial – Batch Mode ON, for the stored procedure OFF.

Sample:

  • Call the order to save: 1 2 3 4
  • JDBC register save: 4 3 2 1

 

In case of Stored Procedure, you should unmark the Batch Mode sample below:

<root>
	<StatementName>
		<storedProcedureName action="EXECUTE">
			<table>${property.SPName}</table>
		</storedProcedureName>
	</StatementName>
</root>

Multicast Seguencial:

The logic behind the to simulate the INSERT_UPDATE with Stored Procedure behind.

Insert Table branch will send the XML sample above result of Groovy with action = INSERT and Stored procedure will to the rest in sequence.

Reminder, you are going to use SAP PI runtime so the Data Base Source in SAP CPI IFlow, before the deployment should be register in Cloud Integration Content Management tab – JDBC DATA STORE, not only that, remind any minor change in the Iflow the package should be redeploy.

So if you create as SCNJDBC external parameter in SAP CPI, you should create the same name as name of DATA Sources details.

Monitor:

 

Postman test solution:

Post man test, I can’t provide details so the images contains some editions as the columns in the data base to simulate the same result from the XML above:

Look the records 0001,0002,0003

SQL Check statement:

Select * from Table – Result 0 – Don’t do it at production home 😀

Empty Table:

Send payload to INSERT first Table resul:

Result after the stored procedure as a diagram what is going on behind the scene:

I hope that you once again enjoy the read and understand more about SAP CPI with JDBC – Batch Operation and Stored Procedure when and how to use it and why.

Enjoy your weekend.

Kind regards,

Viana.

 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.