New QSYS2.HTTP Functions SQL (7.4 TR5 and 7.3 TR11)

A few days ago the new Technology Refresh TR4 for 7.4 and TR11 for 7.3 of the IBM i operating system were presented

Among the various innovations are the new HTTP functions in QSYS2, based on the IWS Transport API and no longer on Java … with notable improvements in performance and management of possibilities in the consumption of HTTPS SSL services.

If you have already used HTTPGETCLOB, HTTPPOSTCLOB or their VERBOSE versions, switching to the new QSYS2-HTTP_ functions will be very easy for you (if you pay due attention to the position of the parameters, those pranksters from IBM Rochester’s lab have reversed them (see below !).

If, on the other hand, you haven’t fought with SQL’s HTTP functions yet, the new versions will save you some bad words, because these new functions have better error handling.

Consumption of a REST web service with GET method (without errors):

Let’s see a simple example of consumption of a REST Web Service in GET: SYSTOOLS.HTTPGETCLOB and QSYS2.HTTP_GET, they are not very different … just call the correct function:

-- Get an existing WEB page with SYSTOOLS.HTTPGETCLOB ... it's so simple!

select SYSTOOLS.HTTPGETCLOB('http://www.google.com/','') from sysibm.sysdummy1;



-- Get the same WEB page with QSYS2.HTTP_GET ... about the same

select QSYS2.HTTP_GET('http://www.google.com/','') from sysibm.sysdummy1;

Consumption of a REST in GET with errors (example 404-Not found)

If there are no mistakes the thing is very simple … if, instead, we have to deal with HTTP errors (4xx or 5xx), things get a little more complicated with old SYSTOOLS.HTTPGETCLOB compared to new QSYS2.HTTP_GET function.

If we try to call from the ACS SQL Script management, a Web Service that returns, for example, a 404-Not found error (such as a web page that does not exist), HTTPGETCLOB goes into error and a Popup window is presented with the error itself … a general Java error that could mean anything and everything.

If we want to avoid this error message we can use the SYSTOOLS.HTTPGETCLOBVERBOSE function, which, at least, responds with an HTTPHeader from the Web Service and, with a little attention on the request Header, we can also get response message ( from which, generally, we can understand the real reason for the error).

Let’s see it step-by-step:

-- Trying a REST Get web service in 404-Not found Error ... I
-- I got a Popup Error with SQL State 38000 Vendor code -302 !!!!!!!!!!

select SYSTOOLS.HTTPGETCLOB('http://www.google.com/notExist','') from sysibm.sysdummy1;



-- Ok, let me try with the HTTPGETCLOBVERBOSE (pay attention, is Table Function, not a
-- scalar function!)

select * from table( systools.httpGetClobVerbose('http://www.google.com/notExist','')) x;


-- Now I don't get the popup-error, 
-- I can read the HTTP Response-Header but I don't get the response-message (body) from Google

-- Let me add this HttpHeader attribute (includeErrorMsg="true") 
-- (pay attention, is an HTTPHeader Attribute not an header value!!!)

select * from table( systools.httpGetClobVerbose('http://www.google.com/notExist','<httpHeader includeErrorMsg="true"/>')) x;


-- Now let me try with the new QSYS2.HTTP_GET function

select QSYS2.HTTP_GET('http://www.google.com/notExist','') from sysibm.sysdummy1;


-- Good! I can get the response-message from Google without annoying popup-messages

Web Service REST with POST method: SYSTOOLS.HTTPPOSTCLOB vs QSYS2.HTTP_POST (Google Translate API Example)

The sources of this part can be found in this Github Gist ;

When instead we have to consume REST Web Services in POST (or in PUT), things become a moment more complicated because we have to pass the body (payload) of the message, in JSON and, often, also some particular settings of the HTTP Header.

To take an example of calling up a Web Service Rest in POST, I “stole” to my colleague Marco Balgera, his own script for translation with the Google Translator API … of course, I first opened my own account on Google Cloud Platform to get the appropriate API-KEY to be used in calls to Web Services (since I was stealing the idea … I could also steal his API-KEY … actually! … but I’m an honest thief!).

The Web Service we’re going to call is one of the many made available by Google in the Cloud Console … if we can use this service for translation, we shouldn’t have any problems with other Google web services too!

API-KEY, URL, HEADER & BODY … Global Variable

To simplify the syntax of the examples that I report below, I first build the Global Variable SQL (we can think of them as a kind of Data-Area) with the JSON or XML texts that we will need for the web services …

I also store my API-KEY in a Global Variable … if I had to change it … I only change the Default value of my variable and all the SQL statements continue to work.

-- Google Translator (API Key from my Google Cloud Console)
create variable faq400.GV_GoogleAPIKey varchar(100) default('xxxxxxxxxxxxxxxxxxxxxxxxxx');
 
// insert here your GoogleAPI key!!!

-- Other Global variables
create variable faq400.GV_URL varchar(256);
create variable faq400.GV_HEADER varchar(1000);
create variable faq400.GV_HEADERJSON varchar(1000);
create variable faq400.GV_BODY varchar(32000);

I create a small table in QTEMP with the names of some fruits … goal of the project is to translate those names into different languages and update the table in QTEMP accordingly

-- Create a sample simple table
CREATE TABLE qtemp.FruitNames ( 
	"ID" INTEGER GENERATED ALWAYS AS IDENTITY, 
	NameITA varCHAR(30) CCSID 280 NOT NULL DEFAULT '', 
        NameENG varCHAR(30) CCSID 37 NOT NULL DEFAULT '',
        NameESP varCHAR(30) CCSID 284 NOT NULL DEFAULT '',
        NameGER varCHAR(30) CCSID 273 NOT NULL DEFAULT '',
        NameFRA varCHAR(30) CCSID 297 NOT NULL DEFAULT ''
 ); 

-- Insert som fruits in Italian (Mela=Apple and so on)

insert into qtemp.FruitNames (NameITA)
     values('Mela'),
           ('Pera'),
           ('Pesca'),
           ('Albicocca'),
           ('Banana'),
           ('Ciliegia'),
           ('Fragola'),
           ('Caco'),
           ('Castagna');

To the Google Translation API, as explained in the documentation of the function itself, we need to pass a JSON with an array of words or phrases and references to the source and target languages.

So I try to create a JSON as desired by the Google API:

-- Create a JSON with an array of words o sentences ... as Google API like
select   json_object('q' value JSON_ARRAYAGG(nameITA),
                   'source' value 'it',
                   'target' value 'en',
                   'format' value 'text') as "BodyMsg"
                   from qtemp.FruitNames ;



-- Here my JSON
{
	"q": [
		"Mela",
		"Pera",
		"Pesca",
		"Albicocca",
		"Banana",
		"Ciliegia",
		"Fragola",
		"Caco",
		"Castagna"
	],
	"source": "it",
	"target": "en",
	"format": "text"
}

I just have to put it all together and really try the translation service:

-- URL ... with my APi-KEY

set faq400.GV_URL='https://translation.googleapis.com/language/translate/v2?key=' concat faq400.GV_GoogleAPIKey;

-- Header (XML)

set faq400.GV_HEADER='<httpHeader><header name="content-type" value="application/json"/></httpHeader>';


-- Body ... with my words

set faq400.GV_BODY= (
select   json_object('q' value JSON_ARRAYAGG( nameITA),
                   'source' value 'it',
                   'target' value 'en',
                   'format' value 'text') as "BodyMsg"
                   from qtemp.FruitNames ) ;


-- And finally, let's try Google Translator API

select systools.httppostclob(faq400.GV_URL,
       cast(faq400.GV_HEADER as clob(1k)),
       cast(faq400.GV_BODY   as clob(10k)) )
       from sysibm.sysdummy1;

-- Here the JSON I get
{
	"data": {
		"translations": [
			{
				"translatedText": "Apple"
			},
			{
				"translatedText": "Pear"
			},
			{
				"translatedText": "Fishing"
			},
			{
				"translatedText": "Apricot"
			},
			{
				"translatedText": "Banana"
			},
			{
				"translatedText": "Cherry"
			},
			{
				"translatedText": "Strawberry"
			},
			{
				"translatedText": "Caco"
			},
			{
				"translatedText": "Chestnut"
			}
		]
	}
}

OK, SYSTOOLS.HTTPPOSTCLOB works … after all, my colleague Marco had already tried it … I had no doubt!

Let’s see if it also works with QSYS2.HTTP_POST … but, be careful … it is not enough to change the name of the SQL Function, we must pay attention to the order of the parameters and to the Header, which is no longer an XML but a JSON: those Rochester pranksters made this joke on us!

-- Let's create a new Header, JSON with a special option "sslTolerate"="true" if we don't need to check SSL Certificates

set faq400.GV_HEADERJSON= 
json_object('header' value 'content-type,application/json',
            'header' value 'content-length, ' concat length(trim(faq400.GV_BODY)),
            'sslTolerate' value 'true'
                   );

-- Now we can try QSYS2.HTTP_POST (pay attention to parameter's sequence, not the same!)
select QSYS2.HTTP_POST(faq400.GV_URL,
               cast(faq400.GV_BODY   as clob(10k)), 
              cast(faq400.faq400.GV_HEADERJSON as clob(1k)) )
       from sysibm.sysdummy1;

Good! Everything works in both the “old” and the new way! Great!

The aim of the project, however, was to translate into various languages and to store the translations in our “simple-sample” table in QTEMP: let’s do it … both with SYSTOOLS.HTTPPOSTCLOB and with QSYS2.HTTP_POST

-- Translate in English .... using HTTPOSTCLOB
update qtemp.FruitNames a 
 set nameEng= 
 (select translatedText from json_table(systools.httppostclob(
       faq400.GV_URL, 
       cast(faq400.GV_HEADER as clob(1k)), 
       cast(json_object('q' value JSON_ARRAY(a.nameITA),
                   'source' value 'it',
                   'target' value 'en',
                   'format' value 'text') as clob(10k))), 
       '$.data.translations' COLUMNS (translatedText varchar(30) path '$.translatedText')) 
       fetch first 1 rows only);

-- Translate in Spanish
update qtemp.FruitNames a 
 set nameEsp= 
 (select translatedText from json_table(systools.httppostclob(
       faq400.GV_URL, 
       cast(faq400.GV_HEADER as clob(1k)), 
       cast(json_object('q' value JSON_ARRAY(a.nameITA),
                   'source' value 'it',
                   'target' value 'es',
                   'format' value 'text') as clob(10k))), 
       '$.data.translations' COLUMNS (translatedText varchar(30) path '$.translatedText')) 
       fetch first 1 rows only);

-- Translate in German
update qtemp.FruitNames a 
 set nameGer= 
 (select translatedText from json_table(systools.httppostclob(
       faq400.GV_URL, 
       cast(faq400.GV_HEADER as clob(1k)), 
       cast(json_object('q' value JSON_ARRAY(a.nameITA),
                   'source' value 'it',
                   'target' value 'de',
                   'format' value 'text') as clob(10k))), 
       '$.data.translations' COLUMNS (translatedText varchar(30) path '$.translatedText')) 
       fetch first 1 rows only);


-- And Now in French ... with QSYS2.HTTP_POST
update qtemp.FruitNames a 
 set nameFRA= 
 (select translatedText from json_table(QSYS2.HTTP_POST(
       faq400.GV_URL, 
       cast(json_object('q' value JSON_ARRAY(a.nameITA),
                   'source' value 'it',
                   'target' value 'fr',
                   'format' value 'text') as clob(10k)),
       cast(faq400.GV_HEADERJSON as clob(1k))
       ), 
       '$.data.translations' COLUMNS (translatedText varchar(30) path '$.translatedText')
       ));       

OK, we have our amazing fruit table in all languages … fantastic … did you know that the Strawberry is called Erdbeere in German and the Pear, Poire in French?

No? That’s what these web services are for!

VERBOSE and response_http_header format (Edit post from 23/09/2021)

Using the new HTTP_GET and HTTP_POST functions in the VERBOSE version to intercept HTTP Status from the Response Header and other information, I also realized that IBM Labs has played another joke on us: The format of the Response Header is not a real JSON … it looks like it but it has the sign ‘=’ (Equals) instead of the sign ‘:’ (colon) between key and value (why?!). I think it’s an oversight and they’ll fix it with a PTF … but, in the meantime, let’s see how to handle it:

--- After setting my host RPG variabiles with URL, BODY and HEADER I'm ready to call web service
  exec sql
           select response_http_header, response_message
              into :responseheader :Null_ds,
                   :responsemsg :Null_ds
              from table(QSYS2.HTTP_POST_VERBOSE(:url,
               cast (:body as clob(10M)),
               cast (:HttpHeader as clob(3k)) ));  

-- Usually I prefer storing results in RPG host variables so I can add them to a log table
...

-- If we take a look at the response_http_header we can see something like a JSON but not a real JSON document (Equal(=) instead of colons(:))
{
	"HTTP_STATUS_CODE"= "400",
	"Cache-Control"= "no-cache",
	"Pragma"= "no-cache",
	"Content-Length"= "597",
	"Content-Type"= "text/plain; charset:utf-8",
	"Expires"= "-1",
	"Date"= "Thu, 23 Sep 2021 08:22:21 GMT"
}


-- So, if we need to parse this string we have to use SCANRPL and then JSON_TABLE

// Scan and replace '=' with ':' (is not the right way but is ok for me)
  responseheader=%scanrpl('=':':':responseheader);

// Now I can play with JSON_TABLE
   exec sql
      select http_status_code into :http_status_code :null_ds
          from json_table(:responseheader, '$' columns
          (HTTP_STATUS_CODE char(5)
            path '$.HTTP_STATUS_CODE')); 

Dislcaimer

No fruit has been mistreated for this post.

The aim was not to learn languages but to see how the new HTTP functions of the TR5 and TR11 work … thanks IBM (and next time, less jokes about parameters order).

Source of this post on Github Gist

The sources of all this post are available in this Gihub GiST:

https://gist.github.com/Faq400Git/19d3c1e48a220c25c142fa81bcd89a5c

--- Roberto De Pedrini Faq400.com