Re: mysql drawback - stored procedure default parameters not allowed

[Steve Richter <stephenrichter@xxxxxxxxx>]

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