Skip to Content
Technical Articles

OData Service in SAP Cloud Platform Integration for Beginners

This blog post covers how to expose an OData Service in SAP Cloud Platform Integration with CRUDQ operations.

Introduction

In SAP Cloud Platform Integration, You can develop OData services that expose existing data sources, such as SOAP, OData, ODC and REST as OData endpoints. In this blog post, I will be exposing an existing OData Service with some modification in SAP Cloud Platform Integration.
Official Documentation

Prerequisite

  1. You have to know OData protocol and how to use an OData Service for Create, Read, Update, Delete operations. Help
  2. You have to know how to model Integration Flows in CPI and know how to use Message mappings.
  3. You have to know the difference between OData Service project and Integration Flow with OData sender.
  4. How to use Postman Rest client to perform HTTP calls.

What this blog doesn’t cover

  1. How to create the request payload for Create, update and $batch operations.

Help with Olingo

CPI uses Olingo library to serialise/deserialize the request-response and URI.

  1. Olingo UriInfo stores the request URI information.
  2. This git contains java code to getUriInfo object with olingo library.

OData Receiver Service

OData Service that I will be using is the reference service from https://www.odata.org/odata-services/

https://services.odata.org/V2/(S(ODataSenderCPIDemo))/OData/OData.svc/

TIP: In the above service URL, (ODataSenderCPIDemo) represents a unique session for the manipulation of records. i.e., Entities created, updated, deleted will reflect only in that session. You can have your own session with any string within S() for your testing.

X-CSRF-Token support

OData service exposed from CPI is CSRF enabled. CPI expects it’s clients to send a valid CSRF token in the HTTP header X-CSRF-Token with every request, except for GET
If you don’t, you will receive an error response with the status code 403

  1. Fetch a CSRF-token
    Send a GET-request to your service root with the appropriate Authorization header and the X-CSRF-Token header with value Fetch.
  2. Send the CSRF-Token with every request
    Now, you can send your payload-requests with the header  X-CSRF-Token set to the value received in the response header of step 1. If your session or token expires, you will get a 403-error again. Just repeat the steps above.

OData Sender Behavior

  1. OData sender converts the incoming OData Request payload (atom+xml / json) to simple XML.
  2. For OData Operations which has response body (create, read), the final step in the IFlow has to be a simple XML which represents the Entities defined in OData sender Edmx.
  3. The URI of the OData Sender can have OData query parametersa and other custom parameters. The URI parameters have to be retrieved using Olingo UriInfo which is available in message header “UriInfo”.

Contents

  1. Create an OData Sender Artifact
  2. Deploying an OData Service
  3. Invoking an OData Service
  4. Editing an OData Model
  5. Read
    5.1 Read with Navigation
  6. Query
  7. Create
    7.1 Deep Insert
  8. Update
  9. Delete
  10. FunctionImport
  11. Set Custom Error Code and Response Body for Error

 

1. Create an OData Sender Artifact

  1. Create an OData Sender project by following the documentation up to step 7.

  2. Download the metadata from existing OData service from postman
    https://services.odata.org/V2/(S(ODataSenderCPIDemo))/OData/OData.svc/$metadata
    TIP: Use your own session
  3. Open the created artifact in Edit mode, choose Import Model Wizard
  4. Select Data Source Type as ODATA, Browse the Edmx file saved in step 2
  5. Choose the entities that you want to expose in the service from SAP Cloud Platform Integration. I have chosen Products and Suppliers (without Address fieds)
  6. Choose the fields that will be key in each entity. I have chosen ID and ReleaseDate to be the Key of Products.
  7. Click Finish.
  8. Now the OData Service is ready with no operations configured. In order to deploy an OData Service, at least one operation has to be configured. Click on bind link under Actions.
  9. Choose the Entity and the End Point to your OData Receiver Service.
  10. An Iflow template for OData Read operation will be generated. Now the OData Service is ready for Deploy.

2. Deploying an OData Service

  1. Deploy the service by following the documentation.
  2. On the Endpoints tab, you can view the endpoint URI. This endpoint URI is the OData Service Root URI.

