HTTPGETCLOB – JSON_VAL – Guida Step by Step (#IBMi, #AS400, #Faq400)

0
385

Come Faq400 stiamo ultimamente lavorando molto sui corsi di formazione, Rational Rdi, RPG Free, SQL ecc: lo scambio di opinioni ed esperienze tra docenti e partecipanti risulta sempre una ottima occasione di confronto e di crescita personale.

Ho raccolto un po’ di richieste durante i corsi su temi di interesse generale … cercherò nei prossimi post di creare del contenuto utile per questi argomenti.

Cominciamo con le funzionalità XML, JSON e WebServices che possiamo utilizzare direttamente da DB2 for i, ancora poco conosciute dagli sviluppatori IBMi / AS400 italiani, ma semplici e utili una volta che capiamo come usarli …

Ne abbiamo già parlato su Faq400, diverse volte … ad esempio nel post   Intergazione IBMi IBM Watson dove utilizziamo le funzionalità HTTPGETCLOB per utilizzare le API di IBM Watson … oppure nell’articolo IBMi e Bitcoins , anche in questo caso usiamo una API di un servizio web di cambi per recuperare il cambio attuale dei Bitcoins.

In questo esercizio vediamo invece come compilare i dati di geolocalizzazione dei nostri clienti tutto direttamente in SQL sfruttando le API di Google Maps

Se vuoi testare questi 10 passi direttamente dal tuo IBMi devi avere almeno una 7.1 con delle PTF aggiornate e utilizzare “Run SQL Scripts” di Rdi o del IBMi Navigator … lo STRSQL da grigio verde su alcune funzionalità HTTP non è molto felice …

 

Step 1 – Creazione di una tabella di work con i nostri clienti


-- Creazione di una tabella di Work
create or replace table faq400.clienti
( Codice char(10) NOT NULL,
RagioneSociale VARCHAR(50) NOT NULL,
Indirizzo VARCHAR(100) NOT NULL,
Cap char(10) NOT NULL,
Localita char(50) NOT NULL,
Provincia char(2) NOT NULL,
Nazione char(2) NOT NULL,
Latitudine decimal(21, 6) ,
Longitudine decimal (21, 6)
);

 

Step 2 – Inseriamo alcuni clienti di esempio

Come esercizio carico 4 aziende conosciute a livello internazionale …


-- Inseriamo qualche dato d'esempio
insert into faq400.clienti
values('000001', 'Barilla Spa', 'Via Mantova, 166', '43122', 'Parma', 'PR', 'IT', 0, 0);

insert into faq400.clienti
values('000002', 'Ferrari Spa', 'Via Abetone Inferiore, 4', '41053', 'Maranello', 'MO', 'IT', 0, 0);

insert into faq400.clienti
values('000003', 'Faq400 Srl', 'Via Manzoni, 37', '20900', 'Monza', 'MB', 'IT', 0, 0);

insert into faq400.clienti
values('000004', 'Nextev Srl', 'Via Cappuccini, 17', '23022', 'Chiavenna', 'SO', 'IT', 0, 0);

OK … ok mi sono un po’ montato la testa con le ultime due aziende  …

Step 3 Creiamo una tabella di archiviazione dei JSON di ritorno

Per evitare di chiamare tutte le volte una Api di Google Maps possiamo crearci una tabella dove andremo a salvare il JSON di ritorno dal servizio …, in questo caso salveremo il JSON dentro un campo BLOB del DB … Il contenuto del Blob potrà poi essere elaborato dai comandi JSON SQL oppure estratto come file di testo con le normali funzioni Blob e IFS del DB2 for i.


-- Creiamo una tabella di memorizzazione dei JSON di ritorno dalla API Google 
CREATE OR REPLACE TABLE faq400.geodata
( TipoOggetto varchar(64) CCSID 1144 NOT NULL,
Oggetto varchar(64) CCSID 1144 NOT NULL,
Descrizione varchar(64) CCSID 1144 NOT NULL,
DocumentoJSON BLOB(64K) NOT NULL);

 

Step 4 – Cerchiamo di capire la chiamata alla API di Google Maps

Ok, ora viene il bello … dobbiamo chiamare una api di Google Maps che ci ritorna varie informazioni sull’indirizzo passato … la chiamata alla API è di tipo Rest con i parametri passati nella path … semplice da testare direttamente dal vostro browser …

https://maps.googleapis.com/maps/api/geocode/json?address=Piazza+Duomo+1,20121+MIlano&components=country:IT

Questo servizio può tornare anche un xml se lo preferisci rispetto al JSON (ma perchè ?) sostituendo json? con xml? nell’url sopra…

 

Step 5 – Creiamo una UDF che costruisce l’url di chiamata alla API di Google Maps

Possiamo anche evitare di fare una UDF User Defined Function … ma essendo un esercizio didattico e essendo io un forte sostenitore delle UDF che rendono più chiaro e riutilizzabile il codice ecco qui una UDF che prende in ingresso Indirizzo, cap ecc e torna una URL nel formato di Google Maps … se un domani poi cambia qualcosa sistemiamo la UDF e i nostri statement SQL continueranno a funzionare a dovere …


CREATE OR REPLACE FUNCTION Faq400.GoogleMapsURL(
indirizzo varchar(100),
cap char(10),
localita char(50),
provincia char(2),
nazione char(2)
)
returns varchar(200)
LANGUAGE SQL
BEGIN
RETURN( trim(
'https://maps.googleapis.com/maps/api/geocode/json?address='
concat trim(Indirizzo) concat ','
concat trim(Cap) concat '+'
concat trim(localita) concat
'&components=country:' concat trim(Nazione)) );
END
;

 

Step 6 – Chiamamo la API e memorizziamo i nostri JSON nella GeoData table

Abbiamo la UDF che costruisce la URL … non ci resta che passare i nostri clienti, chiamare la API e memorizzarne il JSON di uscita nel Blob Field della tabella di appoggio GeoData


insert into faq400.geodata (
Select 'CLI', codice, RagioneSociale,
systools.json2bson(SYSTOOLS.HTTPGETCLOB(
replace(faq400.GoogleMapsUrl(indirizzo, cap, localita, provincia, nazione),' ','%20')
,'')) from faq400.clienti
)
;

 

Step 7 – Controlliamo il contenuto della GeoData
Possiamo ora controllare il contenuto della tabella GeoData … teniamo presente che il JSON è stato registrato in un campo Blob … quindi il suo contenuto è binario non testo …

select * from faq400.geodata;


Step 8 – Leggiamo il JSON estraendone i dati con JSON_VAL

La funzione JSON_VAL parte da un JSON contenuto in un Blob Field ed estrae il dato JSON in modo molto semplice … noi, che siamo i soliti fortunati, abbiamo proprio un JSON salvato in un Blob quindi possiamo testarne le funzionalità con una semplice select …


select Oggetto, Descrizione,
JSON_VAL(DocumentoJSON,’results.formatted_address’,’s’) AS Address ,
JSON_VAL(DocumentoJSON,’results.geometry.location.lat’,’f’) AS Lat,
JSON_VAL(DocumentoJSON,’results.geometry.location.lng’,’f’) AS Lon
from faq400.geodata
where TipoOggetto=’CLI’


 

Step 9 – Aggiorniamo con i dati nella tabella clienti 

A questo punto aggiorniamo la nostra tabella clienti con i dati di geolocalizzazione :


update faq400.clienti c
set latitudine = (
select JSON_VAL(DocumentoJSON,'results.geometry.location.lat','f') AS Lat
from faq400.geodata where TipoOggetto='CLI' and Oggetto=c.codice),
longitudine = (
select JSON_VAL(DocumentoJSON,'results.geometry.location.lng','f') AS Lon
from faq400.geodata where TipoOggetto='CLI' and Oggetto=c.codice)
where exists 
(select *
from faq400.geodata
where TipoOggetto='CLI' and Oggetto=c.codice);

 

Step 10 – Controlliamo il risultato


select * from faq400.clienti;

 

Conclusione

Già !, siamo alla conclusione … in soli 10 passi e poche istruzioni SQL abbiamo geolocalizzato i nostri clienti … per oggi possiamo essere contenti !