Guru: Consuming A REST Web Service Using SQL And POST

October 2, 2017 Mike Larsen

In my prior article, I showed how to consume a REST web service using the GET verb. This time, we’re going to continue to build our knowledge of web services by working with the POST verb. In addition to working with a different verb, I’m also going to demonstrate how to pass a header and body to the service.

The goal is to post information to an Amazon Web Service (AWS). I created a simple REST Amazon Web Service that accepts information about a pet which will be inserted into a pet store database. I pass a JSON structure to the web service that contains the pet type and price. The format of the JSON structure is:

{
  "type": "dog",
  "price": 249.99
}

Following a successful request, I receive a JSON response that contains the information passed to the web service, along with a “success” message that looks like this:

{
  "pet": 
  {
    "type": "dog",
    "price": 249.99
  },
   "message": "success"
}

Since the service returns a JSON table, I parse the results and write them to a DB2 table so I can view them. Let’s step through each section of code.

This story contains code, which you can download here.

I begin by populating three variables required by the web service.

1. The URL of the web service.
2. The Header that needs to be passed. In this example, I indicate the content being sent is a JSON object.
3. The Body that needs to be passed. The web service expects a JSON object be passed to it that contains the pet type and price.

// these would be soft-coded parameters passed to the program

   WebServiceUrl = 'https://um3q1dvnv6.execute-api.us-east-1.amazonaws.com/' +
                   'test/pets/petstorewalkthrough/pets';

   WebServiceHeader = '-

';

   WebServiceBody   = '{"type": "dog","price": 249.99}'; 

Next, I clear the DB2 table that will store the results from the web service to make sure I only have the results from the latest call to the service.

// clear output table before consuming the web service

   Exec sql
      Delete from pet_Post;

Now that the setup work is done, it’s time to consume the web service. The following section of code does just that. I’ll show the entire routine, then break it down piece by piece.

//--------------------------------------------------------
// ConsumeWs  subroutine
//--------------------------------------------------------

 Begsr ConsumeWs;

 Exec sql
   Declare CsrC01 Cursor For

     Select * from
       Json_Table(Systools.HttpPostClob(:WebServiceUrl, :WebServiceHeader,
                                        :WebServiceBody),
       '$'
       Columns(ws_pet_message VarChar(30)  Path 'lax $.message',

       NESTED Path
       '$.pet[*]'
         Columns(ws_pet_type  VarChar(30)  Path 'lax $.type',
                 ws_pet_price decimal(5,2) Path 'lax $.price')
         )) As x;

   Exec Sql Close CsrC01;
   Exec Sql Open  CsrC01;

   DoU 1 = 0;
     Exec Sql
         Fetch Next From CsrC01 into :jsonData;

        If SqlCode < *Zeros or SqlCode = 100;

           If SqlCode < *Zeros;
              Exec Sql
                   Get Diagnostics Condition 1
                   :Text = MESSAGE_TEXT;
           EndIf;

           Exec Sql
                Close CsrC01;
           Leave;
        EndIf;

         Exec sql
         Insert Into Pet_Post
                     (pet_message, pet_type, pet_price, AddPgm, AddUser,
                      UpdatePgm, UpdateUser)
              Values (:jsonData.petMessage, :jsonData.petType,
                      :jsonData.petPrice,
                      :pgm_stat.ProgramId, :pgm_stat.User,
                      :pgm_stat.ProgramId, :pgm_stat.User);

  Enddo;

 Endsr;

I declare a cursor, then execute the SQL statement that consumes the REST service using the HTTPPOSTCLOB function. The JSON_TABLE function parses the results returned from the web service.

Declare CsrC01 Cursor For

     Select * from
       Json_Table(Systools.HttpPostClob(:WebServiceUrl, :WebServiceHeader,
                                        :WebServiceBody),
       '$'
       Columns(ws_pet_message VarChar(30)  Path 'lax $.message',

       NESTED Path
       '$.pet[*]'
         Columns(ws_pet_type  VarChar(30)  Path 'lax $.type',
                 ws_pet_price decimal(5,2) Path 'lax $.price')
         )) As x;

I pass three parameters to the HTTPPOSTCLOB function; the web service URL, a Header, and a Body.

Let’s breakdown the JSON_TABLE function since I use it to parse the results from the service. The JSON_TABLE function allows us to decompose JSON objects into a relational format. I define the columns and data types of the JSON object I’m expecting to receive as well as a path to where information can be found inside the JSON object. In essence, I provide directions on how to find the data stored in the JSON object.

Now we’ll look at the directions I give in this example. The ‘$’ tells the function to start with the current JSON object. Next, I define the fields and data types for the columns contained in the JSON object. Finally, I give the path to the data elements. Notice that I defined two sets of columns in this statement. The message being returned is found directly in the top level JSON object. The period in the path expression (for example, ‘lax $.message’) directs it to look inside the column message. The pet type and price, however, is found in an array named pet. I define this as a nested path and give the instructions to look at the JSON object (indicated by ‘$’) and inside (‘.’) the array pet (‘pet[*]’). The last piece of code that needs explanation is the term lax. The SQL/JSON path expression has a mode associated with it. The mode can either be lax or strict. When using ‘lax’ mode, certain error conditions are tolerated when the SQL/JSON path expression is evaluated. When using ‘strict’ mode, error conditions are not tolerated, resulting in a failure.

The next section of code reads the results returned from the service.

Exec Sql Close CsrC01;
   Exec Sql Open  CsrC01;

   DoU 1 = 0;
     Exec Sql
         Fetch Next From CsrC01 into :jsonData;

        If SqlCode < *Zeros or SqlCode = 100;

           If SqlCode < *Zeros;
              Exec Sql
                   Get Diagnostics Condition 1
                   :Text = MESSAGE_TEXT;
           EndIf;

           Exec Sql
                Close CsrC01;
           Leave;
        EndIf;

I open the cursor, read through it, and fetch it into a data structure where I process the individual fields. Once I’ve read all the rows, the cursor is closed. The data structure is shown below:

dcl-ds jsonData   qualified;
       petMessage varchar(30);
       petType    varchar(30);
       petPrice   packed(5:2);
end-ds;

The last piece of code inserts the data elements retrieved from the service into a DB2 table.

// insert the parsed JSON data into a db2 table

        Exec sql
         Insert Into Pet_Post
                     (pet_message, pet_type, pet_price, AddPgm, AddUser,
                      UpdatePgm, UpdateUser)
              Values (:jsonData.petMessage, :jsonData.petType,
                      :jsonData.petPrice,
                      :pgm_stat.ProgramId, :pgm_stat.User,
                      :pgm_stat.ProgramId, :pgm_stat.User);

After running the program, I view the results stored in the DB2 table using ACS and see the expected results match what was returned.

As the need for web services increases, it’s very important to learn how to use them. This article focused on REST services and JSON objects, as it appears that is the direction the industry is taking. It also went a little deeper to show how to pass header and body information to a web service. Getting started with web services can be challenging, and hopefully this article will make it a little easier.

RELATED ARTICLE

Guru: Consuming A REST Web Service Using SQL And GET