Re: 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.