02 - Database DB2 for i02a - SQL

Nuove QSYS2.HTTP Functions SQL (7.4 TR5 e 7.3 TR11)

Last Updated on 23 Settembre 2021 by Roberto De Pedrini

Da qualche giorno sono stati presentati i nuovi Technology Refresh TR4 per la 7.4 e TR11 per la 7.3 del sistema operativo IBM i

Tra le varie novità ci sono le nuove funzioni HTTP nella QSYS2, basate sulle IWS Transport API e non più su Java … con notevoli miglioramenti di prestazioni e di gestione delle possibilità nel consumo di servizi HTTPS SSL.

Se hai già utilizzato HTTPGETCLOB, HTTPPOSTCLOB o le loro versioni VERBOSE il passaggio alle nuove funzioni QSYS2-HTTP_ ti risulterà molto facile (se presti la dovuta attenzione alla posizione dei parametri, che quei burloni del laboratorio di Rochester hanno pensato bene di invertirli (vedi sotto!).

Se, invece, non hai ancora litigato con le funzioni HTTP di SQL, le nuove versioni ti eviteranno qualche parolaccia, perché hanno una migliore gestione degli errori.

Consumo di un REST web service con metodo GET senza errori:

Vediamo un semplice esempio di consumo di un Web Service REST in GET: SYSTOOLS.HTTPGETCLOB e QSYS2.HTTP_GET, non sono molto differenti … basta chiamare la function corretta:

-- 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;

Consumo di un REST in GET con errori (esempio 404-Not found)

Se non ci sono errori la cosa è molto semplice … se invece iniziamo a sbagliare qualcosa nel richiamo del web service e questo ci restituisce degli errori HTTP (4xx o 5xx), le cose diventano un po’ più complicate con SYSTOOLS.HTTPGETCLOB.

Se proviamo a chiamare dalla gestione degli Script SQL di ACS, un Web Service che torna, ad esempio, un errore 404-Not found (come una pagina web che non esiste), HTTPGETCLOB va in errore e viene presentata una finestra di Popup con l’errore stesso … un errore generale di Java che potrebbe significare tutto e niente.

Se vogliamo evitare questo messaggio d’errore possiamo ricorrere alla funzione SYSTOOLS.HTTPGETCLOBVERBOSE, che almeno inizia a tornare l’HTTPHeader di risposta dal Web Service e, con un po’ di attenzione sull’Header della richiesta, possiamo anche vedere la response message (dalla quale, generalmente, possiamo capire il vero motivo dell’errore).

Vediamolo passo-passo:

-- 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 con metodo POST: SYSTOOLS.HTTPPOSTCLOB vs QSYS2.HTTP_POST

I sorgenti di questa parte li trovi in questo Github Gist;

Quando dobbiamo invece consumare dei REST Web Services in POST (o in PUT), le cose diventano un attimino più complicate perché dobbiamo passare il body (payload) del messaggio, in JSON e, spesso, anche delle impostazioni particolari dell’Header HTTP.

Per prendere un esempio di richiamo di un Web Service Rest in POST, “rubo” al mio collega Marco Balgera, un suo script per la traduzione con le API di Google Translator … naturalmente mi sono prima aperto un mio account sulla Google Cloud Platform per ottenere le apposite API-KEY da utilizzare nelle chiamate ai Web Service (già che rubavo l’idea … potevo rubare anche la sua API-KEY … effettivamente! … sono un ladro onesto!).

Il Web Service che andremo a chiamare è uno dei tanti messi a disposizione da Google nella Cloud Console… se riusciamo ad utilizzare questo servizio per la traduzione, non dovremmo avere problemi anche con gli altri web service Google!

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

Per semplificare la sintassi degli esempi che riporto qui sotto, costruisco prima delle Global Variable SQL (possiamo pensarle come delle specie di Data-Area) con i testi JSON o XML che ci serviranno per i web service …

Anche la mia API-KEY la memorizzo in una Global Variable … se la dovessi cambiare … cambio solo il valore di Default della mia variabile e tutti gli statement SQL continuano a funzionare.

-- 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);

Creo una piccola tabella in QTEMP con i nomi di alcuni frutti … obiettivo del progetto è quello di tradurre quei nomi in diverse lingue e aggiornare di conseguenza la tabella in QTEMP

-- 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');

