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

[Andre_Fischer]

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 also 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...

 }

}

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...

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&$f...

 }

}

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&$f...

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

[former_member754047]

Thank you for your post Andre!

Support the SADL Framework $skiptoken automatically e.g. OData RDS / CDS-View annotation @OData.public: true? This means, no further implementation in DPC_EXT necessary?

 

Best Regards

 

Felix

[Andre_Fischer]

In the SAP BTP ABAP Environment there is not annotation @odata.publish : true nor is there a reference data source approach.  OData service development is only available via RAP.

[former_member754047]

Thank you Andre. In which SAP releases is this feature supported? NW 7.5?

[dominik_ee]

Hi Felix,

the $skiptoken query option is implemented in the gateway layer and transformed into $skip and $top query options for SADL. So $skiptoken should be independent from the exposure type with regards to SADL (I'm not 100% sure if gateway has some restrictions).

Best regards
Dominik

[emilian_felman1]

Thank you for this blog!

I have a problem with my virtual element where I need the full set to make some calculations. Now I only have blocks of 5000 records, so the result will be 'reset' for each block. Is there any way I can bypass the default paging  'max 5000 rule'? (other than not using the ABAP Restful Programming Model)?

[ravi_rajput]

Great information on server side paging, Thanks Andre.

[davidgbarbero]

How do you manage that $top parameter in a UI5 control? SetSizeLimit does not work with a RAP model (V2 at least). Is it possible to change the top limit server side?

[Andre_Fischer]

Since I do not know the answer how to set parameters in SAPUI5 I would like to ask you to post this question in the Q&A area of the community.

$top and $skip are parameters for client side paging and thus not set on server side.

[davidgbarbero]

But is there any parameter I can set server side? Or any kind of solution to increase that default limit (100) server side?

[stefan_engelhardt]

You need to check the UI5 documentation for the specific control. For lists and derived controls you can find a description here: https://ui5.sap.com/#/topic/9164ba7047b74a25a19baf9c5bb986ae. Returning a higher amount of rows than requested in $top could lead to unwanted side effects in the UI so this shouldn't be done. The sap.ui.table.table has a threshold to specify how many rows in addition should be loaded.

[davidgbarbero]

I have tried threshold, model size limit, startIndex and length of the TreeTable control, but RAP limits my request always to 100 entries, no top or skip parameters are added to the request. This works with sap.ui.table.Table, but not with sap.ui.table.TreeTable.

sap.m List and Table have a growing feature but sap.ui does not. I am not trying to load 1 million records, I am trying to load 200...

[stefan_engelhardt]

Do you use the smart table for the tree table? If that doesn't help I would suggest to raise an incident for the UI5 table team to clarify what the expected behaviour is and if you found a gap.

[jhindahl]

Is there any way to increase the server side limit of 5000 records to enable quicker reading of mass data via the OData endpoints?

[raveeshsaurabh3]

Hi Andre,

Thanks for this blog. Wanted to check one thing though.

How does this work with Fiori Elements ALP applications where we have more than 100 data points to show?

The OData query triggered by the FE framework for the chart does not have $top in it and hence service is returning the first 100 data points for the chart,  and there is no option to scroll or fetch more data.

Also we can not influence the OData query, as it is triggered by standard Fiori Elements FW.

Thanks

Raveesh