RE: Help with SYSTOOLS.HTTPGETCLOB and systools.json2bson

Hi


ok i have followed your suggestion and modified the RPG , adding

" EXEC SQL

SET OPTION NAMING = *SYS,
CLOSQLCSR = *ENDMOD,
COMMIT = *NONE,
COMPILEOPT = 'DBGVIEW(*LIST)'; "

now , when i run my PGM in the log i see :
"SQL0206 column or global variable XSQL not found"

this my proc:

dcl-proc store_data ;

xSql = 'https://maps.googleapis.com/maps/api/directions/' +
'json?origin=Montreal&destination=Toronto&key=' +
'<MYKEY>' ;
exec sql
insert into qtemp.jsondata (
Select 'api_g' ,
'/tmp/',
systools.json2bson(json_info) as json_info
From (Values(SYSTOOLS.HTTPGETCLOB(
replace(trim(xSql),
' ','%20'),'')))
x(json_info) );

end-proc;

i can't insert a variable in the " From (Values(SYSTOOLS.HTTPGETCLOB.. "

thanks in advance




________________________________
Da: john art
Inviato: giovedì 23 novembre 2017 01:56
A: midrange-l@xxxxxxxxxxxx
Oggetto: RE: Help with SYSTOOLS.HTTPGETCLOB and systools.json2bson


Hi Peter thanks ,

i have already done it ..

if i run

"SELECT data FROM
(VALUES(SYSTOOLS.HTTPGETCLOB
('https://maps.googleapis.com/maps/api/directions/json?origin=Montreal&destination=Toronto&key=<MYKEY>',''))) WS(data);"

it works fine and i receive this result

{
"geocoded_waypoints" : [
{
"geocoder_status" : "OK",
"place_id" : "ChIJDbdkHFQayUwR7-8fITgxTmU",
"types" : [ "locality", "political" ]
},
{
"geocoder_status" : "OK",
"place_id" : "ChIJpTvG15DL1IkRd8S0KlBVNTI",
"types" : [ "locality", "political" ]
}
],
"routes" : [
{
"bounds" : {
"northeast" : {
"lat" : 45.5017123,
"lng" : -73.5645298
},
"southwest" : {
"lat" : 43.6520789,
"lng" : -79.3827656
}
},
"copyrights" : "Map data ©2017 Google",
"legs" : [
{
"distance" : {
"text" : "542 km",
"value" : 541620
},
"duration" : {
"text" : "5 hours 15 mins",
"value" : 18890
},
"end_address" : "Toronto, ON, Canada",
"end_location" : {
"lat" : 43.6533096,
"lng" : -79.3827656
.........



________________________________
Da: john art
Inviato: giovedì 23 novembre 2017 01:08
A: midrange-l@xxxxxxxxxxxx
Oggetto: Help with SYSTOOLS.HTTPGETCLOB and systools.json2bson


Hi all

I’m try to use systools.json2bson and SYSTOOLS.HTTPGETCLOB;

I write a test pgm like this, but the sql stamen seem not working .. in my qtemp.jsondata I don’t see anything



How can I see why this don’t work ? can I see/check SQL messages ?



If some one can help me, I would be gratefull



Thanks in advance



My procs :



dcl-proc store_data ;



xSql = 'https://maps.googleapis.com/maps/api/directions/' +

'json?origin=Montreal&destination=Toronto&key=' +

‘<MYKEY>’;

exec sql

insert into qtemp.jsondata (

Select 'api_g' ,

'/tmp/',

systools.json2bson(json_info) as json_info

From (Values(SYSTOOLS.HTTPGETCLOB(

replace(trim(xSql),

' ','%20'),'')))

x(json_info) );

end-proc;



DCL-PROC Create_Temp_Table;

exec sql

CREATE OR REPLACE TABLE QTEMP.JSONDATA

( FILE_NAME VARCHAR(64) CCSID 1144 NOT NULL,

IFS_COPY_PATH VARCHAR(256) CCSID 1144 NOT NULL,

JSON_INFO BLOB(64K) NOT NULL);

exec sql

delete from qtemp.jsondata

where file_name='api_g' ;

END-PROC;