Re: RPG program to consume a web service

I am getting an error:

Character conversion between CCSID 65535 and CCSID 1200 not valid.
SQLCODE = -332

??


On 8/18/2018 6:56 AM, Birgitta Hauser wrote:

Just use the SQL Functions HTTPGETCLOB (in Schema SYSTOOLS) in

composition with the XMLTABLE or JSON_TABLE Table functions, depending on
whether XML or JSON is returned, in embedded SQL

Define a cursor or a SELECT ... INTO or a SET Statement to receive the

data.

In the following example a webservice is accessed with the SQL functions

and the result it read into a CLOB variable.

Since a CLOB variable is a regular RPG variable, you can use tools like

YAJL for consuming the JSON data.

DCL-DS PGMSDS PSDS Qualified;
MsgTxt Char(80) Pos(91);
MsgTxtShort Char(50) Pos(91);
End-Ds;

DCL-S GblCode VarChar(25);
Dcl-S GblName VarChar(25);

DCL-S GblErrMsg VarChar(50);
DCL-S GblDocNode Like(YAJL_Val);
DCL-S GblArrNode Like(YaJL_Val);

DCL-S GblIndex Int(10);

DCL-S GblClob SQLTYPE(CLOB: 1000000);

//*******************************************************************************************

Exec SQL Set Option DatFmt=*ISO, Naming=*SYS,

CLOSQLCSR=*ENDACTGRP;

//*******************************************************************************************

*INLR = *ON;
Monitor;
Exec SQL
Set :GblClob = SysTools.httpGetClob(
'https://pkgstore.datahub.io/core/'

concat

'country-list/latest/data/json/data.json', '');

If SQLCODE < *Zeros;
Exec SQL Get Diagnostics Condition 1 :GblErrMsg =

MESSAGE_TEXT;

Dsply GblErrMsg;
Return;
EndIf;

GblDocNode = yajl_buf_load_tree(%Addr(GblClob_Data):

GblClob_Len:

GblErrMsg);

DoW YAJL_Array_Loop(GblDocNode: GblIndex: GblArrNode);
GblCode = Yajl_Get_String(Yajl_Object_Find(GblArrNode:

'Code'));

GblName = Yajl_Get_String(Yajl_Object_Find(GblArrNode:

'Name'));

Dsply (%Trim(GblCode) + ', ' + %Trim(GblName));
EndDo;

On-Error;
Dsply PGMSDS.MsgTxtShort;
EndMon;

yajl_tree_free(GblDocNode);
Return;

In the next example multiple data is read from a webservice with

embedded SQL:

DCL-S LocURL VarChar(4096) CCSID(1208);

//---------------------------------------------------------------------------------------------

Monitor;

LocURL = 'https://....&api_key=' + APIKEY + '&.....';

Exec SQL
Declare LocCsr01 as
Select x.*
from JSON_TABLE(SysTools.HTTPGETCLOB(:LocURL, ''),
...
Columns(...)) x;

Exec SQL Close LocCsr01;
// SQLCODE or SQLSTATE can be checked here

Exec SQL Open LocCsr01;
// SQLCODE or SQLSTATE can be checked here

DoW ...
Exec SQL Fetch Next From LocCsr01 into :LocDSCsr01;
....
EndDo;

Exec SQL Close LocCsr01;

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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Booth Martin
Sent: Samstag, 18. August 2018 02:27
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Subject: RPG program to consume a web service

I have looked for a simple RPG program that consumes a web service. It's

not gone well. Either the examples are way to complex for my "Hello World"
kind of mind, or they are incomplete snippets that I can't figure out how
to use.

--
Booth Martin
www.martinvt.com
(870)576-9371
(802)461-5349

Common sense is the collection of prejudices acquired by age eighteen.
-- Albert Einstein
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)

mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://archive.midrange.com/rpg400-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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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