Re: Validating JSON Data in a Host Variable

I put both forms into subprocedures for a test and am now getting a problem
I don't quite understand.
I was getting this in earlier attempts.
There are 4 subprocedure calls below.
On the 3rd or 4th call ( it seems a bit random ), I'm getting SQLCODE 16402
and SQLSTATE 0169F
and the following message in the joblog:
"The pointer parameter passed to free or realloc is not valid"

Is there anything obviously wrong that I'm doing here?
Is it related to reusing the ODP?
The program is running in a named activation group and has close SQL cursor
*ENDACTGRP.

I suspect this will go away if i only use Birgitta's solution, but I don't
really understand what the problem is.

Am I being a great buffoon unwittingly?

Dcl-S jsonDoc VarChar( 128 ) Inz( '{"valid": "Yes I am"}' );


//============================================================================================
//‚ Mainline Code

//============================================================================================

Monitor;

If validJSON( jsonDoc );
Result = 'Nice One. Get the beers in';
EndIf;

If validJSON2( jsonDoc );
Result = 'Nice One. Get the beers in';
EndIf;

jsonDoc = 'Not so valid now are we...';

If Not validJSON( jsonDoc );
Result = 'Doh!';
EndIf;

If Not validJSON2( jsonDoc );
Result = 'Doh!';
EndIf;

...


//============================================================================================
//‚Validate the JSON Document using a Common Table Expression

//============================================================================================
Dcl-Proc validJSON;

Dcl-PI *N Ind;
JsonDoc VarChar( 128 ) Const;
End-PI;

Dcl-S Valid Ind;
Dcl-S SqlStm VarChar( 256 );

Exec Sql
With t1 ( json_String ) as
( Values( :JsonDoc ) )
Select
Case
When json_String is JSON OBJECT
then '1'
Else '0'
End as valid_JSON
Into :Valid
From t1;

Return Valid;

End-Proc validJSON;

//============================================================================================
//‚Validate the JSON Document WITHOUT using a Common Table Expression

//============================================================================================
Dcl-Proc validJSON2;

Dcl-PI *N Ind;
JsonDoc VarChar( 128 ) Const;
End-PI;

Dcl-S Valid Ind;
Dcl-S SqlStm VarChar( 256 );

Exec Sql
Set :Valid =
Case
When :jsonDoc is JSON OBJECT
then '1'
Else '0'
End;

Return Valid;

End-Proc validJSON2;

//============================================================================================

On 1 February 2018 at 18:39, Mike Jones <mike.jones.sysdev@xxxxxxxxx> wrote:

P.S. If you make a function, see if you can get it to work with a CLOB
input parameter. JSON can easily exceed 32740 in size.

On Thu, Feb 1, 2018 at 10:32 AM, 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