Guru: Consuming A REST Web Service Using SQL And GET

September 18, 2017 Mike Larsen

The use of web services in the industry continues to grow, and sometimes it can be challenging to find information on how to work with them on IBM i. So, what is a web service? In a nutshell, web services allow systems to communicate via the web using file formats such as JSON and XML.

This article demonstrates how to consume a REST web service using SQL. The example I’m working with uses the ‘GET’ verb to retrieve information from an Amazon Web Service (AWS). I created a simple REST Amazon Web Service that retrieves information about a pet from a pet store database. I pass a pet ID to the service and it returns the pet ID, pet type, and price of the pet. The expected result when consuming the pet store web service is:

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

Since the web 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 a variable that holds the URL to the web service. The last position of the URL contains the pet ID (1) I’m inquiring about.

// this would be a soft-coded parameter passed to the program

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

Next, I clear the DB2 table that stores the results from the web service to make sure I only have the results from the latest invocation.

// clear the output file before consuming the web service

Exec sql
  Delete from pet_Get;

Pretty easy so far, right? Here’s where it gets interesting. The following section of code does the majority of the work. I’ll show the entire routine first, before breaking it down piece by piece.

Begsr ConsumeWs;

 Exec sql
   Declare CsrC01 Cursor For

     Select * from
       Json_Table(Systools.HttpGetClob(:WebServiceUrl, :WebServiceHeader),
       '$'
       Columns(ws_pet_id    BigInt       Path 'lax $.id',
               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

                // perform error handling

                   Get Diagnostics Condition 1
                   :Text = MESSAGE_TEXT;
           EndIf;

           Exec Sql
                Close CsrC01;
           Leave;
        EndIf;

        // insert the parsed JSON data into a db2 table

        Exec sql
         Insert Into Pet_Get
                     (pet_id, pet_type, pet_price, AddPgm, AddUser,        UpdatePgm,
                      UpdateUser)
              Values (:jsonData.petId, :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 to consume the REST service using the HTTPGETCLOB function. The JSON_TABLE function parses the results returned from the service.

Declare CsrC01 Cursor For

     Select * from
       Json_Table(Systools.HttpGetClob(:WebServiceUrl, :WebServiceHeader),
       '$'
       Columns(ws_pet_id    BigInt       Path 'lax $.id',
               ws_pet_type  VarChar(30)  Path 'lax $.type',
               ws_pet_price decimal(5,2) Path 'lax $.price')
       ) As x; 

I pass two parameters to the HTTPGETCLOB function; the web service URL and a Header. In this example, there isn’t a need to pass Header data, so the Header parameter is blank. The URL was populated earlier and is stored in variable WebServiceUrl. HTTPGETCLOB is one of many user-defined functions (UDFs) available on the IBM i that allow us to consume REST web services via HTTP.

The JSON_TABLE function allows us to decompose JSON JavaScript Object Notation) objects into a relational format. I define the columns and data types of the JSON object I expect 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.

Let’s take a 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. In this case, I tell it to look for the actual column names as defined in the service. The period in the path expression (for example, ‘lax $.id’) directs it to look inside the column id. So, what is that ‘lax’ thing? 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

                // perform error handling

                   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;
       petId    zoned(2:0);
       petType  varchar(30);
       petPrice packed(5:2);
end-ds;  

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

// insert the parsed JSON data into a db2 table

        Exec sql
         Insert Into Pet_Get
                     (pet_id, pet_type, pet_price, AddPgm, AddUser, UpdatePgm,
                      UpdateUser)
              Values (:jsonData.petId, :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.

This article showed how to use SQL to consume a REST service using the UDF HTTPGETCLOB. As mentioned, there are many other UDFs that can be used as well. In a future article, I’ll show how to use the HTTPPOSTCLOB UDF to consume a REST service, while sending it both a Header and a Body.