Commented by: Omacht András (aomacht)

Same as CORE4736, but I voted for it! ;)

Commented by: @hvlad

Word OPTIONAL used with parameter suggest that caller could not set value for such parameter.
You need different behaviour - ability to silently ignore "marked" parameter if it is not present at SQL query text.
I suggest to "mark" such parameters using keyword EXTRA or EXCESS or ADDITIONAL.

Commented by: Vladimir Arkhipov (arkinform)

I do not agree, I think that for case "caller could not set value for such parameter" word "NULLABLE" is more suitable.
Word "OPTIONAL" is more obvious compared to other suggested variants.

Commented by: @asfernandes

I think this feature should not be done in this way.

It would be much better to think on terms of PSQL APis, as an example, as Oracle does with DBMS_SQL, where developer could dinamically inspect metadata and set parameters.

Commented by: Vladimir Arkhipov (arkinform)

I wrote similar suggestion 8 years ago in CORE2813, but I think that OPTIONAL keyword is simple and very useful feature for simple use cases.

Commented by: @hvlad

Vladimir,

There are some programming language which allows optional parameters. All of them (at least all that i know) requires default value for such parameter.
I.e. optional parameters is well know idea. It is also known as parameters with default values (or "default parameters").

You ask for completely different feature. In your case caller always set all declared parameters of EXECUTE STATEMENT and you want to allow to the
SQL query (i.e. "body" of EXECUTE STATEMENT) to not use some of declared params. From the SQL query point of view some input parameters are
not necessary to use. For me, word "extra" or "excess" looks very logical as mark for such parameters. But not "optional" as it is already widely used
with different meaning.

I don't want to mix up well known "optinal\default parameters" feature with feature you asked for.

Commented by: @hvlad

Adriano,

with all respect to Oracle, i don't think we should duplicate ISC API at P-SQL level (or copy DBMS_SQL package).
This way is very error prone, looks ugly (from SQL POV) and (i believe) will not be widely used by the most FB users.

I will not resist if such package arrives at Firebird some day, but i definitely will not be happy with it ;)

Commented by: @asfernandes

Vladimir, it's already possible to pass parameters not used. Just use RDB$SET_CONTEXT / RDB$GET_CONTEXT.

Not any static language (as PSQL) allows caller to pass unrecognized parameters.

Commented by: Vladimir Arkhipov (arkinform)

Adriano, I don't want to "pass unrecognized parameters", I want to "don't pass parameters" if there are no such parameters in sql text. At present I can make dynamic sql text but I can not make dynamic set of parameters.

Vlad, if you don't want to use OPTIONAL keyword, I choose ADDITIONAL.

Commented by: @asfernandes

You want to pass unrecognized parameters, use context for it.

Commented by: Omacht András (aomacht)

Same as / related to CORE4736

Commented by: @hvlad

Adriano,

context variables can't help in case of external query

Commented by: @hvlad

Lets continue discussion.

I can imagine 3 ways to mark excess (extra, addtional) parameters

1. mark every parameter individually:

EXECUTE STATEMENT ('sql text') (in_param1, EXCESS in_param2, in_param3, EXCESS in_param4)

here marked in_param2 and in_param4

2. mark once all parameters at the tail of the parameters list:

EXECUTE STATEMENT ('sql text') (in_param1, in_param2, EXCESS in_param_3, in_param4)

here marked in_param3 and in_param4

3. put excess parameters into separate list:

EXECUTE STATEMENT ('sql text') (in_param1, in_param2) EXCESS (in_param_3, in_param4)

here marked in_param3 and in_param4

The syntax (1) is in-line with other languages where input parameters could be marked (IN, OUT, INOUT, REF etc)
The syntax (2) and (3) are less noisy. The (3) is very unusual...

Another question if we should allow to mark not named parameters. It introduces some kind of ambiguity, see

EXECUTE STATEMENT ('insert into x values(?, ?, ?)') (1, excess 2, excess 3, 4)

what should be inserted: (1, 2, 4) or (1, 3, 4) ?

With another syntax we also have problem

EXECUTE STATEMENT ('insert into x values(?, ?, ?)') (1, 4) EXCESS (2, 3)

how to put non-excess value into 3rd parameter and leave excess value (which one ?) for the 2nd one ?

With named params we have no such problem:

EXECUTE STATEMENT ('insert into x values(:a, :b, :c)') (a := 1, EXCESS b := 2, EXCESS x := 3, c := 4), or
EXECUTE STATEMENT ('insert into x values(:a, :b, :c)') (a := 1, c := 4, EXCESS b := 2, x := 3), or
EXECUTE STATEMENT ('insert into x values(:a, :b, :c)') (a := 1, c := 4) EXCESS (b := 2, x := 3)

Commented by: Vladimir Arkhipov (arkinform)

>> I can imagine 3 ways to mark excess (extra, addtional) parameters

I think that syntax #⁠1 is the best variant and the most evident.

>> Another question if we should allow to mark not named parameters. It introduces some kind of ambiguity, see

In this case must be error like "parameter mismatch"

Commented by: Omacht András (aomacht)

Hi Vlad!
I vote for #⁠1 too, but I suggest the 'optional' word instead of 'excess'.
András

Commented by: @hvlad

András,

thanks for opinion.
I already explained why OPTIONAL can't be used here. So - no - it will not be OPTIONAL.

Commented by: Omacht András (aomacht)

Sorry Vlad, i forgot to reread comments. I can accept excess too.

András

Commented by: @hvlad

Changed ticket title to reflect agreement in keyword used (optional -> excess)

Commented by: @hvlad

The patch is committed, please check next snapshot build

Commented by: Omacht András (aomacht)

Hi Vlad!

We tested it and works well.

András

Commented by: @SENikitin

Can you please backport it to 3.0 too? This is very important for our business logic implementation.