Consume a REST Web service on IBM i (AS400) using SQL
In this post, we will see how we can build a weather app natively on IBM i using SQL, RPGLE and HTTPGETCLOB, JSON_TABLE functions. The app will consume RESTful APIs to get the data in JSON format. Db2 for i has both the SQL functions available from V7.2 onwards; this functionality will work if you are on or above that version of the OS.
First, some background about JSON.
As can be seen, if we need to get marks in Physics from the JSON, we have to navigate it as
Student1.Physics to access it’s value and we get 95.
There are various ways JSON can be presented, sometimes it contains array of information nested for a key element.
Here, there are 2 entries for Key
"Phones", hence if we want only Office phone number, we have to navigate with array notation as
Phones.Office. The array index starts at zero. We will be using similar JSON in our application, hence this background.
Second, Web Services we will be using.
To get started, we will start by using two web services for our application.
- Mapbox Web service Link:
The Mapbox Web service accepts City/Area as input and provides Latitude, Longitude and details about the area in a JSON format. See the JSON Response
- Weatherstack Web service Link:
The Weatherstack API needs Latitude, Longitude for it to give weather forecast. This also gives JSON output to consume. See the JSON Response
These APIs are free to sign-up. Once signed-up, we need to register/create an app on both APIs and get API Key or Token, also the URL. This API Key will be needed for our request to be identified and processed. We will be using the API keys provided by respective APIs while processing our requests.
Third, the building blocks of our application.
DB2 for i has various HTTP functions available in library
SYSTOOLS. We will be using mainly below 3 functions.
This function accepts the URL of the web service and sends a
HTTP GETrequest. If the request is successful, it gives back the default response provided by the web service provider. It may be in XML or JSON or any other HTTP supported format.
HTTPGETCLOB is used as below:
HTTPGETCLOB(<Web URL>, <HTTP Header>)
This function is used to construct the URL needed by HTTPGETCLOB. The encoding of the URL needs to be correct for the web service to process it. Some characters are escaped while sending the request (e.g.
spaceis replaced by either
%20. e.g. Los Angeles becomes Los%20Angeles). URLENCODE comes handy for this and is highly recommended.
URLENCODE is used as below:
URLENCODE(<Value to be encoded>, <Encoding format, e.g.UTF-8 which is default>)
JSON_TABLE parses the JSON that is returned by the HTTPGETCLOB function. Using this function, we can extract the fields we are interested in from the response.
JSON_TABLE is used as below:
Select * from JSON_TABLE(<JSON-context-item>, '$' COLUMNS (Column-Name Data-type PATH column-path-expression)) AS X
Fourth, building our SQL procedures.
We will be creating two SQL procedures for our two APIs.
SQL procedures can be created using
RUNSQLSTM or using Run SQL Script option of IBM’s Access Client Solution.
We will directly jump into the code, so that, it is clear.
RUNSQLSTM has given me issues with EBCDIC conversion of
[ ]. At the moment, I don’t seem to have solution. I have tried changing the job CCSID to 37, still, it didn’t work. Using ACS’s Run SQL Script is encouraged to create the procedures.
The first SQL procedure is to fetch the location coordinates for the city passed, the code goes as below,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 CREATE OR REPLACE PROCEDURE YourLibrary.getGeoCode( IN City CHAR(50), IN APIKEY VARCHAR(200), OUT Latitude FLOAT, OUT Longitude FLOAT, OUT Place VARCHAR(100) ) LANGUAGE SQL RESULT SETS 0 BEGIN SELECT Lat, Lon, PName INTO Latitude, Longitude, Place FROM JSON_TABLE( SYSTOOLS.HTTPGETCLOB( 'https://api.mapbox.com/geocoding/v5/mapbox.places/' CONCAT SYSTOOLS.URLENCODE(TRIM(City), '') CONCAT '.json?access_token=' CONCAT SYSTOOLS.URLENCODE(TRIM(APIKEY),'') CONCAT '&limit=' CONCAT SYSTOOLS.URLENCODE('1',''), NULL ), '$' COLUMNS (Lat FLOAT path '$.features.center', Lon FLOAT path '$.features.center', PName CHAR(100) path '$.features.place_name' ) error on error ) as x; END
Here, we will see how the code is functioning,
|1-9||We are using standard SQL create procedure with all the input and output parameters we need.|
|12-14||To use JSON_TABLE, we have to use select statement with the fields we are interested in. We are selecting Latitude, Longitude and Place details.|
|15-22||HTTPGETCLOB expects web-url and optional HTTP header as it’s parameters, such as
|23||We are telling JSON_TABLE from where to extract information from the JSON.
|24-27||We select the columns we are interested in, declare the data type and provide the path to the field. This is similar to what we saw above about how to navigate a JSON while extracting office phone number.|
Our second procedure is similar with the required input/output fields and the web service URL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 CREATE OR REPLACE PROCEDURE YourLibrary.getForecast( IN Latitude FLOAT, IN Longitude FLOAT, IN APIKEY VARCHAR(200), OUT W_Description VARCHAR(500), OUT CurrentTemp FLOAT ) LANGUAGE sql RESULT SETS 0 BEGIN SELECT W_Desc, Temp INTO W_Description, CurrentTemp FROM JSON_TABLE( SYSTOOLS.HTTPGETCLOB( 'http://api.weatherstack.com/current?access_key=' CONCAT SYSTOOLS.URLENCODE(TRIM(APIKEY), '') CONCAT '&query=' CONCAT SYSTOOLS.URLENCODE(Latitude,'') CONCAT ',' CONCAT SYSTOOLS.URLENCODE(Longitude,''), NULL ), '$' COLUMNS (W_Desc VARCHAR(500) path '$.current.weather_descriptions', Temp FLOAT path '$.current.temperature' ) error on error ) as x; END
Fifth, writing SQLRPGLE to use SQL procedures.
We will be writing simple DSPF and SQLRPGLE to demonstrate the functionality. I will not post the whole RPGLE program, but will just give a snapshot of main subroutine to get the idea. The source code is hosted at GitHub and links are given in the below section.
Our application is ready to consume the web service! We can add error processing, capturing HTTP response status codes, etc. I kept the scope limited, so as to demonstrate how everything fits into the whole picture.
UPDATE (7th Sept.): Some observations and feedback received on this article.
Thanks to the feedback received on this article, it is noted that, for some reason
java.net.SocketException for the first time the program/procedure is run. If this error is ignored and the program retried, it seems to work afterwards. Any suggestions/feedback is welcome on this issue.
This is tested on IBM i V 7.4.
- Weather DSPF
- MapBox JSON Response
- Weatherstack JSON Response
Feel free to reach me on Twitter.