Re: DATA-INTO - Different JSON depending on succeed or fail

Hi Don,

My five cents.
Assuming the answer received when the request is fulfilled is not JSON
formatted, as in this case it should look like this:
[image: image.png]

and alongside all the other answers you have already gotten, I think I
would have used SQL or YAIL to get errors if any, and if there is no errors
then DATA-INTO to get the data. I'd rather use DATA-INTO as it can return
the number of elements in the array.

Have a great Christmas time
Lucia


Il giorno lun 26 dic 2022 alle ore 19:01 Roberto De Pedrini <
roberto.faq400@xxxxxxxxx> ha scritto:

Hi Don,
I didn't tried with DATA-INTO but I setup two simple mockups (using Postman
Mockup Server) for two web API, one for your "error" response and one for
your "successful" response and tried to consume them with SQL and JSON
Table considering all the possible "JSON fields".
You could use this SQL statement embedded in your RPG and check for error o
right data (pay attention to null value or use coalesce in the SQL
statement).

testAPI01: mockup for error result

select * from json_table(QSYS2.HTTP_GET('
https://57940695-f184-451e-bd1e-40ef26e634a0.mock.pstmn.io/testAPI01
','{"sslTolerate":"true"}'),
'$' COLUMNS (
"responseType" varchar(30) PATH '$.responseType' ,
"responseTime" float PATH '$.responseTime',
"responseCode" integer PATH '$.responseCode',
"errorCode" integer PATH '$.errorCode' ,
"message" varchar(100) PATH '$.message',
"RequestedDate" date PATH '$.RequestedDate' )
) as X;

testAPI02: mockup for right results

select * from json_table(QSYS2.HTTP_GET('
https://57940695-f184-451e-bd1e-40ef26e634a0.mock.pstmn.io/testAPI02
','{"sslTolerate":"true"}'),
'$' COLUMNS (
"responseType" varchar(30) PATH '$.responseType' ,
"responseTime" float PATH '$.responseTime',
"responseCode" integer PATH '$.responseCode',
"errorCode" integer PATH '$.errorCode' ,
"message" varchar(100) PATH '$.message',
"RequestedDate" date PATH '$.RequestedDate' )
) as X;


If you aren't su up-to-date with iOS version (7.4 or 7.5) you can use "old"
HTTPGETCLOB SQL functions too

HTH
Roberto


select * from json_table(SYSTOOLS.HTTPGETCLOB('
https://57940695-f184-451e-bd1e-40ef26e634a0.mock.pstmn.io/testAPI01',''),
'$' COLUMNS (
"responseType" varchar(30) PATH '$.responseType' ,
"responseTime" float PATH '$.responseTime',
"responseCode" integer PATH '$.responseCode',
"errorCode" integer PATH '$.errorCode' ,
"message" varchar(100) PATH '$.message',
"RequestedDate" date PATH '$.RequestedDate' )
) as X;

select * from json_table(SYSTOOLS.HTTPGETCLOB('
https://57940695-f184-451e-bd1e-40ef26e634a0.mock.pstmn.io/testAPI02',''),
'$' COLUMNS (
"responseType" varchar(30) PATH '$.responseType' ,
"responseTime" float PATH '$.responseTime',
"responseCode" integer PATH '$.responseCode',
"errorCode" integer PATH '$.errorCode' ,
"message" varchar(100) PATH '$.message',
"RequestedDate" date PATH '$.RequestedDate' )
) as X;

Il giorno lun 19 dic 2022 alle ore 05:44 Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> ha scritto:

Hi

We are dealing with a new provider and are consuming their service.

They reply with two completely different JSON formats depending if the
request is successful or not. And neither are named.

For example

if error found on the request , then the return data provides the

response

details

{
"responseType": "executeQuery",
"responseTime": 1669597839789,
"responseCode": 104,
"errorCode": 0,
"message": "Parameter Error: Custom Report Exception: Parameter
2022-11-10 :00:00 and 2022-11-10 23:59:59 must be timestamps in the

format

YYYY-DD-MM HH:MM:SS"
}

Whereas if the request is successful we get an array of data

[
{ "RequestedDate":"2022-11-10", ....... },
{ "RequestedDate":"2022-11-11", ....... },
{ ... }
]


So what we are doing is attempting a DATA-INTO into the successful data
structure and if that fails attempting to load into the response data
structure.

While this works I was just wondering if there is a better option to be
able to determine which response received and therefore retrieve into the
respective data structure rather than the trial and error approach


Thanks for any suggestions

Don



--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email,

MSD

cannot accept responsibility for any loss or damage arising from the use

of

this email or attachments..
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
--
Roberto De Pedrini - Faq400 Srl
http://blog.faq400.com/en *A new IBM i Blog from IBM i Italian
Community
Faq400*
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com