How to deal with OData services that return only 100 entities?

In order to shield your OData services that are running on top of the SAP Cloud Platform ABAP Environment from malicious clients the ABAP RESTful Programming Model enforces server side paging as defined by the OData protocol if the client does not use appropriate client side paging.

Appropriate means that $top must be used and that the value for $top should not be larger than 5000. If it is larger than 5000, say 10500, the caller will nevertheless only receive 5000 entries because of the hard coded server side paging enforced by the framework. The client will get in addition a next link with a $skiptoken at the end of the response.

If $top is not used the frameworks limits the response to 100 entities and will add a next link to retrieve the remaining ones.

Enforcing server side paging should not be a problem for any client that supports the OData protocol because OData clients “MUST treat the URL of the next link as opaque” as specified in the OData protocol.

But it can come to a surprise for developers that are using client libraries other than SAP UI5. While SAPUI5 table controls automatically use appropriate client side query options ($top and $skip) developers that use non-SAPUI5 client libraries such as .NET might wonder why the service only returns 100 entities no matter what query options they use. These developers simply have to implement the support for client side paging and have to use $top and $skip.

So this article is only a must read for you if you intend to consume OData services with a 3rd party client library or if you have developed a custom UI5 control that does not support automatic handling of an appropriate client side paging as offered by table controls in SAPUI5.

Server side paging – enforced

Suppose you have registered for the trial version of SAP Cloud Platform Environment and you have created a service binding Z_SKIPTOKEN_TEST_### for the he service definition /DMO/TRAVEL_U.

This is possible if you create it in your own package ZTRAVEL_APP_### and if you replace ### with a unique combination of three digits or three characters (here I chose AFI).

After you have activated the local service endpoint.

you can click on the link Service URL instead of starting the SAP Fiori Elements preview. You will now be able to retrieve booking data using the following URL in the browser.

(where ### denotes the unique combination of characters/numbers)

https://<server>:<port>/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$format=json

As a result you will get a response as follows:

...
   + to_Travel: {...}
 }
 -
 {
   + __metadata: {...}
     TravelID: "17"
     BookingID: "2"
     BookingDate: "/Date(1562112000000)/"
     CustomerID: "581"
     AirlineID: "UA"
     AirlineID_Text: "United Airlines, Inc."
     ConnectionID: "1537"
     FlightDate: "/Date(1563580800000)/"
     FlightPrice: "508.00"
     CurrencyCode: "USD"
     LastChangedAt: "/Date(1562581012000+0000)/"
   + to_BookSupplement: {...}
   + to_Carrier: {...}
   + to_Connection: {...}
   + to_Customer: {...}
   + to_Travel: {...}
  }
 ]
 __next: https://<hostname>:<port>/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$format=json&$skiptoken=100
 }
}

Please note the last entry of the feed:

__next: https://<hostname>:<port>/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$format=json&$skiptoken=100

As you can see the ABAP runtime only returns 100 objects and adds a link to the response the client can follow to get the remaining entries.

Since the first 100 entries have already been delivered by the server the next link contains the query option $skiptoken=100.

Limit the response by client side paging

If the client would use client side paging by adding for example the query option $top=200 the server would not enforce server side paging and the response would hence not contain a link with a skiptoken.

Request:

/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=200&$format=json

Response:

...
   + to_Travel: {...}
 }
 -
 {
   + __metadata: {...}
     TravelID: "35"
     BookingID: "4"
     BookingDate: "/Date(1562457600000)/"
     CustomerID: "484"
     AirlineID: "AA"
     AirlineID_Text: "American Airlines Inc."
     ConnectionID: "322"
     FlightDate: "/Date(1563753600000)/"
     FlightPrice: "630.00"
     CurrencyCode: "USD"
     LastChangedAt: "/Date(1562292493000+0000)/"
   + to_BookSupplement: {...}
   + to_Carrier: {...}
   + to_Connection: {...}
   + to_Customer: {...}
   + to_Travel: {...}
  }
 ]
 }
}

Server side paging enforced – part 2

But what would happen if our malicious client would use client side paging but would try to retrieve a large response by using a huge value for $top, say $top=10500?

/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=10500&$format=json

In this case the OData framework again enforces server side paging and will only return 5000 entities:

...
  + to_Travel: {...}
 }
 -
 {
   + __metadata: {...}
     TravelID: "2066"
     BookingID: "2"
     BookingDate: "/Date(1587859200000)/"
     CustomerID: "250"
     AirlineID: "SQ"
     AirlineID_Text: "Singapore Airlines Limited"
     ConnectionID: "12"
     FlightDate: "/Date(1589587200000)/"
     FlightPrice: "4344.00"
     CurrencyCode: "SGD"
     LastChangedAt: "/Date(1568726871000+0000)/"
   + to_BookSupplement: {...}
   + to_Carrier: {...}
   + to_Connection: {...}
   + to_Customer: {...}
   + to_Travel: {...}
  }
 ]
 __next: https://<hostname>:<port>/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=5500&$format=json&sap-ds-debug=true&$skiptoken=5000
 }
}

and the last entry of the response would contain the following next link:

__next: https://<hostname>:<port>/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=5500&$format=json&$skiptoken=5000

As a result the client must send two additional requests to get all 10500 entities, namely

/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=5500&$skiptoken=5000&$format=json

and finally

/sap/opu/odata/sap/Z_SKIPTOKEN_TEST_###/Booking?sap-client=100&$top=500&$format=json

This last response would again contain no next link.

Caution – implementation of custom entities

If you implement a custom entity where the data is retrieved via your own ABAP code you will encounter the problem that the exception CX_RAP_QUERY_NOT_FULLY_IMPLMTD will be raised if your implementation does not handle the server side paging.

So your coding must call the get_paging method evaluate the result of this method and return the results considering the page size and offset as requested by the client.

An appropriate implementation of the handling of get_paging can be found in my following blog

How to implement a custom entity in the ABAP RESTful Programming Model using remote function modules