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!