Alla API di Google per la traduzione, come spiegato nella documentazione della funzione stessa, dobbiamo passare un JSON con un array di parole o frasi e i riferimenti alle lingue sorgente e destinazione.

Provo quindi a creare un JSON come voluto dalla API di Google:

-- 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"
}

Non mi resta che mettere tutto insieme e provare veramente il servizio di traduzione:

-- 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 funziona … dopotutto lo aveva già provato il mio collega Marco … non avevo dubbi!

Vediamo se funziona anche con QSYS2.HTTP_POST … ma, attenzione … non è sufficiente cambiare il nome della Function SQL, dobbiamo prestare attenzione all’ordine dei parametri e all’Header, che non è più un XML ma un JSON: quei burloni di Rochester ci hanno fatto questo scherzo!

-- 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;

Bene! Funziona tutto sia nel “vecchio” modo che in quello nuovo! Ottimo!

L’obiettivo del progetto era però quello di tradurre in varie lingue e di memorizzare le traduzioni nella nostra “simple-sample” table in QTEMP: facciamolo … sia con SYSTOOLS.HTTPPOSTCLOB che con 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, abbiamo la nostra stupenda tabella dei frutti in tutte le lingue … fantastico … lo sapevate che la Fragola si chiama Erdbeere in tedesco e la Pera Poire in francese?

No? Ecco a cosa servono questi web services!

VERBOSE e formato response_http_header (Modifica post del 23/09/2021)

Utilizzando le nuove funzioni HTTP_GET e HTTP_POST nella versione VERBOSE per intereccare HTTP Status dall’Header della risposta e altre informazioni, mi sono anche accorto che i Laboratori IBM ci hanno fatto un altro scherzo: Il formato dell’Header di risposta non è un vero JSON … assomiglia ma ha il segno ‘=’ (Uguale) invece del segno ‘:’ (Due Punti) tra chiave e valore (ma perchè?!). Credo che sia una svista e che la sistemino con una PTF … ma, intanto, vediamo come gestirla:

--- Dopo aver messo nelle variabili HOST RPG URL, Body e Header della chiamata, chiamo il web service 
vero e proprio
  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)) ));  

-- Per comodità tengo a memorizzarmi responsemsg e responseheader per loggarli in una tabela di log
...

-- Come dicevo nel post, il Response Header che mi viene tornato non è un vero e proprio JSON ma una cosa di questo tipo (caso con errore HTTP)
{
	"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"
}


-- Quindi, se voglio farne il parsing, devo fare

// Cambio '=' con ':'
  responseheader=%scanrpl('=':':':responseheader);

// Quindi con JSON_TABLE ottengo quello che i serve
   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

Per questo post non è stato maltrattato nessun frutto.

Lo scopo non era quello di imparare le lingue ma quello di vedere come funzionano le nuove functions HTTP della TR5 e TR11 … grazie IBM (e la prossima volta, meno scherzi sull’ordine dei parametri).

Sorgenti del post su Github Gist

I source ti tutto questo post sono disponibili in questo GiST di Gihub :

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

Related Posts
DB2 for i SQL – Stringhe – POSSTR-LOCATE-LOCATE_IN_STRING (IT)

Introduzione Spesso, nelle nostre applicazioni, abbiamo la necessità di lavorare con le stringhe di testo e l'SQL del DB2 può Read more

DB2 for i & SQL – FAQ & Howto (Part. 1) (IT)

Database DB2 e SQL ... forse lo strumento più potente e completo che abbiamo sulla piattaforma IBM i: ecco una Read more

Annuncio IBM i 7.4

Arriva direttamente con l'uovo di Pasqua questo annuncio IBM per le novità della versione IBM i 7.4, versione iNext secondo Read more

Generated Always Columns – Approfondimenti (IT)

Introduzione "Generated Always Column": sono colonne, campi, di una tabella il cui contenuto è controllato direttamente dal sistema ... e Read more

--- Roberto De Pedrini Faq400.com
About author

Founder di Faq400 Srl, IBM Champion, ideatore del sito Faq400.com e del Blog blog.faq400.com. Sviluppatore RPG da quando avevo i pantaloni corti, forte sostenitore della piattaforma IBM i (ex AS400), ho sempre cercato di convididere le mie conoscenze con gli altri tramite forum, eventi e corsi. Oggi, tramite Faq400 Srl, cerchiamo di aiutare le aziende a sfruttare al meglio questa fantastica piattaforma IBM i.

3 Comments

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *