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



If you are dealing with the CLOB as a Host Variable, you will probably need
to define it as an SQLType:

Dcl-S OutMessage SQLType( CLOB : 2500000 );

You can use that Host Variable in the SQL that we have been discussing.

If it's in a CLOB in the Database you can validate it there as I posted
originally:


Exec Sql
Select
Case
When t1.RqsText is JSON OBJECT then '1'
Else '0'
End
into :WellFormed
From SomeFile t1
where t1.ID = :Ctl_ID;

regards,
Craig

On 2 February 2018 at 14:17, DFreinkel <dfreinkel@xxxxxxxxxxxxxxxxx> wrote:

How would you do this if the object is large and put into a CLOB?

Darryl Freinkel
iPad

On Feb 1, 2018, at 8:32 PM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi Craig,

I've not yet tinkered with the DB2 JSON functionality...

It may be as easy as:

exec sql

set :isJson = char( case when :textThatMayBeJson is JSON OBJECT then '1'
else
'0' end, 1 )
;

Alternative, using an SQL scalar function:

create function IS_JSON ( IN_TEXT varchar( 32740 ) )

returns char( 1 )

return

char( case
when IN_TEXT is JSON OBJECT then '1'
else '0'
end, 1 )
;


Then, in RPG:

exec sql

set :isJson = IS_JSON( :textThatMayBeJson )
;

An SQL function like that could also be easily embedded into SQL queries,
procedures, and other functions.

Mike

On Thu, Feb 1, 2018 at 12:51 AM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Hi All,

It's easy enough to validate data in DB2 columns to check if it's valid
JSON ( well formed I suppose I should say ) using code like:

Exec Sql
Select
Case
When t1.RqsText is JSON OBJECT then '1'
Else '0'
End
into :WellFormed
From SomeFile t1
where t1.ID = :Ctl_ID;

But if you just have the document in a Host Variable, is there an easy
way
to validate it?
I'd rather stick to using the DB2 new JSON functions than start using
YAJL.

I can see how I can could so something like this:

SqlStm = 'With t1 as ( Select ''' + jsonDoc + ''' as doc from
SysIbm/SysDummy1 ) '
+ 'Select Case When doc is JSON OBJECT then ''1'' Else ''0''
End
'
+ 'From t1';

And then select the result of that into a Host Variable, but it seems a
bad
solution and is problematical anyway because I'm wrapping the value for
the
host variable in quotes so I'd need to check for embedded quotes and
double
them up which means I'm messing with the incoming document.

Is there an obvious way I'm missing that you can, in an SQLRPGLE
program,
check if a host variable contains well-formed JSON?

thanks in advance,
Craig


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


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.