Re: [EXTERNAL] Re: Thank you

I have explored the JSON functions in developing IBM I SQL stored procedures. A stored procedure that uses the JSON functions to perform a SELECT statement directly into a CLOB parameter runs faster than a stored procedure that performs the same SELECT statement using a SQL cursor. You will do better speed-wise to use JSON than SQL cursors in stored procedures.

My experience is that the JSON functions did not like spaces in the names of name/value pairs. So "firstName": "Kelly" worked, but "first Name": "Kelly" did not work. It took me a while to figure this one out.

Yet, I'm not using the JSON functions. Why? If you need to pass back a few dozen rows of data that each have multiple columns, then you may need a CLOB parameter to hold the JSON string. The CLOB parameter worked for me when I called the stored procedure using the ACS Run SQL Scripts tool. But Microsoft .NET does not have a CLOB data type. It has a corresponding data type called LONGVARCHAR. When calling the stored procedure from a .NET app using the ACS ODBC driver, the call fails because of the data type mismatch. This is an issue of .NET and the IBMi not playing nice together...not anything wrong with the JSON functions or with using CLOB parameters per se. Nonetheless, it's still an obstacle that prevents me from using the JSON functions in SQL stored procedures.

Thanks,

Kelly Cookson
Senior Software Engineer II
Dot Foods, Inc.
217-773-4486 ext. 12676
www.dotfoods.com