3. Invoking an OData Service

  1. Copy the endpoint URI by choosing the copy icon.
  2. From Postman,  fire the request for the endpoint with the user having the role ESBMessaging.send
  3. The OData service has 2 entities ProductSet and SupplierSet. Copy the EDMX URL and trigger it from Postman.
    The OData Receiver Service had Products and Suppliers, which is exposed by the CPI OData Service as ProductSet and SupplierSet. Similarly, you can see that the ProductSet has 2 Key property that was configured previously.

4. Editing an OData Model

  1. Follow the steps from the documentation.
  2. Rename the ID of ProductSet to ProductID and ID of SupplierSet to SupplierID.
  3. Save and deploy. $metadata call will reflect the changes.

The following operations are supported when developing an OData service in the SAP Cloud Platform Integration. Let’s configure and test each operation one by one.

5. Read

  1. Configure the Read operation for ProductSet. In Configure OData Data Source, make sure to give the same endpoint for all operations (with the same session)
  2. Navigate to IFlow Editor.
  3. A predefined IFlow will be created. The script step of the IFlow will have the template to add $top and $skip to add to message header “odataURI”.
  4. Modify the groovy script to set the key parameter as message header with key “Key_<propertyName>”
    The URI parameters has to be retrieved using Olingo UriInfo which is available in message header “UriInfo”.

    This git contains java code to get UriInfo object. Since you cannot debug a script, you can use the java class to understand the UriInfo and derive the key property values from request URI.

    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 keyPredList = uriInfo.getKeyPredicates();				
    		def k=0;
    		for(item in keyPredList)
    		{			
    			message.setHeader("Key_"+item.getProperty().getName(),item.getLiteral());
    		}
    	return message;
    }​
  5. The Receiver OData is configured to get the key value from the header set from the script.
  6. The Response from OData receiver has to be mapped as per the entities in sender Edmx.
  7. Perform the Read request from postman https://<serviceRoot>/ProductSet(ProductID=1,ReleaseDate=datetime’1995-10-01T00:00:00′)

5.1 Read with Navigation

  1. Edit the OData Model such that ProductsSet has navigation to SupplierSet and vice versa.
    <edmx:Edmx
        xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"
        xmlns:sap="http://www.sap.com/Protocols/SAPData" Version="1.0">
        <edmx:DataServices
            xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:DataServiceVersion="2.0">
            <Schema
                xmlns="http://schemas.microsoft.com/ado/2008/09/edm" Namespace="S1">
                <EntityContainer Name="EC1" m:IsDefaultEntityContainer="true">
                    ....
                    <AssociationSet Name="Products_Supplier_Suppliers" Association="S1.Product_Supplier_Supplier_Products">
                        <End Role="Product_Supplier" EntitySet="ProductSet"/>
                        <End Role="Supplier_Products" EntitySet="SupplierSet"/>
                    </AssociationSet>
                </EntityContainer>
                <EntityType Name="Product">
                    .....
                    <NavigationProperty Name="Supplier" Relationship="S1.Product_Supplier_Supplier_Products" FromRole="Product_Supplier" ToRole="Supplier_Products"/>
                </EntityType>
                <EntityType Name="Supplier">
                    .....
                    <NavigationProperty Name="Products" Relationship="S1.Product_Supplier_Supplier_Products" FromRole="Supplier_Products" ToRole="Product_Supplier"/>
                </EntityType>
                <Association Name="Product_Supplier_Supplier_Products">
                    <End Role="Product_Supplier" Type="S1.Product" Multiplicity="*"/>
                    <End Role="Supplier_Products" Type="S1.Supplier" Multiplicity="0..1"/>
                </Association>
            </Schema>
        </edmx:DataServices>
    </edmx:Edmx>​
  2. The navigation ProductsSet to Supplier is of cardinality * to 1. Hence the request ProductSet(ProductID=1,ReleaseDate=datetime’1995-10-01T00:00:00′)/Supplier will trigger the Read operation of SupplierSet. Configure Read for SupplierSet with the initial script containing below 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 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
            }
    	return message;
    }

    TIP : In the script above, log.logErrors are used to print the values of my variable in logs. These logs appear in System Log file of type CP default trace as below.
    …..[Gateway][TECHNICAL][TechnicalError]:Entity Name::CategorySet|

  3. Do the required response mapping. The navigation query from postman https://<serviceRoot>/ProductSet(ProductID=1,ReleaseDate=datetime’1995-10-01T00:00:00′)/Supplier will return Supplier of ID 0.

