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
midrange.com
