Skip to Content

OData Channel API – Implementing Paging of Entity Sets

Take 2 – make sure to read the second code snippet, much improved!

 

This week I am on board the SAP Train Race helping to support the teams that are developing cool apps based on SAP NetWeaver Gateway in hopes of winning some prizes as well as being featured at SAP Teched Madrid. One of the participants asked me how paging was supported in OData Channel API based services. I knew this was possible but I have never actually tried it with OData Channel…so I told him let me code something up as an example for you…I figured it would also make a good blog as I am sure in the future many people will want to implement this behavior.

 

We know that the OData standard supports paging on “Query” aka “retrieveEntitySet” operation via the URL query string parameters $top=x and $skip=y. Where $skip=y means to skip the first y entities, and $top=x means to grab the next x rows proceeding the skipped rows. An example of how this would look on a “Query” URL is this:

http://gateway:port/service_uri/BankSet?$top=5&$skip=15 

In theory, this should result in returning rows 16 to 20, but how to make this happen in the GET_ENTITYSET method of an OData Channel Entity data object?

 

First off, I have to admit, my ABAP skills are still a little rusty…went on a major Java detour for quite a bit of time…but now I am back…let’s see how this goes. J

 

If you look at the signature of the GET_ENTITYSET method you will notice a structure parameter named IS_PAGING. This structure has two attributes, TOP and SKIP…and you guessed it…if the $top and $skip parameters are present on the “Query” URL these values will be pushed into this corresponding structure attributes. So now the trick is how to construct the SELECT statement to use these vales.

 

So here is how I did it, I would be interested to know if you have a better way to go about this!

 

First off, I wanted to still support queries that didn’t pass in $skip and $top…so an if statement checking if $top is greater than zero would suffice (note: if $top and $skip are not set on the URL their values in the IS_PAGING structure will be zero)…if $top is zero, just go thru the old select statement, if $top is greater than zero we are doing some paging of the result set. One last note, I guess I could have supported when $top equals zero and $skip is greater than zero but I didn’t…if you want to do this you should be able to figure it out from the below code example. Ok, that said here is the code snippet from my first attempt (note: you must put this sort of code in the appropriate place of the GET_ENTITYSET method):

 

 

 

 

* Check if we are paging or not

 

 

 

if   ( is_paging-top > 0 ).

 

 

 

* total rowcount to select is skip +   top

 

    rowcount = is_paging-top +   is_paging-skip.

 

 

 

* select the data into an internal   table

 

    SELECT banks bankl banka FROM BNKA

 

           INTO CORRESPONDING FIELDS OF TABLE   lt_bank

 

           UP TO rowcount ROWS

 

           WHERE BANKS IN   lt_range_bank_country.

 

 

 

* make sure the number of rows returned   is greater than the number to skip

 

    if ( sy-dbcnt > is_paging-skip ).

 

 

 

* initialize the skip variable used to   read the proper rows    

 

      skip = is_paging-skip.

 

 

 

* loop over the table top times

 

      do is_paging-top times.

 

 

 

* increment skip – get the next row   after the skip count     

 

        skip = skip + 1.

 

 

 

* read the table starting at row skip +   1

 

        read table lt_bank index skip into   wa_bank.

 

 

 

* get the values we are after into the   return table

 

        ls_bank-id = wa_bank-bankl.

 

        ls_bank-bankcountry = wa_bank-banks.

 

        ls_bank-name = wa_bank-banka.

 

        append ls_bank to et_resulttable.

 

 

 

* see if we stil have more rows in the   internal table

 

* if not exit out of the loop

 

        if ( skip >= sy-dbcnt ).

 

          exit.

 

        endif.

 

      enddo.

 

    endif.

 

  else.

 

 

 

* this is just our standard select   without paging support

 

    SELECT banks bankl banka FROM BNKA

 

           INTO (ls_bank-bankcountry,   ls_bank-id, ls_bank-name)

 

           WHERE BANKS IN   lt_range_bank_country.

 

      append ls_bank to et_resulttable.

 

    ENDSELECT.

 

 

 

  endif.

 

 

 

 

 

 