6. Query

  1. Add the initial script step to hold the query options Filter, top, skip, orderby, expand as below. Query options are retrieved using Olingo UriInfo which is available in message header “UriInfo”.
    $select doesn’t need to be handled in the script.

    This git contains java code to get UriInfo object. Since you cannot debug a script, you can use the java class to understand the UriInfo and derive each of the OData query options like $top $filter.

    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 odataURI = new StringBuilder();
    	def urlDelimiter = "&";
    	def urlConcat = "?";
    	def entityName = uriInfo.getTargetEntitySet().getName();
    	log.logErrors(LogMessage.TechnicalError, "Entity Name::"+entityName);
    	if (uriInfo.getTop() != null){
    		def top = uriInfo.getTop();
    		if(odataURI.size()!=0)
    			odataURI.append(urlDelimiter);
    		odataURI.append("\$top=").append(top);
    		log.logErrors(LogMessage.TechnicalError, "Top value:"+top);
    	}
    	if (uriInfo.getSkip() != null){
    		def skip = uriInfo.getSkip();
    		if(odataURI.size()!=0)
    			odataURI.append(urlDelimiter);
    		odataURI.append("\$skip=").append(skip);
    		log.logErrors(LogMessage.TechnicalError, "Skip value:"+skip);
    	}
    	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);
    	}
    	if(uriInfo.getOrderBy() != null){
    	    def orderBy = uriInfo.getOrderBy().getExpressionString();
    	    if(odataURI.size()!=0)
    			odataURI.append(urlDelimiter);
    		odataURI.append("\$orderby=").append(orderBy);
    	    log.logErrors(LogMessage.TechnicalError, "orderby value: "+orderBy);
    	}
    	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);
    	    }
    	}
    	log.logErrors(LogMessage.TechnicalError, "URI value:"+ odataURI.toString());
    	message.setHeader("odataEntity",entityName);
    	message.setHeader("odataURI",odataURI.toString());
    	return message;
    }
  2. The receiver OData has to be configured to get the system query option from the header ${header.odataURI}
  3. Map the response from receiver to Sender Edmx. For $expand scenario the expanded entity has to be mapped. The Response mapping template will generate source and target with only root entity.
    I have created the XSD for source and target containing the Supplier child and used that XSd for the mapping.
  4. From Postman, perform Query with various query options like $select, $top, $skip, $filter, $expand.

7. Create

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

  1. Configure create for ProductSet.
  2. Request from the Sender has to be mapped to the Receiver edmx.
  3. Response from the receiver has to be mapped to Sender Edmx.
  4. Perform Create operation from postman

7.1 Deep Insert

The Response of Deep Insert should have a response body with only the root Entity and not the navigation entities.

  1. In case of deep insert, the sender generates the nested structure of the deep insert payload and the same has to be handled in the iflow as per the use case.
  2. The request mapping with nested Supplier has to be done.
  3. The response of the deep insert should have only the root entity for the OData sender. Please note in below response mapping, only the root Entity ProductSet is mapped.
  4. The OData deepinsert request will be converted to XML by OData sender whcih has to be handled as per your business logic.
    I have enabled trace and triggered deepinsert and the equivalent XML is as below.
  5. Since the OData receiver is capable of handling the deep insert, I have just mapped the incoming payload to the receiver as shown in step 2.
  6. DeepInsert triggered from the postman.
  7. I verify that the Supplier with SupplierID 6543 is created by querying on the OData receiver URL configured.
    https://services.odata.org/V2/(S(ODataSenderCPIDemo))/OData/OData.svc/Suppliers(6543)

8. Update

  1. URI handling: Similar to Read operation, The ID has to be correctly passed to the receiver.
  2. Payload: The request payload has to be mapped just like Create.

