Re: Validating JSON Data in a Host Variable

[Mike Jones <mike.jones.sysdev@xxxxxxxxx>]

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