Re: Validating JSON Data in a Host Variable

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