[SAP CPI] – WORKING WITH ODATA API IN SAP CLOUD INTEGRATION

Hi guys, Today I want to discuss about ODATA API which one of projects in SAP Cloud Integration. In this article, I referenced blog to understand about difference Integration flow project vs ODATA API project, thanks so much Sabarish. And blog to know how to build ODATA API, thanks so much Baskaran.

First, we need to understand about difference between ODATA API and Integration Flow API and when should be use it

Scenario #1 : Sender system send ODATA adapter with format ATOM/JSON to Receiver system through CPI.

In this case, we use Integration Flow Project with sender is ODATA adapter. In CPI, we convert payload JSON to XML. After that send to receiver by any adapter which we want (Ex: HTTP, SFTP, FTP…)

Scenario #2 : Front end application use one ODATA service which exposed from backend with many data source (ODATA service, REST API…)

In this case, we use ODATA API Project to expose ODATA Service with many Entitysets and Operations.

Second, we will exploring some method of ODATA, example as select, filter, top… For sample, we will do one sample integration about ODATA.

Exploring ODATA with Northwind DB

In this example, I use two table which related together is Products and Suppliers.

I used POSTMAN to run some method with ODATA Northwind.

#1. Query data with select command

https://services.odata.org/V2/Northwind/Northwind.svc/Products?$select=ProductID,ProductName

#2. Query data with filter

https://services.odata.org/V2/Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

#3. Query data with TOP

https://services.odata.org/V2/Northwind/Northwind.svc/Products?$top=10

#4. Read data with Navigation

Because of One products will have one Suppliers so we need get information of that supplier

https://services.odata.org/V2/Northwind/Northwind.svc/Products(1)/Supplier

#5. Query data with $expand

https://services.odata.org/V2/Northwind/Northwind.svc/Products?$expand=Supplier&$select=ProductID,ProductName,Supplier/SupplierID,Supplier/CompanyName&$top=1

Working ODATA API with Northwind DB in SAP CPI

#1. Create ODATA API Artifacts and import table Product, Supplier

Go to POSTMAN, save meta data of ODATA service

• Import meta data of ODATA service above

• Check Product, Supplier

• Review and finish

#2. Configuration relationship of Product and Supplier

• Click on button ODATA model Editor at top right

• This will open editor and we config relationship between table Product and Supplier in here.

After this step, we click on Graphical model viewer, we will see relationship between this table

Next, we will config every method of this ODATA API in every Dataset

#3.Configuration ODATA API with GET(Query) for table ProductSet

In this method GET(query), we need to get all information of Product Set and information relevant of its as information of supplier of that product.

NOTE

For do this, we need create dummy ODATA receiver to get EDMX Schema Product-supplier

#3.1. Create dummy and get EDMX Schema of Product – Supplier

• On query method, click action -> Bind

• Double click on arrow from Request-Reply to Odata_Receiver
• Choose Tab Processing
• Click button Select

• Click outside of integration flow, go to Resource and download file XSD of above step

• Double this file to another for target mapping

NOTE

Remember open file for target and change name element accordingly

And all steps from here to end, we will use these file XSD for all mapping

#3.2. Configuration for GET ( Query)

• Click on Bind action of method query below Product Set

• Go to Integration Flow Editor

• Go to mapping and add 2 file XSD of above step to get schema of Product – Supplier.

This is flow of query

Main idea is :

• (1) will collect information from sender by library URI Info. Create dynamic value and add into header

• After that, value in header/property will be used in (2) to collect data from data source backend ( in this case it is ODATA NORTHWIND) by dynamic filter value. Ex : ${header.odataURI}
• Data of NORTHWIND will return for (3) as message source and mapped to message target. After that will be returned for client.

#3.2.1. Query with TOP keyword

• (1) : Script

In above groovy script, method logErrors(…) will write log message into CPI. We can view log at

After this step (1), header odataURI will have value : $top = <number top>

• (2) : Get data from backend with value header odataURI

• (3) : Mapping source which returned from backend above with target structure. Open mapping

Ok, after done we have to deploy and test from POSTMAN

#3.2.2. Query with FILTER keyword

• The same way above, we configure query data with filter keyword. In script we add code

if(uriInfo.getFilter() != null){
	    def filterValue = uriInfo.getFilter().getUriLiteral();
	    filterValue = filterValue.replace("ProductID","ID");  //The receiver has property names as ID and not ProductID
	    if(odataURI.size()!=0)
			odataURI.append(urlDelimiter);
		odataURI.append("\$filter=").append(filterValue);
	    log.logErrors(LogMessage.TechnicalError, "Filter value: "+filterValue);
	}

• Test on POSTMAN

#3.2.2. Query with $expand keyword

