People who have used MySQL will be more help than I can be. The syntax for the JSON functions in MySQL is very different than the syntax for the JSON functions in DB2 for i.
Here are a few thoughts based on my experiences with JSON functions in DB2 for i. I hope some of it is useful for learning JSON functions in MySQL as well.
1. Yes, you should be able to pass JSON objects into a store procedure parameter.
* Two parameters example: {"parameter1":12345, "parameter2":"abcde"}
* Two records of data example. Each record is a member of an array of JSON objects: {"MYFILENAME":[{"field1":1234,"field2":"abcd","field3":20191213},{"field1":9876,"field2":"zyxw","field3":20191212}]}
1.3 As long as your stored procedure parameter is large enough, you can reuse the same stored procedure parameter to receive each of the above-on separate calls to the procedure, of course. You will need to switch to a CLOB type if you need more than 32,768 bytes. (32,768 bytes is not that big if you need to pass in a relatively large number of records with a lot of fields.)
2. Some JSON functions are designed to only retrieve a single key/value pair out of a JSON object. If you have two key/value pairs (e.g., {"parameter1":12345, "parameter2":"abcde"}), then you will need to use the JSON function twice to get each key/value pair.
3. The JSON_TABLE function allows you to retrieve multiple records from a JSON object in relational format. You can then loop through the records and process them. (JSON_TABLE would be unnecessary and overkill for a simple list of parameters like {"parameter1":12345, "parameter2":"abcde"}.)
4. JSON functions depend on being able to navigate the JSON schema (e.g., navigate to parameter2 in the JSON object {"parameter1":12345, "parameter2":"abcde"}).
4.1. This means you must use valid JSON. There are plenty of free websites that will validate your JSON objects. I use
https://jsonlint.com/.
4.2. This means you must be familiar with how the JSON functions in your database represent JSON schema. This can differ from database to database.
5. The JSON functions in DB2 for i do not like spaces in the keys of key/value pairs. At least, this is my experience with JSON_TABLE. {"First Name":Kelly} fails. {"FirstName":Kelly} works. This might not apply to JSON_TABLE in MySQL or other databases, but it's something to consider if everything else looks right and it's still failing.
Thanks,
Kelly Cookson
Senior Software Engineer II
Dot Foods, Inc.
217-773-4486 ext. 12676
www.dotfoods.com<
http://www.dotfoods.com/>
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steve Richter
Sent: Friday, December 13, 2019 1:03 PM
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: [WEB400] mysql drawback - stored procedure default parameters not allowed
here is my version. works in mysql 5.7
JSON_OBJECT is kind of a weird syntax. Can I just pass in a json
formatted string? {propName:'vlu', prop2:123}
call objectDefn_select( json_object('objName','e', 'objType', 'function'))
Create procedure objectDefn_select(
in inWhere json
)
Language SQL
BEGIN
declare inObjName varchar(256);
declare inObjType varchar(10);
set inObjName = coalesce(json_unquote(json_extract( inWhere ,
'$.objName')), '');
set inObjType = coalesce(json_unquote(json_extract( inWhere ,
'$.objType')), '');
select a.objName, a.databaseName, a.objType, a.object_id,
routine_exists( a.objName ) routineExists,
a.codeText
from ObjectDefn a
where ( inObjName = '' or lower(a.objName) like
concat('%',trim(lower(inObjname)), '%'))
and ( inObjType = '' or a.objType = inObjType ) ;
END
On Fri, Dec 13, 2019 at 12:26 PM Tim Fathers <X700-IX2J@xxxxxxxxxxx<mailto:X700-IX2J@xxxxxxxxxxx>> wrote:
I meant to add that this was the MariaDB syntax not MySQL, I have a
feeling it's slightly different between the two.
"The pattern I follow is to have a stored procedure for
each table in my application. cusms_select( ), orderHeader_select( ) ...
Then the input parms are used when selecting rows from the table. Which
works ok when only a few parms are used. Using a single JSON parameter
makes it less intrusive to add an additional WHERE parameter."
I'm doing something similar, except the SP implements a RESTful CRUD+
webservice over the file, with request and response information being
passed in and out as JSON objects and the request and response bodies as
BLOBs which are interpreted/generated according to the content-type/accepts
headers.
--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxxxxxxxx<mailto:WEB400@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/web400<
https://lists.midrange.com/mailman/listinfo/web400>
or email: WEB400-request@xxxxxxxxxxxxxxxxxx<mailto:WEB400-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/web400<
https://archive.midrange.com/web400>.
midrange.com
