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.
It's quite easy to generate most of the boilerplate code for this from the SYSTABLES/SYSCOLUMNS tables (or whatever their called in MySQL). I use Angular Schematics for this.
Tim.
________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Steve Richter <stephenrichter@xxxxxxxxx>
Sent: 13 December 2019 18:15
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] mysql drawback - stored procedure default parameters not allowed
On Fri, Dec 13, 2019 at 11:53 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:
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 looks useful. 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.
--
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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C8e9a0a4410b24e3f04ed08d77ff00f9b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118541334382227&sdata=OMEKyoin3shFQ0SmE2z8rYqzT%2BMiZ11Y1rxWbt8RtIw%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C8e9a0a4410b24e3f04ed08d77ff00f9b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637118541334382227&sdata=xv%2B6rr5jjePJso45z374J6Fy0XsykQBaOFZ8H%2F96vgI%3D&reserved=0.
midrange.com
