Re: RPG program to consume a web service

[Booth Martin <booth@xxxxxxxxxxxx>]

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