IBM provides a library/schema called SYSTOOLS that contains some great examples that we can utilise within our applications. These can make our developers job much easier.
Creating a webservice from our RPG programs, or service programs, has been very easy over the last few years, but it has been a totally different kettle of fish trying to get RPG to consume a webservice. The functions provided by IBM in the SYSTOOLS library makes consuming webservices a lot simpler.
The DB2 HTTP Services have been available since 7.1 but don’t seem widely used, or known about.
The SYSTOOLS is a collection of DB2 supplied examples and tools.
Be aware that the SYSTOOLS utilities are not covered by IBM Service & Support.
If you are concerned with security, aren’t we all, you will be pleased to know that these tools will use a secure channel, HTTPS, when specified.
If you are using the SYSTOOLS functions, you will have to add that to your library list.
We can easily see all the particulars for any of the SYSTOOLS functions using Access for Client Solutions.
Expand the functions section of the SYSTOOLS schema.
To show specific details for a function, right click on the function and click Definition.
The definition for the HTTPGETBLOB function is shown in the figure below.
Note: You could also use IBM Navigator for i to find all this information.
In this section, I will show how we can incorporate these services within our applications, providing extra functionality that we would not expect to be easily amalgamated. Once again, proving how flexible our IBM i server is.
In the first example, I will get a PDF from our website and store it in a DB2 table.
To start with, let’s create the table to store it in.
CREATE TABLE FormaServe.PDF(URL VARCHAR(1024), PDF BLOB(2G));
Then use the HTTPGETBLOB UDF to get the traning.pdf. This object is then embedded within the SQL INSERT INTO function.
INSERT INTO PDF(URL, pdf)
A quick SELECT on the table will show the binary object stored in our table.
FROM pdf ;
The actual binary object (the PDF) is stored within our database, not just a link to the web location.
In this second example, I will explain how RPG, with embedded SQL, can grab RSS feeds. If you want to know more about RSS feeds, click the link here.
I will use a BBC news feed to display the latest news headlines. The program will output these feeds onto a 5250 screen, as can be seen in the graphic below.
Using the hotspot setup within ACS 5250 emulator, the user can click on any link and the article will open in your default favourite web browser.
From the figure above, you can see how we can easily use these functions within a RPG program. How cool is that!
Let me show how this was achieved.
By running the SQL statement below, it will return a binary object of all the BBC news feeds from the URL http://feeds.bbci.co.uk/news/england/rss.xml
Select SYSTOOLS.HTTPGETBLOB(‘http://feeds.bbci.co.uk/news/england/rss.xml’ ,”)
This SQL statement produces the following;
As its in binary, not much use to us at the moment, but what we can do is use a SQL XML function that will convert this value into something more meaningful.
As the figure below shows, the XMLPARSE SQL function is the correct function to achieve this. It will take a parameter, the binary object, and convert it to XML.
FROM XMLTABLE(‘$result/rss/channel/item’ PASSING XMLPARSE(DOCUMENT
AS “result” COLUMNS title VARCHAR(128) PATH ‘title’, description VARCHAR(1024) PATH
‘description’, LINK VARCHAR(255) PATH ‘link’)
FETCH FIRST 20 ROWS ONLY ;
Three columns are extracted from the binary object, they are the news title, description and link.
The above the SQL has been embedded into a RPG program to display the details of each news feed.
The full source for the RPG and display file used for this example can be found here.
Top Tip: Every so often it is worth checking the contents of the SYSTOOLS library, IBM adds some real gems in there that are not that well publicised – Keeping checking!
Hopefully, this article will give you a quick insight in how we can easily use DB2 HTTP services and include their functionality within our applications.
Thanks once again to IBM Developerworks for examples that were used during the compiling of this article.
I’m looking forward to the next round of the i-UG user group meetings this year in the UK, this is at the Mount Hotel in Wolverhampton on 8th November 2018. Full details can be found on the i-UG site here. Hopefully, you can attend my presentation on Open Source Package Management.
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.