Google Translator API and DB2 for i HTTP_POST HTTPPOSTCLOB.sql

-- Google Translator (API Key Roberto.depedrini@faq400.com)
create variable faq400.GV_GoogleAPIKey varchar(100) default('xxxxxxxxxxxxxxx'); -- YOUR API KEY Here!
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);


-- A simple Word table
drop table qtemp.FruitNames;

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 a few words
insert into qtemp.FruitNames (NameITA)
     values('Mela'),
           ('Pera'),
           ('Pesca'),
           ('Albicocca'),
           ('Banana'),
           ('Ciliegia'),
           ('Fragola'),
           ('Caco'),
           ('Castagna');

-- Build a JSON with an array of words ... 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 ;
                   
-- Set Global Variables URL, HEADER and BODY                   
set faq400.GV_URL='https://translation.googleapis.com/language/translate/v2?key=' concat faq400.GV_GoogleAPIKey;
set faq400.GV_HEADER='<httpHeader><header name="content-type" value="application/json"/></httpHeader>';

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 ) ;
                                      
-- Now Test Google API                
select systools.httppostclob(faq400.GV_URL,
       cast(faq400.GV_HEADER as clob(1k)),
       cast(faq400.GV_BODY   as clob(10k)) )
       from sysibm.sysdummy1; 
       
-- Let's try with HTTP_POST       
 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'
                   );
                   
       select qsys2.http_post(faq400.GV_URL,
       cast(faq400.GV_BODY   as clob(10k)),
       cast(faq400.GV_HEADERjson as clob(1k)) )
       from sysibm.sysdummy1; 

-- Let me understand
select * from json_table(systools.httppostclob(faq400.GV_URL, cast(faq400.GV_HEADER as clob(1k)), cast(faq400.GV_BODY   as clob(10k))), 
       '$.data.translations' COLUMNS (translatedText varchar(30) path '$.translatedText')) as x;

-- Update NameENG colum
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);

-- Update NameESP colum
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);
       
-- Update NameGER colum
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);

       
       
-- Update NameFRA colum 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')
       ));       

       
  --  Playing     
 

 select faq400.GV_HEADERJSON from sysibm.sysdummy1;  
 select faq400.GV_body from sysibm.sysdummy1;            

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; 

       
select * from table(QSYS2.HTTP_POST_VERBOSE(faq400.GV_URL,
         cast(faq400.GV_BODY   as clob(10k)),
         cast(faq400.GV_HEADERJSON as clob(1k))
               ));
       
       
 select * from qtemp.fruitnames;