if(uriInfo.getExpand() != null){
	    def expandList = uriInfo.getExpand();
	    def expandValue;
	    log.logErrors(LogMessage.TechnicalError, "expandList size: "+expandList.size());
	    if(expandList.size()!=0){
			odataURI.append(urlDelimiter);
    		for(item in expandList){
    		    if(item.size() > 0){
    		        for(navSegments in item){
    			            expandValue = navSegments.getNavigationProperty().getName();  //TO DO : Multiple expand values to be handled
    		        }
    		    }
    		}
    		odataURI.append("\$expand=").append(expandValue);
	        log.logErrors(LogMessage.TechnicalError, "expand value: "+expandValue);
	    }
	}

• Test on POSTMAN : Get Supplier information base on Product. This case, payload in response will include information of Supplier which have Supplier ID equal with Supplier ID in Product

#3.2. Configuration for CREATE

The Response of Create should have a response body of the created entity with the key properties.

• Click action bind on method CREATE

• click on integration flow editor

• This mapping step, we will mapping data from Product set with fields mandatory as Product ID, Product Name, Discontinued to Product entity

• At ODATA backend, we use method Create (POST) with fields the same ( Product ID, Product Name, Discontinued)

• Payload returned will mapped with Product Set (Target message)

• Test on POSTMAN

Because of we use ODATA backend is NORTHWIND database, so It cannot create or delete or update. So we just reference steps to known.

As we know, Product and Supplier relationship together, so we expect that in payload of insert product, we have also payload of supplier. When create new Product, then supplier created also. Look like

#3.3. Configuration for READ

• With the same way above, we bind action with method READ

• Open script and add more code to get condition from sender

def keyPredList = uriInfo.getKeyPredicates();				
		def k=0;
		for(item in keyPredList)
		{			
			message.setHeader("Key_"+item.getProperty().getName(),item.getLiteral());
		}

• At ODATA backend we will user value of header which added in script above. This is Key_ProductID

• Test on POSTMAN

#3.3. Configuration for READ with Navigation

• As we known, product and suppler have relationship together. So sometime we want get information of supplier base on supplier id inside product.

• Add relationship in model editor

• Add bind for supplier

• Add code in script

	def keyPredList = uriInfo.getKeyPredicates();				
		def k=0;
		for(item in keyPredList)
		{
			log.logErrors(LogMessage.TechnicalError, (++k) + " Key Predicate value for property "+item.getProperty().getName()+" is: "+ item.getLiteral());
			message.setHeader("Key_"+item.getProperty().getName(),item.getLiteral());
		}	
		def targetEntityName = uriInfo.getTargetEntitySet().getName();
	def startEntityName = uriInfo.getStartEntitySet().getName();	
	//Handle Navigation request
	if(!targetEntityName.equals(startEntityName)){
	    log.logErrors(LogMessage.TechnicalError, "Navigation request from source "+ startEntityName + " to target " +targetEntityName);
	    //Do your own processing to decide which Supplier ID to retrive.
	    message.setHeader("SupplierID","0");
	}else{
            //Handle SupplierSet Read
        }

NOTE : In this step, we hard code Supplier ID=1 for example. In fact, we will get value of Supplier ID which relevant with Product ID

• Config backend ODATA for Supplier

• Mapping for target message

• Test on POSTMAN

The result is information of Supplier ID, because we get Supplier base on Product

You can download EDMX file in here

#3.4. Function Import

• Go to ODATA model editor

• Add more code for import function

<FunctionImport Name="GetProductGTRating" EntitySet="Products" ReturnType="Collection(S1.Product)"
                m:HttpMethos="GET">
                    <Parameter Name="rating" Type="Edm.Int32" Mode="In"/>
                </FunctionImport>

• We will have one custom function look like

• Config bind action

• Go to script add this code

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import org.apache.olingo.odata2.api.uri.UriInfo;
import com.sap.gateway.ip.core.customdev.logging.*; 
def Message processData(Message message) {
	def uriInfo = message.getHeaders().get("UriInfo");	
	def funcImpParams = uriInfo.getFunctionImportParameters();
	if(funcImpParams  != null && !funcImpParams.isEmpty()){
	    log.logErrors(LogMessage.TechnicalError, "FunctionImport"+funcImpParams);
	    def k=0;
		for(item in funcImpParams)
		{
			log.logErrors(LogMessage.TechnicalError, "Functionimport Param "+(++k)+" : "+ item.getKey()+" = "+item.getValue().getLiteral());
			message.setHeader(item.getKey(),item.getValue().getLiteral());
		}
	}
	return message;
}

• Configuration ODATA BACKEND receiver

• With function import, we no need use response mapping. So we can delete it
• Now we will test on POSTMAN. Open POSTMAN run endpoint : https://<host>/GetproductGTRating?rating=2

Summary

In this article I shared step by step How to working with project ODATA API. Base on requirement of business we can choose project ODATA API or integration flow. It’s very interested when working with ODATA API, thank for your reading and if any question kindly leave your comment in below this.

Joseph.