JSON_OBJECT will (presumably) make sure everything is properly escaped (in case you have have any reserved characters such as ", ' etc.) in your data and it won't quote numeric values. Personally I'd stick with it to avoid any potential pitfalls.
________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Steve Richter <stephenrichter@xxxxxxxxx>
Sent: 13 December 2019 20:02
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: 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> 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
To subscribe, unsubscribe, or change list options,
visit:
https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C08914d2457cc4f89f75c08d77fff1c7a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118605972158222&sdata=lIyJ1Oyu3VvEVwHQ%2F5h%2F5iUppScQErIEYWmbwr2u1S8%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C08914d2457cc4f89f75c08d77fff1c7a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118605972158222&sdata=gv%2Ftpq%2BIz8xgI3eHogYdakqhTfx0Guj%2F1At2ilEnMwg%3D&reserved=0.
midrange.com
