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

Hi Lucia

Thank you for your reply.

Unfortunately this list does not support images - what was it you were
trying to show me ?

Thanks

Don

From: "Maria Lucia Stoppa" <mlstoppa@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 28/12/2022 07:17 PM
Subject: Re: DATA-INTO - Different JSON depending on succeed or
fail
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

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

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

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