[SAP CPI] – WORKING WITH JDBC ADAPTER RECEIVER ON SAP CPI AND SQL SERVER

Hi guys, in previous blog I talked step by step configuration JDBC adapter in SAP CPI connect to SQL server on premise. In this article, I will share step by step How to working with SQL from SAP CPI and JDBC Adapter Receiver.

For example, we have table ORDERS in SQL

Because of working with JDBC driver, so we need one structure accordingly with SQL command as below

  • INSERT Statement
<StatementName>
	<dbTableName action="INSERT">
		<table>realDbTableName</table>
		<access>
			<col1>val1</col1>
			<col2>val2</col2>
		</access>
		<access>
			<col1>val11</col1>
		</access>
	</dbTableName>
</StatementName>
  • UPDATE
<StatementName>
	<dbTableName action="UPDATE">
		<table>realDbTableName</table>
		<access>
			<col1>val1</col1>
			<col2>val2new</col2>
		</access>
		<key1>
			<col2>val2old</col2>
			<col4>val4</col4>
		</key1>
		<key2>
			<col2>val2old2</col2>
		</key2>
	</dbTableName>
</StatementName>
  • UPDATE & INSERT
<StatementName>
	<dbTableName action="UPDATE_INSERT">
		<table>realDbTableName</table>
		<access>
			<col1>val1</col1>
			<col2>val2new</col2>
		</access>
		<key1>
			<col2>val2old</col2>
			<col4>val4</col4>
		</key1>
		<key2>
			<col2>val2old2</col2>
		</key2>
	</dbTableName>
</StatementName>
  • DELETE
<StatementName>
	<dbTableName action="UPDATE_INSERT">
		<table>realDbTableName</table>
		<access>
			<col1>val1</col1>
			<col2>val2new</col2>
		</access>
		<key1>
			<col2>val2old</col2>
			<col4>val4</col4>
		</key1>
		<key2>
			<col2>val2old2</col2>
		</key2>
	</dbTableName>
</StatementName>
  • SELECT
<StatementName>
	<dbTableName action="SELECT">
		<table>realDbTableName</table>
		<access>
			<col1/>
			<col2/>
			<col3/>
		</access>
		<key1>
			<col2>val2old</col2>
			<col4>val4</col4>
		</key1>
		<key2>
			<col2>val2old2</col2>
		</key2>
	</dbTableName>
</StatementName>
  • EXECUTE
<StatementName>
	<storedProcedureName action=" EXECUTE">
		<table>realStoredProcedureeName</table>
		<param1 [isInput="true"] [isOutput=true] type=SQLDatatype>val1</param1>
	</storedProcedureName >
</StatementName>

1. Call Store Procedure SQL by JDBC adapter receiver in SAP CPI
  • Write logic for Store Procedure to insert data to table ORDERS
  • Create structure XSD for Source message
  • Create structure XSD for destination message. Base on structure XML of SQL Command for EXUCUTE, we create XSD
  • Create integration Flow
  1. HTTPS : Client will send data XML from POSTMAN
  2. Message Mapping : In this step, we have to map source field to parameter in Store procedure. Example in store procedure we have 4 parameters, we can hardcode constant or get from source fields.

3. After mapping, we have data with many segment SQL Statement. So we have to use General Splitter to LOOP every statement and insert to SQL by JDBC

4. General Splitter with XML path : //StatementName

  • Test from POSTMAN
1. Call TSQL INSERT by JDBC adapter receiver in SAP CPI
  • Source message (XSD)
  • Destination message (XSD)
  • Message Mapping
  • General Splitter
  • Test POSTMAN

Summary

In this article, I shared step by step how to working with JDBC adapter receiver. I also take 2 script JDBC one for call Store Procedure, and one for TSQL insert command.

Next article, I will share fulfilment flow. Example S4 outbound master data, and from this data xml, we will insert to SQL server by JDBC adapter receiver. Thanks for your reading and any question, kindly leave your comment on below this.

Thanks.

Joseph.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.