Guru: Testing URLs With HTTP_GET_VERBOSE

May 20, 2024 Gregory Simmons

In my previous article Fooling around with SQL and RPG, I explored having a little fun with the HTTP_GET function to fetch a witty Dad Joke from https://icanhazdadjoke.com/. In this article, I want to demonstrate a more practical use of this great function. Or should I say, another version of HTTP_GET, that is HTTP_GET_VERBOSE, which also was introduced to us by the DB2 team in V7R3.

In its simplest implementation, I can insert the URL I want to test into an SQL statement:

select *
from table(QSYS2.HTTP_GET_VERBOSE('https://icanhazdadjoke.com/',''))

I will later want to run this embedded in an RPG program, but for now I will run the above statement in Run SQL Scrips. This results in two columns being returned; RESPONSE_MESSAGE which will contain the Html that contains the joke and RESPONSE_HTTP_HEADER. In this header, I want to call your attention to two pieces; HTTP_STATUS_CODE and Content-Type.

A good start, but I prefer JSON over HTML, so let’s alter this statement a bit to return the RESPONSE_MESSAGE as JSON. In V7R4, header support was added. This allows me to specify that I don’t want the default application type of text/html, but that I would like JSON:

select *
from table(QSYS2.HTTP_GET_VERBOSE('https://icanhazdadjoke.com/',
           '{"header": "Accept,application/json"}'));

This is better. Note that now the RESPONSE_MESSAGE is returned as a JSON string and the Content-Type in the RESPONSE_HTTP_HEADER also reflects that I requested JSON:

For this exercise, I needed to not just check one URL, but I had a file of URLs; thousands of URLs. Of course, this would be a tedious task to plug each of them, one by one, into the above statement. In this next iteration, I’ll pull them from a file and isolate the HTTP_STATUS_CODE from the RESPONSE_HTTP_HEADER. The first value in the RESPONSE_HTTP_HEADER is the HTTP_STATUS_CODE. And this will allow me to get a listing from the file of which records contain active URLs vs those that do not.

First, let’s create a file and populate it with a couple of records:

CREATE OR REPLACE TABLE resource_listing (id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 
resource CHAR(10), resource_url VarChar(255) Allocate(50));

Insert Into resource_listing (resource, resource_url)
Values ('DAD_JOKES','https://icanhazdadjoke.com/'),
       ('FAV_JOKES','https://icanhazdadjoke.com/favorite/');
      
Select * From resource_listing;

Alright, good, now we have some test data; one record that will return with 200 – success and one that will return 404 – fail:

Now, we can change the SQL from above to check all URLs in our test file. I am a big fan of common table expressions or CTEs, which have lots of benefits, but the primary benefit for me is readability and organization. So, in this iteration of the SQL statement, I started with a CTE that simply gathers a list of the resource and resource_urls worthy of testing:

With resource_urls as (Select resource, resource_url
                       From resource_listing
                       Where Trim(resource_url) <> '')
Select resource, resource_url, HTTP_STATUS_CODE
From resource_urls,
     table(QSYS2.HTTP_GET_VERBOSE(resource_url)),
     json_table(RESPONSE_HTTP_HEADER, 'lax $' columns 
                (HTTP_STATUS_CODE num(3) path '$.HTTP_STATUS_CODE'))

The second part of the above SQL statement strips out the HTTP_STATUS_CODE from the RESPONSE_HTTP_HEADER. Now my SQL results in a concise list of resources and whether or not I have a good URL for them.

And of course, I can isolate the URLs that need attention by adding a simple Where clause to the SQL statement.

Create the above SQL as a view would wrap this up nicely. When embedding SQL into my RPG program(s), I usually prefer to keep my code clean and just select * from my view. But no matter how you implement the HTTP_GET and/or HTTP_GET_VERBOSE functions, I hope you find the above examples useful.

Until next time, happy coding.

Gregory Simmons is a project manager with PC Richard & Son. He started on the IBM i platform in 1994, graduated with a degree in Computer Information Systems in 1997 and has been working on the IBM i platform ever since. He has been a registered instructor with the IBM Academic Initiative since 2007, holds a COMMON Application Developer certification and is a part of the 2024 class of IBM i Champions. In his spare time, he enjoys running, backpacking, hunting, and fishing.

RELATED STORIES

Guru: Fooling Around With SQL And RPG

Guru: Procedure Driven RPG And Adopting The Pillars Of Object-Oriented Programming

Guru: Getting Started With The Code 4 i Extension Within VS Code

Guru: Procedure Driven RPG Means Keeping Your Variables Local

Guru: Procedure Driven RPG With Linear-Main Programs

Guru: Speeding Up RPG By Reducing I/O Operations, Part 2

Guru: Speeding Up RPG By Reducing I/O Operations, Part 1

Guru: Watch Out For This Pitfall When Working With Integer Columns