9. Delete

  1. URI handling: similar to Read operation, The ID has to be correctly passed to the receiver.
  2. The is no need for request and response mapping.

10. FunctionImport

  1. Edit the Iflow model to have a FunctionImport defined. I have defined GetProductsGTRating which will fetch all the Products greater than the rating given in input.
  2. Configure the FunctionImport.
  3. Modify the initial script step to store the function import parameters into the message header.
    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;
    }​
  4. Model the Iflow to handle the defined function Import. The OData receiver is configured to get all the Products with rating greater than the header value ‘rating’.
  5. Do the required response mapping.
  6. Trigger the functionimport from the postman, <service root>/GetProductsGTRating?rating=2.
    The response will contain all the Products with rating greater than 2.

 

11. Set Custom Error Code and Response Body for Error

  1. Perform a Create operation on ProductSet without the Key Property in payload as below.
    {
    	"ReleaseDate": "/Date(694224000123)/",
    	"Rating": 4,
    	"Price": "2.5"
    }​

     

  2. Below is the response that will be seen.500 status code with default error.
  3. This response can be customized with custom response code and response message.
  4. Add an exception subprocess in create Iflow. Add a content modifier.
  5. Set the message header “CamelHttpResponseCode” to the status code you want to propagate to Sender.
  6. Save and deploy the artifact and trigger the create operation again. The status code will be 400 instead of 500.
  7. The Iflows resources has an XSD named Error. Set the message body with the error message as per the XSD.
  8. Trigger the create without key property. The response will be custom status code and message set in exception subprocess.

 

OData Sender related blogs

  1. Introduction to Creating OData Service in HANA Cloud Integration
  2. OData Service Project vs Integration Project when to use what in Cloud Platform Integration
  3. OData Service in $batch Mode
  4. https://blogs.sap.com/2016/06/10/deep-insert-suport-in-odata-provisioning-in-hci/
  5. https://blogs.sap.com/2017/06/05/cloud-integration-how-to-setup-secure-http-inbound-connection-with-client-certificates/
  6. https://blogs.sap.com/2017/07/17/odata-service-project-vs-integration-project-when-to-use-what-in-cloud-platform-integration/
  7. https://blogs.sap.com/2018/09/16/sap-cloud-platform-integration-odata-v2-conditional-update/
4 Comments
You must be Logged on to comment or reply to a post.
  • Hi Saranya, Very helpful article indeed.

    I was trying to do the same with Success Factors odata api. However while importing EDMX , it is giving errors.

    “Element ‘tagcollection’ is not valid at this position Namespace of attribute ‘label’ not found Namespace of attribute ‘creatable’ not found Namespace of attribute ‘updatable’ not found Namespace of attribute ‘sortable’ not found Namespace of attribute ‘filterable’ not found Namespace of attribute ‘visible’ not found”

    Can you please guide how to download edmx in case of SF APIs or if any modification is needed.

    Thanks

    • Hi Ashuthosh,
      SF OData Edmx is annotated with sap namespace for properties as below which is not understood by the OData Sender GUI.
      <Property Name=”addressLine1″ Type=”Edm.String” Nullable=”true” sap:required=”false” sap:creatable=”true” sap:updatable=”true” sap:upsertable=”true” sap:visible=”true” sap:sortable=”false” sap:filterable=”false” MaxLength=”255″ sap:field-control=”userPermissionsNav/addressLine1″ sap:label=”Address Line 1″/>

      However, instead of importing the EDMX, you could go to the Edmx Editor and paste the complete EDMX. This will show the lines with error and you could fix them one by one. I have copied the EDMX of user Entity from SF, and I have commented the line 17 to 20 to fix the validation errors.

  • Hi Saranya,

    Great piece of work.

    Can you comment if we can integrate SAP Gateway oData services(v2) in Cloud platform iFlow ? Also is it possible to reuse the same session from the cloud platform for all the oData api calls to the SAP backend ?

    Note: oData services in the backend are stateful.i.e., soft state enabled.

    Regards

    Prabha