Re: data-into -- RPG400-L

Vern/Birgitta

The problem with the SQL approach is, as Scott mentioned, you don't know what the name of the element is. It is not just a question of mapping to a valid RPG name - I only suggested that earlier so that the OP could prove that the name was indeed the problem.

But this is a very odd document that appears to use an actual error code as a field name - so it is "fieldname": "error text" - where fieldname can be any error code. Normally you'd expect to see something like:
"error":"117", "text":"error text".

Scott's approach is I think the only way to handle it since even if the number were known there would probably be far too many possibilities to make it practical.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Jun 1, 2018, at 1:18 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Assumed the JSON document looks like this:

{"root":{"success":false,"errors":{"117":["Please provide a billing name","Additional Information"]},"masked_card_no:":"xxxxxxxxxxxx1234"}}

The following SQL Statement will return the Error textes located in the array:
Select *
from JSON_Table(YourJSONDocument,
'$.root.errors.117[*]'
Columns(Error VarChar(256) Path '$')) as x;

YouJSONDocument defines where your JSON document is.
It can be a sub-select if your JSON document is located within a SQL Table.
If the JSON document is located in the IFS use Get_CLOB_From_File('/home/IFSLocation') to get a access to the IFS file (attention must be executed under commitment control).
If the JSON document is returned from a web service use the HTTPGetCLOB scalar function located in the SYSTOOLS library for accessing the web service and to get the JSON data.

Since it is a regular SELECT-Statement it can be easily handled with embedded SQL.

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 Vernon Hamberg
Sent: Donnerstag, 31. Mai 2018 22:21
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: data-into

To push the SQL thing a little more - it appears that JSON_TABLE is like XMLTABLE, in that you can map an element name, whatever it is, to a valid column name - this would get around the issue with an element whose "name" is not something you can put into an RPG data structure.

Just sayin'!

Vern

On 5/31/2018 3:14 PM, Vernon Hamberg wrote:

There is always JSON_TABLE UDTF!

Jon, I am ducking! Still, I have found XMLTABLE to be more flexible
than the RPG alternatives and not much worse to use than a CREATE
TABLE statement.

I'm sure Birgitta will jump in on this! I've not had to use JSON_TABLE
yet, but it has a "lovely" syntax that is similar to XMLTABLE - your
favorite thing, I know!!

Vern

On 5/31/2018 1:15 PM, Jon Paris wrote:

I discussed this with Barbara Morris and Scott Klement today and the
only way to do it is with the YAJL "SAX" parser.

Alternatively to make it work with DATA into you would have to
convert any field named for the error code to a sequence - so you
would have to replace "1117" with something like "errorCode": "1117",
"errorText".

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On May 31, 2018, at 10:08 AM, Steve Jones <sjones@xxxxxxxxxxxxxxx>
wrote:

Great idea on changing the json. I changed the "117" to "who" &
fixed my DS (see below) & that does work. So I will have to parse
it myself.
Thanks for the help!!!

Dcl-ds result2 qualified;
success ind;
status_message varchar(500);
transaction_id varchar(16);
approval_message varchar(50);
Dcl-ds errors;
who varchar(50) Dim(10);
End-ds;
End-ds;

On Thu, May 31, 2018 at 9:37 AM Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

P.S. Unless I am misreading the JSON, 117 is the array - not errors.
Errors is just a DS.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On May 31, 2018, at 8:53 AM, Steve Jones <sjones@xxxxxxxxxxxxxxx>
wrote:

I did get the data-into to work on one of my json files, but I am
struggling with defining the DS on another.
It contains an array, but I can not get my DS populated with the
data.

This is the returned json from calling our credit card processors
API

when

