Re: [EXTERNAL] Re: mysql drawback - stored procedure default parameters not allowed

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

.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
list
To post a message email: WEB400@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/web400.