RE: Help with SYSTOOLS.HTTPGETCLOB and systools.json2bson

Host variables used in embedded SQL need to be preceeded with a column!
Your insert Statement must look as follows:
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) );

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of john
art
Sent: Donnerstag, 23. November 2017 13:40
To: midrange-l@xxxxxxxxxxxx
Subject: 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&desti
nation=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;



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD