Re: mysql drawback - stored procedure default parameters not allowed

I don't know how you're calling your routines but maybe a workaround might be to use a single JSON object parameter which contains your actual parameters and then pull them out of that?

Something along these lines:

create or replace procedure MY_SP(
in PARMS json
)

begin

declare MY_PARM1 varchar(256);
declare MY_PARM2 varchar(256);

set MY_PARM1 = coalese(json_unquote(json_extract(PARMS , '$.myparm1')), 'default1');
set MY_PARM2 = coalese(json_unquote(json_extract(PARMS , '$.myparm2')), 'default2');

: : : : : : : :
end

call MY_SP(json_object('myparm1', 'Hello World!'))

This has the added benefit of being able to accept arrays and more complex objects which you can use for passing in filter lists or date ranges etc.

Tim.


________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Steve Richter <stephenrichter@xxxxxxxxx>
Sent: 13 December 2019 17:31
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: [WEB400] mysql drawback - stored procedure default parameters not allowed

initially I liked mysql. It supports mixed case column names. Has the
TEXT series of data types, which can store large text strings. Runs fast
on a linux PC and works well with node.js.

but MYSQL does not support procedure and function default parameters. ?
and no table functions?

no default parameters is a problem. Adding a parameter to a procedure
causes an error when I run exiting code that uses the procedure:
ER_SP_WRONG_NO_OF_ARGS: Incorrect number of arguments for PROCEDURE
steve.objectDefn_select; expected 2, got 0

I am using version 5.7 of mysql. Have not upgraded to newer version 8.
But not seeing much on google.

-Steve
--
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://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&amp;data=02%7C01%7C%7C4a9728f1c4fa41c1cc2708d77fe9ebfa%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118514962658654&amp;sdata=swtVToy6hZ8q4ltF4cN9cO6jd4%2FfNM3%2BfplrPTwE62I%3D&amp;reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&amp;data=02%7C01%7C%7C4a9728f1c4fa41c1cc2708d77fe9ebfa%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118514962658654&amp;sdata=OPg1Sfsb2lwtDALrLLJL6%2Br6lWB5uQK8uOUFH%2Bc56PI%3D&amp;reserved=0.