Menu
▾
▴
firebird-devel
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 13:19:32
|
Hi!
EXECUTE PROCEDURE is full of weirdness, so I propose that standard SQL
CALL is adapted for our needs.
EXECUTE PROCEDURE [ <package> . ] <name>
[ <value list> | ( <value list> ) ]
[ RETURNING_VALUES <variable list> |
RETURNING_VALUES ( <variable list> ) ]
It does not allow one to "select" what just it wants.
So if one changes the procedure output parameters, clients (DSQL and
PSQL) needs to be changed. It's like "SELECT *" which is sure a bad
practice.
It has this weird RETURNING_VALUES and multiple syntax about parenthesis.
It does not allow to just execute and ignore output parameters.
I propose that CALL syntax:
CALL [ <package> . ] <name> ( <value list> )
[ RETURNING { * | <value list> } ]
[ INTO <variable list> ]
A CALL without RETURNING and without INTO will execute the procedure and
ignore possible output parameters.
A CALL without RETURNING and with INTO (PSQL only), works like with
"RETURNING *" and EXECUTE PROCEDURE ... RETURNING_VALUES.
Examples:
-- Like EXECUTE PROCEDURE in PSQL
CALL proc(1, 2) INTO v1, v2
-- Can ignore some output parameters in PSQL
CALL proc(1, 2) RETURNING o1 INTO a1
-- Can ignore some output parameters in DSQL
CALL proc(1, 2) RETURNING o1
-- Ignore all output parameters in DSQL and PSQL
CALL proc(1, 2)
Adriano
|
|
From: Dimitry S. <sd...@ib...> - 2021-05-31 13:50:51
|
31.05.2021 15:19, Adriano dos Santos Fernandes wrote: > I propose that standard SQL > CALL is adapted for our needs. Your proposed syntax is far from standard. I see no point in using standard keyword with non-standard syntax. -- WBR, SD. |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 14:30:07
|
On 31/05/2021 10:50, Dimitry Sibiryakov wrote: > 31.05.2021 15:19, Adriano dos Santos Fernandes wrote: >> I propose that standard SQL >> CALL is adapted for our needs. > > Your proposed syntax is far from standard. I see no point in using > standard keyword with non-standard syntax. > Standard procedures are far different from Firebird. Note that standard allows named arguments with "=>" and I don't put it here, because this is a general enhancement that IMO should be done also in function calls, for example. Also, RETURNING is not standard and we added it to others statements. Adriano |
|
From: Alex P. <pes...@ma...> - 2021-05-31 14:19:19
|
On 5/31/21 4:50 PM, Dimitry Sibiryakov wrote: > 31.05.2021 15:19, Adriano dos Santos Fernandes wrote: >> I propose that standard SQL >> CALL is adapted for our needs. > > Your proposed syntax is far from standard. I see no point in using > standard keyword with non-standard syntax. > Dimitry, can you provide standard syntax for others to compare? |
|
From: Vlad K. <hv...@op...> - 2021-05-31 14:39:56
|
31.05.2021 16:19, Adriano dos Santos Fernandes wrote:
> Hi!
>
> EXECUTE PROCEDURE is full of weirdness, so I propose that standard SQL
> CALL is adapted for our needs.
>
> EXECUTE PROCEDURE [ <package> . ] <name>
> [ <value list> | ( <value list> ) ]
> [ RETURNING_VALUES <variable list> |
> RETURNING_VALUES ( <variable list> ) ]
>
> It does not allow one to "select" what just it wants.
>
> So if one changes the procedure output parameters, clients (DSQL and
> PSQL) needs to be changed. It's like "SELECT *" which is sure a bad
> practice.
>
> It has this weird RETURNING_VALUES and multiple syntax about parenthesis.
>
> It does not allow to just execute and ignore output parameters.
As I see, all problems above is related with RETURNING_VALUES clause, correct ?
> I propose that CALL syntax:
>
> CALL [ <package> . ] <name> ( <value list> )
> [ RETURNING { * | <value list> } ]
> [ INTO <variable list> ]
>
> A CALL without RETURNING and without INTO will execute the procedure and
> ignore possible output parameters.
>
> A CALL without RETURNING and with INTO (PSQL only), works like with
> "RETURNING *" and EXECUTE PROCEDURE ... RETURNING_VALUES.
If "CALL" is not accepted (because of non-standard syntax), why not add "correct"
RETURNING clause into EXECUTE PROCEDURE ? Leave old RETURNING_VALUES clause as is for
compatibility and allow to use new good RETURNING clause instead.
Just my 0.02 uah
Regards,
Vlad
|
|
From: Dimitry S. <sd...@ib...> - 2021-05-31 14:49:54
|
31.05.2021 16:19, Alex Peshkoff via Firebird-devel wrote: > Dimitry, can you provide standard syntax for others to compare? https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#call%20statement -- WBR, SD. |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 14:59:37
|
On 31/05/2021 11:39, Vlad Khorsun wrote: > 31.05.2021 16:19, Adriano dos Santos Fernandes wrote: >> Hi! >> >> EXECUTE PROCEDURE is full of weirdness, so I propose that standard SQL >> CALL is adapted for our needs. >> >> EXECUTE PROCEDURE [ <package> . ] <name> >> [ <value list> | ( <value list> ) ] >> [ RETURNING_VALUES <variable list> | >> RETURNING_VALUES ( <variable list> ) ] >> >> It does not allow one to "select" what just it wants. >> >> So if one changes the procedure output parameters, clients (DSQL and >> PSQL) needs to be changed. It's like "SELECT *" which is sure a bad >> practice. >> >> It has this weird RETURNING_VALUES and multiple syntax about parenthesis. >> >> It does not allow to just execute and ignore output parameters. > > As I see, all problems above is related with RETURNING_VALUES clause, > correct ? > But then an EXECUTE PROCEDURE ignoring output will not be possible. Also, EXECUTE PROCEDURE is very verbose (specially for execute ignoring outputs, which in most languages does not even require any keyword - but is ambiguous with our functions). If CALL is not accepted, then I propose same syntax with "EXEC". Adriano |
|
From: Dimitry S. <sd...@ib...> - 2021-05-31 15:05:53
|
31.05.2021 16:59, Adriano dos Santos Fernandes wrote: > But then an EXECUTE PROCEDURE ignoring output will not be possible. Why? RETURNING_VALUES clause can be left as is and new branch "RETURNING <list> INTO <list>" is just added. -- WBR, SD. |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 16:57:11
|
On 31/05/2021 12:05, Dimitry Sibiryakov wrote: > 31.05.2021 16:59, Adriano dos Santos Fernandes wrote: >> But then an EXECUTE PROCEDURE ignoring output will not be possible. > > Why? RETURNING_VALUES clause can be left as is and new branch > "RETURNING <list> INTO <list>" is just added. > In DSQL, without RETURNING_VALUES it will return all values instead of nothing. Adriano |
|
From: Mark R. <ma...@la...> - 2021-05-31 15:38:56
|
On 31-05-2021 16:59, Adriano dos Santos Fernandes wrote: > But then an EXECUTE PROCEDURE ignoring output will not be possible. > > Also, EXECUTE PROCEDURE is very verbose (specially for execute ignoring > outputs, which in most languages does not even require any keyword - but > is ambiguous with our functions). > > If CALL is not accepted, then I propose same syntax with "EXEC". What is the real problem you're trying to solve? The current syntax is 20+ years old, and I think this is one of the first times (if not the first time) I hear complaints about its verbosity, or a need to selectively obtain output columns. If this is really something that needs to be addressed, I would prefer a less ambiguous naming than `EXEC` (which at first glance I'd think just to be an abbreviation of `EXECUTE PROCEDURE` instead of an entirely different statement with different semantics). Maybe a few design options would need to be evaluated. For example, if the existing `EXECUTE PROCEDURE` syntax can be retrofitted to obtain the behaviour you want. For example, `RETURNING_VALUES <variable list>` and `INTO <variable_list>` serve the same purpose, so they could be alternative terms (we could then also consider allowing `INTO (<variable list>)` everywhere `INTO` currently occurs for consistency). The problem of selectively returning columns could be solved with `RETURNING`, where we introduce `RETURNING ()` to signal that no columns need to be returned (although I really question the need for such a feature). Again, for syntax consistency, we should consider allowing this everywhere `RETURNING` occurs, where it would signal the 'normal' behaviour. Mark -- Mark Rotteveel |
|
From: Dmitry Y. <fir...@ya...> - 2021-05-31 16:28:06
|
31.05.2021 18:38, Mark Rotteveel wrote: > What is the real problem you're trying to solve? The current syntax is > 20+ years old, and I think this is one of the first times (if not the > first time) I hear complaints about its verbosity, or a need to > selectively obtain output columns. Blame me ;-) This thread has originated from the discussion in PR #281 where I suggested to consider less verbose typing for packaged routines in PSQL, compare: execute procedure rdb$blob_util.append(...); -- packaged procedure vs rdb$blob_util.seek(...); -- packaged function Our functions historically required to return the result, i.e. looked like this: some_var = rdb$blob_util.seek(...); but later (FB v1.5, IIRC) the syntax was relaxed to allow a call without the explicitly returned value. And this is really handy when used in PSQL. In turn, procedures have a more verbose syntax (EXECUTE PROCEDURE) and still don't allow to ignore the results, which looks somewhat inconsistent. This is not something extremely important, but our EXECUTE PROCEDURE is really showing its age with some syntax weirdness and maybe it makes sense to polish it up a little. Dmitry |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 16:58:29
|
On 31/05/2021 12:38, Mark Rotteveel wrote: > The problem of selectively returning columns could be solved with > `RETURNING`, where we introduce `RETURNING ()` to signal that no columns > need to be returned No, please. Adriano |
|
From: Dmitry Y. <fir...@ya...> - 2021-05-31 16:34:54
|
31.05.2021 17:49, Dimitry Sibiryakov пишет: > 31.05.2021 16:19, Alex Peshkoff via Firebird-devel wrote: >> Dimitry, can you provide standard syntax for others to compare? > > https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#call%20statement As far as I see, it's the same as EXECUTE PROCEDURE without outputs. But we already have non-standard RETURNING added to standard I/U/D statements, so what's the difference? Even if the majority objects this idea, we can still utilize CALL in the standard way, i.e. use it when calling procedures that don't return outputs (or we are willing to ignore them). Dmitry |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 16:55:44
|
On 31/05/2021 13:34, Dmitry Yemanov wrote: > 31.05.2021 17:49, Dimitry Sibiryakov пишет: >> 31.05.2021 16:19, Alex Peshkoff via Firebird-devel wrote: >>> Dimitry, can you provide standard syntax for others to compare? >> >> https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#call%20statement > > As far as I see, it's the same as EXECUTE PROCEDURE without outputs. But > we already have non-standard RETURNING added to standard I/U/D > statements, so what's the difference? > > Even if the majority objects this idea, we can still utilize CALL in the > standard way, i.e. use it when calling procedures that don't return > outputs (or we are willing to ignore them). > I do not known exactly how standard SQL procedures works, but given that majority of engines supports IN/OUT parameters together, CALL could also return results. Our procedures IMO is better separating IN/OUT, but requires adaptation to CALL to return values. Adriano |
|
From: Dimitry S. <sd...@ib...> - 2021-05-31 19:12:01
|
31.05.2021 18:34, Dmitry Yemanov wrote: > As far as I see, it's the same as EXECUTE PROCEDURE without outputs. But we already have > non-standard RETURNING added to standard I/U/D statements, so what's the difference? > > Even if the majority objects this idea, we can still utilize CALL in the standard way, > i.e. use it when calling procedures that don't return outputs (or we are willing to ignore > them). The problem with standard syntax that it is supposed to return values via the same parameters set. IN parameters, OUT parameters and IN OUT parameters can be intermixed. We would have to change a lot in CREATE PROCEDURE/FUNCTION syntax and may be even engine internals to comply with the standard. -- WBR, SD. |
|
From: Mark R. <ma...@la...> - 2021-06-01 08:21:45
|
On 2021-05-31 18:34, Dmitry Yemanov wrote: > 31.05.2021 17:49, Dimitry Sibiryakov пишет: >> 31.05.2021 16:19, Alex Peshkoff via Firebird-devel wrote: >>> Dimitry, can you provide standard syntax for others to compare? >> >> https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#call%20statement > > As far as I see, it's the same as EXECUTE PROCEDURE without outputs. > But we already have non-standard RETURNING added to standard I/U/D > statements, so what's the difference? > > Even if the majority objects this idea, we can still utilize CALL in > the standard way, i.e. use it when calling procedures that don't > return outputs (or we are willing to ignore them). IIRC, the SQL standard assumes existence of OUT parameters here (though I'm not sure, and I don't have my copy at hand right now). Mark |
|
From: Alex P. <pes...@ma...> - 2021-05-31 17:47:55
|
On 5/31/21 4:19 PM, Adriano dos Santos Fernandes wrote: > Hi! > > EXECUTE PROCEDURE is full of weirdness... May be keep old syntax and add some new, not related with SQL-standard CALL: EXECUTE PROCEDURE [ <package> . ] <name> [ <value list> | ( <value list> ) ] [ RETURNING_VALUES <variable list> | RETURNING_VALUES ( <variable list> ) | RETURNING <value list> [ INTO <variable list> ] ] As the second, unrelated change, shorten EXECUTE PROCEDURE/STATEMENT/what-else OBJECT to EXEC OBJECT and decide about object type from context - provided it will not raise the hell in btyacc. |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-05-31 19:21:29
|
On 31/05/2021 14:47, Alex Peshkoff via Firebird-devel wrote: > On 5/31/21 4:19 PM, Adriano dos Santos Fernandes wrote: >> Hi! >> >> EXECUTE PROCEDURE is full of weirdness... > > May be keep old syntax and add some new, not related with SQL-standard > CALL: > > EXECUTE PROCEDURE [ <package> . ] <name> > [ <value list> | ( <value list> ) ] > [ RETURNING_VALUES <variable list> | > RETURNING_VALUES ( <variable list> ) | > RETURNING <value list> [ INTO <variable list> ] ] > This does not support ignore all output in DSQL. > As the second, unrelated change, shorten EXECUTE > PROCEDURE/STATEMENT/what-else OBJECT to EXEC OBJECT and decide about > object type from context - provided it will not raise the hell in btyacc. > I do not understood what you mean. Adriano |
|
From: Dmitry Y. <fir...@ya...> - 2021-06-01 03:56:11
|
31.05.2021 22:21, Adriano dos Santos Fernandes wrote: > >> As the second, unrelated change, shorten EXECUTE >> PROCEDURE/STATEMENT/what-else OBJECT to EXEC OBJECT and decide about >> object type from context - provided it will not raise the hell in btyacc. > > I do not understood what you mean. Shorthands like these: EXEC MY_PROC (...) -- instead of EXECUTE PROCEDURE MY_PROC EXEC 'MY SQL' (...) -- instead of EXECUTE STATEMENT 'MY SQL' Dmitry |
|
From: Dimitry S. <sd...@ib...> - 2021-06-01 09:51:11
|
31.05.2021 21:21, Adriano dos Santos Fernandes wrote: >> [ <value list> | ( <value list> ) ] >> [ RETURNING_VALUES <variable list> | >> RETURNING_VALUES ( <variable list> ) | >> RETURNING <value list> [ INTO <variable list> ] ] >> > This does not support ignore all output in DSQL. There can be syntax "RETURNING NULL" for that. -- WBR, SD. |
|
From: Mark R. <ma...@la...> - 2021-06-01 11:05:36
|
On 2021-06-01 11:51, Dimitry Sibiryakov wrote: > 31.05.2021 21:21, Adriano dos Santos Fernandes wrote: >>> [ <value list> | ( <value list> ) ] >>> [ RETURNING_VALUES <variable list> | >>> RETURNING_VALUES ( <variable list> ) | >>> RETURNING <value list> [ INTO <variable list> ] ] >>> >> This does not support ignore all output in DSQL. > > There can be syntax "RETURNING NULL" for that. No it can't, because under the syntax rules established for the RETURNING clause in other statements, that would produce a column with the value NULL. Mark |
|
From: Alex P. <pes...@ma...> - 2021-06-01 10:59:34
|
On 5/31/21 10:21 PM, Adriano dos Santos Fernandes wrote: > >> As the second, unrelated change, shorten EXECUTE >> PROCEDURE/STATEMENT/what-else OBJECT to EXEC OBJECT and decide about >> object type from context - provided it will not raise the hell in btyacc. >> > I do not understood what you mean. Now: EXECUTE PROCEDURE PROC1 .... (other parameters) Suggested alternate syntax: EXEC PROC1 .... (other parameters) Now: EXECUTE STATEMENT 'SQL-text' .... (other parameters) Suggested alternate syntax: EXEC 'SQL-text' .... (other parameters) Change is trivial - the only problem I see is does btyacc agree with such syntax or not. |
|
From: Adriano d. S. F. <adr...@gm...> - 2021-06-01 11:26:31
|
On 01/06/2021 07:59, Alex Peshkoff via Firebird-devel wrote: > On 5/31/21 10:21 PM, Adriano dos Santos Fernandes wrote: > >> >>> As the second, unrelated change, shorten EXECUTE >>> PROCEDURE/STATEMENT/what-else OBJECT to EXEC OBJECT and decide about >>> object type from context - provided it will not raise the hell in >>> btyacc. >>> >> I do not understood what you mean. > > Now: EXECUTE PROCEDURE PROC1 .... (other parameters) > Suggested alternate syntax: EXEC PROC1 .... (other parameters) > > Now: EXECUTE STATEMENT 'SQL-text' .... (other parameters) > Suggested alternate syntax: EXEC 'SQL-text' .... (other parameters) > > Change is trivial - the only problem I see is does btyacc agree with > such syntax or not. > This syntax is ambiguous: EXEC something something may be a procedure or a variable. Of course semantically everything can be done, but PSQL has not such dynamic rules as C++, nor I think it will be good to have or not a variable in scope change a command completely. If we wanted such rules, we could instead allow to call procedures with the function syntax as statement, and if it exist, call the procedure. With the same problems, as a function with the same name may be later created. Of course, I don't want that. Adriano |
|
From: Alex P. <pes...@ma...> - 2021-06-01 11:03:17
|
On 6/1/21 12:51 PM, Dimitry Sibiryakov wrote:
> 31.05.2021 21:21, Adriano dos Santos Fernandes wrote:
>>> [ <value list> | ( <value list> ) ]
>>> [ RETURNING_VALUES <variable list> |
>>> RETURNING_VALUES ( <variable list> ) |
>>> RETURNING <value list> [ INTO <variable list> ] ]
>>>
>> This does not support ignore all output in DSQL.
>
> There can be syntax "RETURNING NULL" for that.
>
A wide choice:
- if we accept also RETURNING '(' <value list> ')' [ INTO '(' <variable
list> ')' ] ] form RETURNING '(' ')' may be used
- just RETURNING - blank list of values
- something like NORETURN
But telling true all of them are looking not ideal - ie. not enough
beautiful...
|
|
From: Adriano d. S. F. <adr...@gm...> - 2021-06-01 11:18:08
|
On 01/06/2021 08:03, Alex Peshkoff via Firebird-devel wrote:
> On 6/1/21 12:51 PM, Dimitry Sibiryakov wrote:
>> 31.05.2021 21:21, Adriano dos Santos Fernandes wrote:
>>>> [ <value list> | ( <value list> ) ]
>>>> [ RETURNING_VALUES <variable list> |
>>>> RETURNING_VALUES ( <variable list> ) |
>>>> RETURNING <value list> [ INTO <variable list> ] ]
>>>>
>>> This does not support ignore all output in DSQL.
>>
>> There can be syntax "RETURNING NULL" for that.
>>
>
> A wide choice:
> - if we accept also RETURNING '(' <value list> ')' [ INTO '(' <variable
> list> ')' ] ] form RETURNING '(' ')' may be used
> - just RETURNING - blank list of values
> - something like NORETURN
>
> But telling true all of them are looking not ideal - ie. not enough
> beautiful...
>
RETURNING does not use parenthesis.
RETURNING NULL is ambiguous.
RETURNING () is ugly and PSQL has nothing to do with LISP.
NORETURN seems nonsense for me - the act of ignore return values should
be the more compact possible syntax - not have appendices - otherwise
suggestion of creating functions returning dummy values will be used(by
us and by users) instead of creating procedures.
Even if we consider that a procedure without output parameters may be
called without NORETURN in DSQL, things must be extensible - if
procedure is changed, already wrote commands should not change their
behaviors completely.
Adriano
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X