Re: Validating JSON Data in a Host Variable

On 2/1/2018 2:11 PM, Craig Richards wrote:

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"

Are you connecting to Db2 for i?
I can't find either of those in the 7.3 reference.

I ran your code one million times on my 7.3 machine and didn't have an
issue. What OS version, cume, and DB group level is your machine?

Dcl-S jsonDoc VarChar( 128 ) Inz( '{"valid": "Yes I am"}' );
dcl-s result char(50);
dcl-s i int(10);


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

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


for i = 1 to 1000000;

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;

endfor;

*inlr = *on;

//============================================================================================
//‚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;

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