API pagination with OpenEdge

It's more than well known the importance of sending records from server to client in batches through APIs. One of the advantages is a less time wait to the client whilst fetch the data, less unnecessary data traffic running out from the server and, not to mention that this is a best practice.

Well, I'm not the best person to explain to you what is API pagination neither why should you adopt it. The purpose here is to show a way to do this using Progress OpenEdge and Progress Application Server for OpenEdge (PASOE or OEPAS).

If you know a different way to paginate your APIs with Progress OpenEdge , please share in the comments. And before to get into the code, it's worth to mention the free course of Progress Software to become an OpenEdge developer, checkout!

Before the code

In this article, I'll not get into details about the PDSOE (IDE) configuration or the creation of the server project. Up to now, my PDSOE is already settled up with a database connection, the sports2020, and an OpenEdge project with Server and REST transport for PASOE, called ws.

The example to the API pagination is to return item records (from sports2020) with a maximum limit of 200 records per request and an option to point the id for the next batch of records. Both of them passed as parameters to the URL of the endpoint.

Sections of this article:

1. Code (the .p file)
2. Mapping the .p as an endpoint
3. Testing on a PASOE local instance

1. Code (the .p file)

Let's start defining the objects, such as temp-table, datasource, dataset, and query:

DEFINE TEMP-TABLE ttItem NO-UND
    FIELD ItemNum    LIKE Item.ItemNum 
    FIELD ItemName   LIKE Item.ItemName 
    FIELD Price      LIKE Item.Price 
    INDEX ItemNum IS PRIMARY IS UNIQUE ItemNum.
    
DEFINE DATA-SOURCE srcItem FOR Item.
DEFINE DATASET dsItem FOR ttItem.
DEFINE QUERY qItem FOR ttItem.        

Then, the INPUT and OUTPUT parameters. Those parameters will be used when defining the endpoint on the REST Resource URI Editor.

DEFINE INPUT PARAM              iQty    AS INTEGER   NO-UNDO
DEFINE INPUT-OUTPUT PARAM       pRowId  AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAM             cStatus AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAM DATASET FOR dsItem.        

The main block, with all the logic and comments at each step:

/* Clear dataset */
DATASET dsItem:EMPTY-DATASET ().

/* Initial validation */
IF iQty = 0 THEN RETURN.
IF iQty > 200 THEN
DO:
    ASSIGN
        cStatus = "Not alowed! Maximum of 200 records per request!".
    RETURN.
END.

ASSIGN
    cStatus = "Unknown error!".

/* Attaches the srcItem datasource to the ttItem buffer */
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE).

/* Set the RESTART-ROWID using the pRowId passed */
IF LENGTH(pRowId) > 0 THEN
  DATA-SOURCE srcItem:RESTART-ROWID = TO-ROWID(pRowId).

/* Set the batch size */
BUFFER ttItem:BATCH-SIZE = iQty.

/* Empties the table before the FILL operation begins */
BUFFER ttItem:FILL-MODE = "EMPTY".

/* Fill dataset */
DATASET dsItem:FILL().

/* Get the NEXT-ROWID of DATA-SOURCE to return */
pRowId = STRING(DATA-SOURCE srcItem:NEXT-ROWID).

/* Detach the datasource */
BUFFER ttItem:DETACH-DATA-SOURCE().

ASSIGN
    cStatus = "Success!".        

And a CATCH using Progress.Lang.Error, to catch any raised condition not expected at runtime:

CATCH err AS Progress.Lang.Error 
    DEFINE VARIABLE iMessage AS INTEGER NO-UNDO.
    
    DO WHILE iMessage < err:NumMessages:
        ASSIGN 
            cStatus = SUBSTITUTE ("&1~n&2", cStatus, err:GetMessage(iMessage))
            iMessage = iMessage + 1.
    END.
    IF err:CallStack <> ? THEN DO:
        ASSIGN 
            cStatus = SUBSTITUTE ("&1~n~nCall Stack:~n&2", cStatus, err:CallStack).
    END.
END CATCH.:        

The full code you can find here.

Once the code is done, the next step is to map the program as an endpoint on the REST Resource URI Editor.

2. Mapping the .p as an endpoint

On the REST Resource URI Editor, you can add a new resource, and associate programs to the verbs (GET, PUT, POST and DELETE).

Here, I created a new endpoint (named as item), and select the item.p file on the GET verb:

Then I linked the input parameters as Query String Parameters:

Finally, linked the output parameters on the Response Body:

As I already have the PASOE instance running on localhost, and the project is already deployed on the OpenEdge Explorer, the next step is just to test the endpoint.

3. Testing on a PASOE local instance

Testing the records limit calling http://localhost:8810/ws/rest/wsService/item?iQty=300:

Great! The first validation was successful!

Now, testing with limit of 3 records in a two steps:

Firstly, calling without pass the pRowId parameter, what should give me the three first records in the table (http://localhost:8810/ws/rest/wsService/item?iQty=3).

And then, getting the returned pRowId value and passing to the next request (http://localhost:8810/ws/rest/wsService/item?iQty=3&pRowId=0x0000000000005c04)

See the results below:

Great! All the expected results worked just fine! While this article focuses on a specific and simple scope, there is room for further enhancements and validations.

If you're interested in exploring the complete code, you can find it by click here.

I hope you like it! Comment if this is new to you or if you build your pagination APIs differently!