Hi Folks,
INTRODUCTION:
In this blog post, I will explain how you can implement client-side pagination for an OData V4 API in SAP CPI (Cloud Platform Integration). Pagination is essential when dealing with large datasets, as it helps control the amount of data transferred and processed in each request.
In this scenario, I have used the Product Master OData V4 API to demonstrate client-side pagination.
Why Client-Side Pagination?
While server-side pagination works well for smaller datasets, it can become inefficient when the total number of records is large and fetching data page by page may consume more time and delaying overall integration performance.
To overcome this latency, I implemented client-side pagination using the OData query options $top and $skip, allowing SAP CPI to control the number of records fetched and their offset.
Let’s start implementing in SAP CPI:
Create in IFLOW in SAP CI Tenant as below:
Main Integration I-Flow
1: Use a Timer:
For the purpose of this blog, a Timer is used to trigger the iFlow.
2: Create Exchange Properties in Content Modifier:
Below Properties are created:
top = 5000 → Number of records to fetch per page (max value can be 5000 per page)skip = 0 → Initial offset for fetching records
3: Looping Process Call
A Looping Process Call will repeatedly call the LIP (Local Integration Process).
The loop condition is: ${property.emptyPayload} = ‘false’
Local Integration Process:
i : OData V4 adapter with Request-Reply
Below query Parameters are used:
$select=Product,CreationDate,LastChangeDate&$top=${property.top}&$skip=${property.skip}
$select → to retrieve specific field$top → Number of records per call$skip → Offset for fetching records
This ensures only the required fields are fetched in each batch.
ii: Groovy Script to Handle Pagination
Groovy script is used to manage the pagination logic. Its purpose is to:
Check if the payload is empty:If the API returns no records, it sets emptyPayload = true to stop the loop.Otherwise, it keeps emptyPayload = false to continue fetching the next batch.Increment the skip property:Retrieves the current skip valueAdds the page size (e.g., 5000) to it for the next iteration.
Remove First and Last Line: That carries the root node tags.
Groovy Script:
import com.sap.it.script.v2.api.Message
import java.io.Reader
Message processData(Message message) {
Reader reader = message.getBody(Reader)
String body = reader.text
def lines = body.readLines()
if (lines.size() > 0) lines.remove(0)
if (lines.size() > 0) lines.remove(lines.size() – 1)
body = lines.join(“n”)
message.setBody(body)
def skipValue = message.getProperty(“skip”) as String
int skip = skipValue ? skipValue.toInteger() : 0
skip += 5000
message.setProperty(“skip”, skip)
message.setProperty(“emptyPayload”, body.trim() ? “false” : “true”)
return message
}
Below are the screenshots to depict how the above groovy will execute:
CamelLoopIndex is increasing as per available no of pages to check if data is available in each page. Basis on data availability a custom property- emptyPayload set as true or false.
Now you can see the increment in skip value and top remain unchanged.
iii: Append Payload in Each Iteration
Content Modifier is used to gather the payload for each iteration. Below Exchange property is created:
storepayload = ${property.storepayload}${in.body}
In Message Body of Content Modifier:
${property.storepayload}
Step i, ii, iii in LIP will execute in loop until condition ${property.emptyPayload} = ‘false’ is met.
Local Integration Process will stop calling when condition is not met in looping process call, The loop will end here.
(Back to main integration process)
4: Add root node.
Content Modifier wraps the gathered payload under a single root node:
Voilà! The process ends here!!!!!
Conclusion:
Client-side pagination provides full control over page size by allowing the use of $top and $skip as needed, whereas server-side pagination can result in unpredictable page sizes.
I would recommend using OData Adapter for OData v2/v4 APIs over HTTP adapter because for any error, reason can be traced throw OData response body/headers that provides more realistic understanding of reason of failure.
I hope this blog post proves useful for implementing client-side pagination for an OData V4 API.
Thanks for reading!
Hi Folks,INTRODUCTION:In this blog post, I will explain how you can implement client-side pagination for an OData V4 API in SAP CPI (Cloud Platform Integration). Pagination is essential when dealing with large datasets, as it helps control the amount of data transferred and processed in each request.In this scenario, I have used the Product Master OData V4 API to demonstrate client-side pagination.Why Client-Side Pagination?While server-side pagination works well for smaller datasets, it can become inefficient when the total number of records is large and fetching data page by page may consume more time and delaying overall integration performance.To overcome this latency, I implemented client-side pagination using the OData query options $top and $skip, allowing SAP CPI to control the number of records fetched and their offset.Let’s start implementing in SAP CPI:Create in IFLOW in SAP CI Tenant as below: Main Integration I-Flow1: Use a Timer:For the purpose of this blog, a Timer is used to trigger the iFlow.2: Create Exchange Properties in Content Modifier:Below Properties are created:top = 5000 → Number of records to fetch per page (max value can be 5000 per page)skip = 0 → Initial offset for fetching records3: Looping Process CallA Looping Process Call will repeatedly call the LIP (Local Integration Process).The loop condition is: ${property.emptyPayload} = ‘false’Local Integration Process:i : OData V4 adapter with Request-ReplyBelow query Parameters are used:$select=Product,CreationDate,LastChangeDate&$top=${property.top}&$skip=${property.skip}$select → to retrieve specific field$top → Number of records per call$skip → Offset for fetching recordsThis ensures only the required fields are fetched in each batch.ii: Groovy Script to Handle PaginationGroovy script is used to manage the pagination logic. Its purpose is to:Check if the payload is empty:If the API returns no records, it sets emptyPayload = true to stop the loop.Otherwise, it keeps emptyPayload = false to continue fetching the next batch.Increment the skip property:Retrieves the current skip valueAdds the page size (e.g., 5000) to it for the next iteration.Remove First and Last Line: That carries the root node tags.Groovy Script:import com.sap.it.script.v2.api.Message
import java.io.Reader
Message processData(Message message) {
Reader reader = message.getBody(Reader)
String body = reader.text
def lines = body.readLines()
if (lines.size() > 0) lines.remove(0)
if (lines.size() > 0) lines.remove(lines.size() – 1)
body = lines.join(“n”)
message.setBody(body)
def skipValue = message.getProperty(“skip”) as String
int skip = skipValue ? skipValue.toInteger() : 0
skip += 5000
message.setProperty(“skip”, skip)
message.setProperty(“emptyPayload”, body.trim() ? “false” : “true”)
return message
} Below are the screenshots to depict how the above groovy will execute:CamelLoopIndex is increasing as per available no of pages to check if data is available in each page. Basis on data availability a custom property- emptyPayload set as true or false. Now you can see the increment in skip value and top remain unchanged.iii: Append Payload in Each Iteration Content Modifier is used to gather the payload for each iteration. Below Exchange property is created:storepayload = ${property.storepayload}${in.body}In Message Body of Content Modifier:${property.storepayload}Step i, ii, iii in LIP will execute in loop until condition ${property.emptyPayload} = ‘false’ is met.Local Integration Process will stop calling when condition is not met in looping process call, The loop will end here.(Back to main integration process)4: Add root node.Content Modifier wraps the gathered payload under a single root node:Voilà! The process ends here!!!!!Conclusion: Client-side pagination provides full control over page size by allowing the use of $top and $skip as needed, whereas server-side pagination can result in unpredictable page sizes.I would recommend using OData Adapter for OData v2/v4 APIs over HTTP adapter because for any error, reason can be traced throw OData response body/headers that provides more realistic understanding of reason of failure. I hope this blog post proves useful for implementing client-side pagination for an OData V4 API.Thanks for reading! Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
