× 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. RPG400-L
  3. January 2020

Re: JSON_OBJECT and Writing to IFS

Thank you Birgitta. I definitely am set to CCSID 37

I declare the DBCLOB like this:
DCL-S OutFile SqlType(DBCLOB:256000) ccSid(1200) ;

but these lines say the variables are not defined
Outfile_Name = %trim('/tmp/test.json');
Outfile_NL = %len(%trimr(Outfile_Name));
Outfile_FO = SQFCRT;


On Thu, Jan 9, 2020 at 12:10 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

The example returns the JSON Document as a CLOB Variable, while you
defined a CLOB_FILE.
You may try it with a DBCLOB_FILE.

You are sure, your job environment is not CCSID 65535 or may be the data
in your table are CCSID 65535.

Try to explicitly cast your JSON Data to UTF-8 in SQL, but I assume it
will not work either. Data without CCSID (or CCSID 65535) cannot be
converted.

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@xxxxxxxxxxxxxxxxxx> On Behalf Of Art
Tostaine, Jr.
Sent: Donnerstag, 9. Januar 2020 17:58
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: JSON_OBJECT and Writing to IFS

I Found this link suggested changing to DBCLOB SQLType but now I Can't set
the _name, _nl, etc. They compile as undefined.


https://www.ibm.com/developerworks/community/forums/html/topic?id=5352adf0-9502-47e7-99a4-27124526243a


Thank you.

On Wed, Jan 8, 2020 at 3:24 PM Art Tostaine, Jr. <atostaine@xxxxxxxxx>
wrote:

Yes my job CCSID is 37.

On Wed, Jan 8, 2020 at 3:03 PM Bdietz400 <bdietz400@xxxxxxxxx> wrote:

Check your job’s CCSID. It should be 37 if in the US

--
Bryan Dietz

On Jan 8, 2020, at 12:35 PM, Art Tostaine, Jr.
<atostaine@xxxxxxxxx>
wrote:

I tried this and am getting two errors:

DCL-S OutFile SQLType(CLOB_FILE) ccSid(1208) ;
exec sql set option commit=*none, naming=*sys; Outfile_Name =
%trim('/tmp/mytest.json'); Outfile_NL =
%len(%trimr(Outfile_Name)); Outfile_FO = SQFCRT;.

Message ID . . . . . . : CPD4318 Severity . . . . . . . : 40

Message type . . . . . : Diagnostic

Date sent . . . . . . : 01/08/20 Time sent . . . . . . :
11:34:11


Message . . . . : Derived operands not valid for operator
JSON_OBJECT.

Reason code 12.

Cause . . . . . : Either the derived operands are not compatible
with
each
other or the operands are not compatible with operator JSON_OBJECT.
The

type of incompatibility is indicated by reason code 12. The
reason
codes

and their meanings follow:
12 -- The CCSIDs (Coded Character Set Identifiers) of the operands
cannot
be made compatible.

Even though my job CCSID and system CCSID is 37, I'm also getting this
Message ID . . . . . . : SQL0332 Severity . . . . . . . : 30

Message type . . . . . : Diagnostic

Date sent . . . . . . : 01/08/20 Time sent . . . . . . :
11:34:11


Message . . . . : Character conversion between CCSID 65535 and CCSID
1208
not valid.

Cause . . . . . : Character or graphic conversion has been attempted
for

data that is not compatible. There is no conversion defined
between CCSID
65535 and CCSID 1208.



On Wed, Jan 8, 2020 at 10:50 AM Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx>
wrote:

Select ... INTO can only handle a single return value/row!

In this way you should build the complete JSON document within a
single SELECT statement and write the result into the IFS.
For complex queries use common table expressions (CTE).
In the first (few) CTEs generate the raw data that must be
included in
the
JSON document.
In the next CTEs build successive the JSON data, starting with the
lowest
level and then merging the result of these CTEs in the next CTEs ...
until
the complete JSON document is built.

... on the other side it is possible to append data to an IFS table.
You have to change the _FO value to SQLAPP (=Append). After having
changed
the File Operation, new data is added to the end of the IFS file.

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@xxxxxxxxxxxxxxxxxx> On Behalf Of
Stephen
Piland
Sent: Mittwoch, 8. Januar 2020 16:06
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: JSON_OBJECT and Writing to IFS

Great! Thanks! That did work. It only allows me to fetch the
first
row
to
dump into the IFS file, which makes sense to me.

How could I include multiple JSON messages / 'rows'? Someone
approached us
about doing ND JSON, which might be what this would be. I'm not
sure
to be
honest.

Any thoughts?

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta
Hauser
Sent: Tuesday, January 7, 2020 11:23 PM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: JSON_OBJECT and Writing to IFS

Outfile must be a CLOB_FILE CCSID 1208.

DCL-S OutFle SQLType(CLOB_FILE) CCSID(1208);

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@xxxxxxxxxxxxxxxxxx> On Behalf Of
Stephen
Piland
Sent: Mittwoch, 8. Januar 2020 01:46
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: JSON_OBJECT and Writing to IFS

Anyone have any luck writing the encoded JSON out to the IFS like
we
have
done with XML using sqltype(xml_clob_File) and Select-ing into
that variable? Do I need to convert / cast the JSON_OBJECT into a
CLOB in
the
SQL? Thanks!

Quick Test...
dcl-s Outfile sqltype(xml_clob_File) ; exec sql Set Option
COMMIT=*NONE,CLOSQLCSR=*ENDMOD,DATFMT=*ISO;

// Prep JSON File
Outfile_Name = %trim('/temp/test.json'); Outfile_NL =
%len(%trimr(Outfile_Name)); Outfile_FO = SQFCRT;

exec sql Select json_object ('upc' value fldupc, 'price' value
price)
Into
:Outfile From PRICING;



--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe,
or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe,
or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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:
https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe,
or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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: https://amazon.midrange.com



--
Art Tostaine
--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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: https://amazon.midrange.com



--
Art Tostaine



--
Art Tostaine
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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: https://amazon.midrange.com




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.