RPGApi and paging data

A couple of articles back we built a REST webservice with an HTTP get route to employees that returned all 3 of our employees. It was marvelous, but we have real amounts of data in production. We don’t have IBMi’s to play games, we have them to process large amounts of data. This makes our get all employees endpoint a little useless. It was a good starting point for sure, but in real life, we need something a little better. The good thing is this is easy, and it will introduce some things I haven’t yet used in previous examples.

So here is an image excerpt from my data… I loaded 500 records into the table for testing. Sure it's not production scale, but it is enough to make the point.

Our main program stays pretty simple. Here it is.

 ctl-opt bnddir('RPGAPI':'YAJL') option(*srcstmt:*nodebugio) 
               decedit('0.') datfmt(*iso);

      /include RPGAPI/QRPGLESRC,RPGAPI_H
      /include YAJL/QRPGLESRC,YAJL_H

       dcl-ds data template qualified;
         id         packed(15:0);
         first_name varchar(20);
         last_name  varchar(30);
         salary     packed(15:2);
         hired_at   timestamp;
       end-ds;

       dcl-ds app likeds(RPGAPIAPP) inz;
       RPGAPI_get(app : '/v1/employees' : %paddr(EMP_index));
       RPGAPI_start(app : 3048);

 
       *inlr = *on;

       return;

Our data structure named data is just for holding the data coming out of the table. We are registering our get route and pointing it at the EMP_index procedure. This is where the real magic is so let's have a gander at it.

       dcl-proc EMP_index;
         dcl-pi *n likeds(RPGAPIRSP);
           request likeds(RPGAPIRQST);
         end-pi;
         dcl-ds response likeds(RPGAPIRSP);
         dcl-ds row likeds(data) inz;
         dcl-s row_i int(5:0) dim(5);
         dcl-ds rows likeds(data) dim(50) inz;
         dcl-s x int(10:0) inz;
         dcl-s tempJSON varchar(32000) inz;
         dcl-s page_size int(10:0) inz;
         dcl-s page_number int(10:0) inz;
         dcl-s sql_string char(500) inz;
         dcl-s offset int(10:0) inz;

         RPGAPI_setHeader(response : 'Content-Type' : 'application/json');
     1.  page_size = %int(RPGAPI_getQueryParam(request : 'page_size'));
     2.  page_number = %int(RPGAPI_getQueryParam(request : 'page_number'));

         sql_string = 'select id, first_name, last_name, salary, hired_at ' +
                      'from dlong.employees ' +
                      'order by id ' +
                      'limit ? offset ?';

         exec sql prepare stmt from :sql_string;
         exec sql declare get_all_emps cursor for stmt;

         x = 1;
         offset = (page_size * (page_number -1));
         exec sql open get_all_emps using :page_size, :offset;
         exec sql fetch next from get_all_emps into :row :row_i;
         dow sqlstate <> '02000';
           eval-corr rows(x) = row;
           x = x + 1;
           exec sql fetch next from get_all_emps into :row :row_i;
         enddo;
         exec sql close get_all_emps;

         tempJSON = cvtToJSON(rows);

     3.  RPGAPI_setStatus(response : HTTP_OK);         
     4.  RPGAPI_setBody(response : tempJSON);

         return response;

       end-proc;

If you take a look at points 1 and 2 in the code for EMP_index you will see I am pulling the data from query params. So the request for today will look like 

/v1/employees?page_size=10&page_number=1

By being able to access those values you can then pull the records needed to return a “page” of data. So now you are returning manageable chunks of data to the caller, based on what they are asking for. So if the request looks like 

/v1/employees?page_size=10&page_number=1

We return the first 10 records, here are the results on my data set.

Then in the next call, page 2 could be asked for like so

/v1/employees?page_size=10&page_number=2

And my response looks like…

You will see that the rest of the code is simple SQL and RPG to pull the requested data. You could simplify the code by loading the results directly into the rows data structure array. In a production environment, I would have done it that way.

Please notice points 3 and 4. In the past, I have just set the response.body and the response.status directly. I’ve gotten some good feedback about working towards “protecting” the app, request, and response data structures that drive the framework. These procedures provide a way to do that in the future. Right now, this is just another way to set those values. 

Check out RPGApi in GitHub.