× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. December 2022

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

Hi Don,
Yes, unfortunately, I always try to attach images to these emails...

So, this is how the answer should look like if it were a JSON.

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

Meaning the answer you get is not a proper JSON and you are obliged to use
different techniques to read data from http answer.
Also, Roberto solution should be fine but I haven't tried it yet so I don't
know how it works when received data is an array, and that's why I
suggested you use both YAIL and DATA-INTO.
If you need more information, please just ask.

Il giorno mer 28 dic 2022 alle ore 23:48 Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> ha scritto:

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
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



--

Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
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




--
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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.