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.
Senior Software Engineer II
Dot Foods, Inc.
217-773-4486 ext. 12676
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact