Re: Validating JSON Data in a Host Variable

[DFreinkel <dfreinkel@xxxxxxxxxxxxxxxxx>]

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