So from the above code in the paging section, you can see we basically just select all the data (up to skip+top number of rows) into an internal table and from there we just get the rows that we actually want based on the top and skip values.

 

Take 2 – so I posted the original blog above only to find out an hour later that a utility class exists that will do the paging over any internal table! ARG!!! If I had only knew about that before…in any case, using this utility class (/IWBEP/CL_MGW_DATA_UTIL) makes life very easy to page over results. All you need to do is pass the static PAGING method the IS_PAGING structure and your internal table you want to page over. Here is the new resulting code using this class:

 

 

 

 

 

* if top is greater than 0 then we only   need

 

* skip + top rows returned – we are   limiting the data

 

* we are pulling from the DB

 

  if ( is_paging-top > 0 ).

 

    rowcount = is_paging-top +   is_paging-skip.

 

    SELECT banks bankl banka FROM BNKA

 

           INTO CORRESPONDING FIELDS OF TABLE   lt_bank

 

           UP TO rowcount ROWS

 

           WHERE BANKS IN   lt_range_bank_country.

 

  else.

 

* if top is equal to zero we need to   get all rows

 

    SELECT banks bankl banka FROM BNKA

 

 

 

 

 

           WHERE BANKS IN   lt_range_bank_country.

 

  endif.

 

 

 

* pass the IS_PAGING structure and   internal table

 

* into the static paging method

 

  CALL METHOD   /IWBEP/CL_MGW_DATA_UTIL=>PAGING

 

    EXPORTING

 

      IS_PAGING = IS_PAGING

 

    CHANGING

 

      CT_DATA   = lt_bank.

 

 

 

* put the data into the return table

 

  loop at lt_bank into wa_bank.

 

    ls_bank-id = wa_bank-bankl.

 

    ls_bank-bankcountry = wa_bank-banks.

 

    ls_bank-name = wa_bank-banka.

 

    append ls_bank to et_resulttable.

 

  endloop.

 

 

 

 

 

The SAP Train Race has been an awesome experience so far…without it I would not have learned of this easy way to page over result sets…its really nice to be working so closely with development here on the train.

I hope this helps you with your OData Channel API coding! 

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Jeff Gebo Post author
    So it just so happens that a colleague from Gateway development is sitting here with me and knows of a Utility class that has a method that does the paging for you…the class/method is /IWBEP/CL_MGW_DATA_UTIL=>PAGING…all you need to do is pass it the internal table you selected your data into as well as the IS_PAGING structure…it does the rest. Here is what the code snippet will look like now:

    * if top is greater than 0 then we only need
    * skip + top rows returned
      if ( is_paging-top > 0 ).
        rowcount = is_paging-top + is_paging-skip.
        SELECT banks bankl banka FROM BNKA
               INTO CORRESPONDING FIELDS OF TABLE lt_bank
               UP TO rowcount ROWS
               WHERE BANKS IN lt_range_bank_country.
      else.
    * if top is equal to zero we need to get all rows
        SELECT banks bankl banka FROM BNKA
               INTO CORRESPONDING FIELDS OF TABLE lt_bank
               WHERE BANKS IN lt_range_bank_country.
      endif.

    * pass the IS_PAGING structure and internal table
    * into the static paging method
      CALL METHOD /IWBEP/CL_MGW_DATA_UTIL=>PAGING
        EXPORTING
          IS_PAGING = IS_PAGING
        CHANGING
          CT_DATA   = lt_bank.

    * put the data into the return table
      loop at lt_bank into wa_bank.
        ls_bank-id = wa_bank-bankl.
        ls_bank-bankcountry = wa_bank-banks.
        ls_bank-name = wa_bank-banka.
        append ls_bank to et_resulttable.
      endloop.

    (0) 

Leave a Reply