the card has an error:
{"success":false,"response_code":1,"status_message":"One or more
errors

has

occurred.","errors":{"117":["Please provide a valid Billing
Name"]},"masked_card_number":"xxxxxxxxxxxx1234"}

The same API will return this file when the card is valid:
{"success":true,"response_code":101,"status_message":"Your
transaction

was

successfully

approved.","transaction_id":211035456,"approval_code":"DSC182","approval_message":"

NO MATCH - Approved and completed","avs_response":"No

Match","csc_response":"","external_transaction_id":"","masked_card_
number":"xxxxxxxxxxxx1234"}

My Data-into specifies allowmissing=yes allowextra=yes - I have it
able

to

parse each json file, but the one with the array I can not get it
to load the array portion. I am thinking it is because of the
"117" is not able

to

be matched. This also is potentially a different code depending on
the error.

Here is my DS

Dcl-ds result2 qualified;
success ind;
status_message varchar(500);
transaction_id varchar(16);
approval_message varchar(50);
Dcl-ds errors Dim(10);
Dcl-ds message;
message_text varchar(50);
End-ds;
End-ds;
End-ds;

Thank you for any help!!

On Wed, May 30, 2018 at 8:14 AM Slanina, John
<jslanina@xxxxxxxxxx>

wrote:

Ok, I see it not. It's an option in the %parser not %data.
Thanks Jon

Thanks
John Slanina

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Jon
Paris
Sent: Tuesday, May 29, 2018 5:11 PM
To: Rpg400 Rpg400-L <rpg400-l@xxxxxxxxxxxx>
Subject: Re: data-into

null values are handled by Scott's parser as best they can be John.

To handle them you specify the parser option value_null = value
(where value = *NULL by default. This special value will be
placed in the field when a null is encountered. Perhaps in the
future RPG will add the

ability

for DATA-INTO handlers to access the null flag of a null capable
field - but for now a special value is the only option.

Scott talks to this option and the other two initially supported
in this blog post

https://www.common.org/scotts-iland-blog/parsing-json-data-into/

<https://www.common.org/scotts-iland-blog/parsing-json-data-into/

where

you can also see an example of its use.

Since that was written he has added a couple more options but
I'll leave him to describe those.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On May 29, 2018, at 1:36 PM, Slanina, John <jslanina@xxxxxxxxxx>

wrote:

Jon,
I agree with what you're saying. I just have been dealing with
vendors that do not publish 100% of the JSON formats and they
love to

change them without telling anyone. Also a time saving when you're

dealing

with api with 100's of fields and you only need a couple values.

The count is cool I have to try that.

The one issue I did not email Scout about is when you have null
values.

I don't think YAJLINTO handles them.

John Slanina

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Jon
Paris
Sent: Tuesday, May 29, 2018 10:14 AM
To: Rpg400 Rpg400-L <rpg400-l@xxxxxxxxxxxx>
Subject: Re: data-into

Be VERY VERY cautious about using allowmissing=yes.

In general it is a very bad idea.

Reason is that there is no granularity - anything and EVERYTHING
could

be missing and DATA-INTO would simply say "Yup - all looks good
to me!".

Back in the mists of time with the initial release of XML-INTO

allowmissing was all we had - but we now have a better option

countprefix.

By using countprefix you can tell DATA-INTO exactly which fields are
optional and you have an easy way of detecting whether they are
present

or

not.

Using part of your DS as an example:

Dcl-ds billing_address;
name char(40);
street_address char(40);
city char(40);
state char(2);
zip char(10);
End-ds;

Assume that name and street_address are both expected but that the

other

fields are optional. You could add the option countprefix=count_ and

change

the definitions to:

Dcl-ds billing_address;
name char(40);
street_address char(40);
count_city int(5);
city char(40);
count_state int(5);
state char(2);
count_zip int(5);
zip char(10);
End-ds;

Now if city, state or zip are omitted, even without
allowmissing=yes,

no

error will be flagged and the relevant count_ fields will be 0 or 1
depending on the absence/presence of the field in question.

You can apply the same thinking to the name and street_address
fields

too and then test their count_xxxx fields to ensure that they were

present

and issue your own appropriate error if they were missing.

So - if your mother had known about you using allowmissing=yes
she'd

have told you to STOP IT before it damages you permanently!

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On May 29, 2018, at 8:42 AM, Steve Jones <sjones@xxxxxxxxxxxxxxx>

wrote:

After seeing Scott Klements session at Powerup18 on using Json
& the
new data into, I wanted to start using it.

I am getting the error
Message . . . . : The document for the DATA-INTO operation
does not
match
the RPG variable; reason code
5.
Cause . . . . . : While parsing a document for the DATA-INTO

operation,

the
parser found that the document does not correspond to RPG variable
"result"
and the options do not allow for this. The reason code is 5. The
exact subfield for which the error was detected is
"result.customers(1)".
The
options are "doc=file case=any countprefix=num_". The document
name
is /paytrace/889_output.json; *N indicates that the document
is not
an externa file. The parser is 'YAJLINTO'. *N indicates that the
parser is a procedure pointer.

So that leads me to believe my DS does not match, but I am not
seeing
where it is not matching, unless you need to define fields you are
not even going to use in the pgm.

Here is my DS:
Dcl-ds result qualified;
success ind;
response_code int(3);
status_message varchar(500);
num_customers int(10);

Dcl-ds customers dim(10);
Dcl-ds credit_card;
masked_number char(16);
expiration_month int(2);
expiration_year int(2);
End-ds;
Dcl-ds billing_address;
name char(40);
street_address char(40);
city char(40);
state char(2);
zip char(10);
End-ds;
email char(70);
End-ds;

End-ds;

Here is the raw json I am trying to read from the IFS:
{"success":true,"response_code":1,"status_message":"Your
request has
been successfully completed.","customers":[{"customer_id":"100-1
","credit_card":{"masked_number":"************1111","expiration_month"

:12,"expiration_year":20},"shipping_address":{"name":"","street_addre

s
s":"","street_address2":"","city":"","county":"","state":"","zip":"","

country":"US"},"billing_address":{"name":"Transfer
Order","street_address":"Internal Use

Only","street_address2":"","city":"Anywhere","state":"OH","zip":"44721","country":"US"},"email":"

testing@xxxxxxxxxx","phone":"","fax":"","created":{"at":"5/25/2018
7:26:13
AM","by":"demo123","from_ip":"x.x.x.x"},"discretionary_data":{"Site

IDs":"","Company Name":"","Location":""}}]}

The data-into is:
data-into result %Data(outFile
:'doc=file case=any countprefix=num_')
%PARSER('YAJLINTO');

Anyone see where I am missing what is causing the error?

Thanks!!
--
Steve Jones
H-P Products, Inc
330-871-2054

--
NOTE: The information in this email is confidential and may be
legally privileged. If you are not the intended recipient, you
must
not read, use or disseminate the information; please advise the
sender immediately by reply email and delete this message and any
attachments without retaining a copy. Although this email and any
attachments are believed to be free of any virus or other
defect that
may affect any computer system into which it is received and
opened,
it is the responsibility of the recipient to ensure that it is
virus
free and no responsibility is accepted by H-P Products, Inc.
for any

loss or damage arising in any way from its use.

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

--
Steve Jones
H-P Products, Inc
330-871-2054

--
NOTE: The information in this email is confidential and may be
legally
privileged. If you are not the intended recipient, you must not read,

use

or disseminate the information; please advise the sender
immediately by
reply email and delete this message and any attachments without

retaining a

copy. Although this email and any attachments are believed to be
free

of

any virus or other defect that may affect any computer system into
which

it

is received and opened, it is the responsibility of the recipient to

ensure

that it is virus free and no responsibility is accepted by H-P
Products,
Inc. for any loss or damage arising in any way from its use.
--
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

--
Steve Jones
H-P Products, Inc
330-871-2054

--
NOTE: The information in this email is confidential and may be legally
privileged. If you are not the intended recipient, you must not
read, use
or disseminate the information; please advise the sender immediately by
reply email and delete this message and any attachments without
retaining a
copy. Although this email and any attachments are believed to be
free of
any virus or other defect that may affect any computer system into
which it
is received and opened, it is the responsibility of the recipient to
ensure
that it is virus free and no responsibility is accepted by H-P
Products,
Inc. for any loss or damage arising in any way from its use.
--
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

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