Handle dynamic paging for OData Services using Looping Process call.

Introduction:

This scenario was a result of a query raised by one of our community colleagues regarding handling paging dynamically, so I thought of putting it into a short blog post.

Business Requirement:

Customer wants to make a generic API design which could cater to any one of the below requirements:

• Dynamically fetch records from OData service/entity.
• Setting Dynamic call back URL.
• Setting  the page-size dynamically.

Scenario:

I am going to fetch customer details from a available Northwind ODATA service.

In order to achieve this I am fetching the pagesize, Serivce URL/Query and Push back URL dynamically from the request payload.

I am using below tools & services:

• Cloud Platform Integration
• Postman or any ThirdParty-Client tool to push the request.
• OData service{ Northwind services }
• Webhook or any service which could receive data.

Create Integration flow in SAP Cloud Platform Integration:

1	Content Modifier	Any externalized parameters based on the requirement.
2	Script-1 Parse the JSON request and set the properties with request parameters posted.	def body = message.getBody(java.lang.String) def slurper = new JsonSlurper().parseText(body) def addURL = slurper.RequestODATAURL def topVal = slurper.DynamicPageSize def pushBackURL = slurper.CallBackUrl def queryFilter = slurper.QueryFilter message.setProperty("queryFilter",queryFilter) message.setProperty("addURL",addURL) message.setProperty("topVal",topVal) message.setProperty("pushBackURL",pushBackURL) message.setBody("") return message;
3	Request-reply call To Fetch the total count of the records from OData Entity. This service URL would be formed based on the request sent from the source system.	https://services.odata.org/Northwind/Northwind.svc/Customers/$count
4	Script-2 Set the total count variable.	String totalCount = message.getBody(java.lang.String); message.setProperty("totalCount",totalCount) message.setBody("") return message;
5	Looping Process call Make call to the local integration process to handle paging and further processing
6	Request Reply HTTP adapter Fetch data from ODATA service call.
7	Script-3 This script contains the logic to set top & skip and set the exit condition from the Looping process call. This is one of the ways to design the logic but there could be different way’s apply the logic.	def map = message.getProperties(); def exitFlag = "false" def topVal = map.get("topVal") def skipVal = map.get("skipVal") def totalCount = map.get("totalCount") def tempSkipVal = skipVal.toInteger() + topVal.toInteger() if(totalCount.toInteger()/tempSkipVal < 1) exitFlag = "true" skipVal = tempSkipVal.toString() message.setProperty("exitFlag",exitFlag); message.setProperty("topVal",topVal); message.setProperty("skipVal", skipVal); return message;
8	Request Reply- HTTP adapter Send response to PushBack URL
9	Content Modifier Set the Message to be Posted

OData Service

• { Northwind service }

Webhook

• For setting up of web-hook, refer to the blog post from r_herrmann [ Use Requestcatcher]

Testing:

Using Postman I have posted the request as shown in the sample request.

Sample Request:

{
"RequestODATAURL" : "https://services.odata.org/Northwind/Northwind.svc/Customers",
"DynamicPageSize" : "100",
"QueryFilter" : "",
"CallBackUrl" : "https://dynamicpaging.requestcatcher.com/test"
}

I have received below response on the web hook.

Since the page size was set to 50 and the total no of records were 91 so I had 2 post responses to the call back URL as shown above in the request catcher screen below.

If I change the page size to 100 I will get only one post response.

Sample Response:

Sample Response fetched & formatted.

Conclusion:

So I have achieved three things using this use case:

1. I was able to set the page-size dynamically.[«DynamicPageSize» : «100» ] I could change the page size in request payload and could notice that there is a difference in output.
2. I could change the resource name [ Northwind.svc/Customers ]  to [ Northwind.svc/Orders ] or even I can change the API URL but have to handle the authentication internally.
3. I could change the query parameters changing the input segment [QueryFilter ]
4. At last could change the Call Back/ PushBack URL based on the requirement [ «CallBackUrl» : «https://dynamicpaging.requestcatcher.com/test« ]

References:

{ SAP CPI Looping Process }

https://community.sap.com/t5/technology-blogs-by-sap/handle-dynamic-paging-for-odata-services-using-looping-process-call/ba-p/13463784