Re: [Firebird-devel] New statement: EXECUTE SQL
On Tue, Aug 16, 2022 at 3:48 AM Mark Rotteveel wrote:
>
> Am I reading this correctly that your proposal is to use the following
> syntax?
>
> execute sql (p1 integer, p2 varchar(50))
> do
>select * from t where t.id = :p1 and t.name = :p2
>
> So, no explicit positional parameter markers ('?')?
>
>
Not exactly. I split proposal in two:
1) Add named parameters
This will become valid (in server):
-
select *
from rdb$database
where rdb$relation_id = :relation_id or
rdb$relation_id = :relation_id
Will report a single parameter, with name RELATION_ID.
-
Unnamed parameters (question marks) will still be allowed.
-
select *
from rdb$database
where rdb$relation_id = :relation_id or
rdb$relation_id = ? or
rdb$relation_id = :relation_id
This will report parameters in order: RELATION_ID, unnamed
-
With this, EXECUTE BLOCK can be simplified and "= ?" in input parameters
becomes optional.
2) Add EXECUTE SQL (maybe with another name)
It will support named and unnamed parameters.
So, given your example, it would be valid, but this one too:
-
execute sql (p1 integer)
do
select *
from t
where t.id = :p1 and
t.name = :p2 and
t.whatever = ?
-
Explicit parameters in the argument list will be like EXECUTE BLOCK (may
use or not "= ?").
In this way we create a path that we should have already created many times
ago, to avoid clients doing server business.
Only who can want would use unnamed parameters, and since these are
top-level commands, one would not use them if their framework does not
support them.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
>Can we just have named parameters support in Firebird before this feature > so > client parser will be not needed at all?.. That would be great. :) -- Mgr. Jiří Činčura https://www.tabsoverspaces.com/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Adriano dos Santos Fernandes wrote 16.08.2022 3:25: No, I expect that client parsers may not do unnecessary things, at least with this statement. Can we just have named parameters support in Firebird before this feature so client parser will be not needed at all?.. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15-08-2022 19:42, Vlad Khorsun wrote: Also, I don't like 'sql' word, especially after 'execute statement' and 'execute block'. Too much, as for me :) Syntax with 'with' instead of 'execute sql' looks much better to me, but it is already used in CTE's, thus it seems as not the best choice :( Personally, I do like the EXECUTE SQL for its explicitness, and it fits in the EXECUTE PROCEDURE and EXECUTE BLOCK naming; it is slightly a mismatch/confusing with EXECUTE STATEMENT though. An alternative could be to extend the EXECUTE BLOCK syntax for this so it has a simplified form, without a RETURNS clause. So, it has two forms: EXECUTE BLOCK [(, ...)] [RETURNS (, ...)] AS [] BEGIN [ ...] END and the simplified form EXECUTE BLOCK [(, ...)]] AS [] DO Syntactically, the simplified form could be distinguished by the DO instead of the BEGIN. Presence of the RETURNS clause with DO would raise an error (maybe easiest is to allow it in the parse.y syntax, but raise an error when its present). A downside of this is that statement recognition by clients that currently just look for EXECUTE BLOCK might break. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 16-08-2022 03:25, Adriano dos Santos Fernandes wrote:
On 15/08/2022 14:42, Vlad Khorsun wrote:
I like the idea but not syntax. As already mentioned, there it will be
hard for
app\component devs to parse the whole statement looking for parameters.
Note,
semicolon usually mark "client" named parameters and it will be near to
impossible
for, say, Delphi components to correctly preprocess statement like below:
execute sql (p1 integer = :p1)
do
select * from t where t.id = :p1 and t.name = :p2
Client parsers already need to stop at some place.
But I understand what you mean, so just read below...
You may expect after preprocessing by app it will be like:
execute sql (p1 integer = ?)
do
select * from t where t.id = :p1 and t.name = ?
but actually it will be like:
execute sql (p1 integer = ?)
do
select * from t where t.id = ? and t.name = ?
without complex re-writing of existing preprocessors.
No, I expect that client parsers may not do unnecessary things, at least
with this statement.
We may report named parameters in the right way (currently EXECUTE BLOCK
does not report then, do not know why).
For the parsers using :name syntax, that's very easy. Just detect the
new statement and do not pre-parse it.
AFAIK .NET uses @name syntax. Since that is not a Firebird valid syntax,
it should also not be difficult to detect and replace it.
So this will not be processed by client at all. It will just read
parameter names (p1, p2) from the described statement:
execute sql (p1 integer = ?)
do
select * from t where t.id = :p1 and t.name = :p2
In fact the ugly "= ?" becomes completely unnecessary and better removed
in this statement.
And it's up to us to continue allowing unnamed parameters in this
statement or not.
I think we should and people using client libraries that require named
(instead positional) parameters could just not use them.
Am I reading this correctly that your proposal is to use the following
syntax?
execute sql (p1 integer, p2 varchar(50))
do
select * from t where t.id = :p1 and t.name = :p2
So, no explicit positional parameter markers ('?')?
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 14:42, Vlad Khorsun wrote: > > I like the idea but not syntax. As already mentioned, there it will be > hard for > app\component devs to parse the whole statement looking for parameters. > Note, > semicolon usually mark "client" named parameters and it will be near to > impossible > for, say, Delphi components to correctly preprocess statement like below: > > execute sql (p1 integer = :p1) > do > select * from t where t.id = :p1 and t.name = :p2 > Client parsers already need to stop at some place. But I understand what you mean, so just read below... > > You may expect after preprocessing by app it will be like: > > execute sql (p1 integer = ?) > do > select * from t where t.id = :p1 and t.name = ? > > > but actually it will be like: > > execute sql (p1 integer = ?) > do > select * from t where t.id = ? and t.name = ? > > without complex re-writing of existing preprocessors. > No, I expect that client parsers may not do unnecessary things, at least with this statement. We may report named parameters in the right way (currently EXECUTE BLOCK does not report then, do not know why). For the parsers using :name syntax, that's very easy. Just detect the new statement and do not pre-parse it. AFAIK .NET uses @name syntax. Since that is not a Firebird valid syntax, it should also not be difficult to detect and replace it. So this will not be processed by client at all. It will just read parameter names (p1, p2) from the described statement: execute sql (p1 integer = ?) do select * from t where t.id = :p1 and t.name = :p2 In fact the ugly "= ?" becomes completely unnecessary and better removed in this statement. And it's up to us to continue allowing unnamed parameters in this statement or not. I think we should and people using client libraries that require named (instead positional) parameters could just not use them. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 16:20, Dimitry Sibiryakov wrote: > I see no difference between proposed syntax and WITH except of > definition of functions instead of derived tables in CTE part. > What is wrong with expanding it into something like this: > > WITH I prefer to not reuse a standard keyword for this. And the case with parameters makes WITH a complete different beast. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 14:47, Dmitry Yemanov wrote: > using (p1 integer = :p1, p2 varchar(255) = :p2) > do select * from t where t.id = :p1 and t.name = :p2 I like this one. Or replacing USING by GIVEN. I'm not sure the parameter-less case is nice: using declare function ... do ... Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Vlad Khorsun wrote 15.08.2022 19:42: Also, I don't like 'sql' word, especially after 'execute statement' and 'execute block'. Too much, as for me :) Syntax with 'with' instead of 'execute sql' looks much better to me, but it is already used in CTE's, thus it seems as not the best choice :( I see no difference between proposed syntax and WITH except of definition of functions instead of derived tables in CTE part. What is wrong with expanding it into something like this: WITH function subfunc (i1 integer) returns integer as begin return i1; end, procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end, derives_table as (select current_time from rdb$database) select subfunc(?) + o1 from subproc(?) join dt on 1=1 -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
15.08.2022 20:42, Vlad Khorsun wrote: Note, semicolon usually mark "client" named parameters Colon, of course, not semicolon. Sorry, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
15.08.2022 20:42, Vlad Khorsun wrote: Also, I don't like 'sql' word, especially after 'execute statement' and 'execute block'. Too much, as for me :) Syntax with 'with' instead of 'execute sql' looks much better to me, but it is already used in CTE's, thus it seems as not the best choice :( using (p1 integer = :p1, p2 varchar(255) = :p2) do select * from t where t.id = :p1 and t.name = :p2 or just using (p1 integer = :p1, p2 varchar(255) = :p2) select * from t where t.id = :p1 and t.name = :p2 ? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
14.08.2022 2:28, Adriano dos Santos Fernandes wrote: Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. I like the idea but not syntax. As already mentioned, there it will be hard for app\component devs to parse the whole statement looking for parameters. Note, semicolon usually mark "client" named parameters and it will be near to impossible for, say, Delphi components to correctly preprocess statement like below: execute sql (p1 integer = :p1) do select * from t where t.id = :p1 and t.name = :p2 You may expect after preprocessing by app it will be like: execute sql (p1 integer = ?) do select * from t where t.id = :p1 and t.name = ? but actually it will be like: execute sql (p1 integer = ?) do select * from t where t.id = ? and t.name = ? without complex re-writing of existing preprocessors. Therefore I suggest to use one kind of parameters. I prefer declared ones, i.e. without direct params. Query above will look like: execute sql (p1 integer = :p1, p2 varchar(255) = :p2) do select * from t where t.id = :p1 and t.name = :p2 and after preprocessing: execute sql (p1 integer = ?, p2 varchar(255) = ?) do select * from t where t.id = :p1 and t.name = :p2 i.e. only header part between 'sql' and 'do' (or 'declare') should be preprocessed by client app\access components. Also, I don't like 'sql' word, especially after 'execute statement' and 'execute block'. Too much, as for me :) Syntax with 'with' instead of 'execute sql' looks much better to me, but it is already used in CTE's, thus it seems as not the best choice :( Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15-08-2022 12:40, Simonov Denis via Firebird-devel wrote: I'm talking about the fact that EXECUTE STATEMENT supports named parameters, including those for EXECUTE BLOCK. There's even a special query preparser for that. Those who implement client libraries will have to do much the same. And if for EXECUTE BLOCK the prepaser is quite simple (named parameters need to be searched only between EXECUTE BLOCK and the first AS), then for such a syntax it will not be very trivial. In your version, I don't see how even EXECUTE STATEMENT can work with named parameters. The named parameters in the example are basically equivalent to stored procedure / execute block parameters (a.k.a. PSQL variables), and PSQL supports named parameters in SQL. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 07:27, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.08.2022 12:24: >> Firebird does not have any support for client named parameters. > > IMessageMetadata has field for name which is currently unused. What > else client support do you have on mind? > And it's not filled for: select 1 from rdb$database where rdb$relation_id = ?; Looks like not filled for EXECUTE BLOCK input parameters too, which I think it should, so as with EXECUTE SQL. What I mean is that we do not support this (except in EXECUTE STATEMENT): select 1 from rdb$database where rdb$relation_id = :relation_id If we start accept this, probably relation_id should be automatically declared and "joined" the explicit EXECUTE SQL parameter list. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
15.08.2022 13:24, Adriano dos Santos Fernandes пишет: On 15/08/2022 04:16, Simonov Denis via Firebird-devel wrote: Very good feature, reminds me of the functionality from Oracle WITH Procedure p1 ... AS ... FUNCTION f1 ... AS ... SELECT... Firebird does not have any support for client named parameters. As Mark told, it will require client support. Just try to implement EXECUTE STATEMENT with your syntax and support for named parameters, then it will become clear what I'm talking about. My option is this. I suppose if standalone statements is going to have support of named parameters as EXECUTE STATEMENT, undeclared named parameters are going to be automatically declared and comes after the explicit ones. I'm talking about the fact that EXECUTE STATEMENT supports named parameters, including those for EXECUTE BLOCK. There's even a special query preparser for that. Those who implement client libraries will have to do much the same. And if for EXECUTE BLOCK the prepaser is quite simple (named parameters need to be searched only between EXECUTE BLOCK and the first AS), then for such a syntax it will not be very trivial. In your version, I don't see how even EXECUTE STATEMENT can work with named parameters. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Adriano dos Santos Fernandes wrote 15.08.2022 12:24: Firebird does not have any support for client named parameters. IMessageMetadata has field for name which is currently unused. What else client support do you have on mind? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 04:16, Simonov Denis via Firebird-devel wrote: > > Very good feature, reminds me of the functionality from Oracle > > WITH Procedure p1 ... > AS > ... > FUNCTION f1 ... > AS > ... > SELECT... > Plus, DECLARE VAR := ? But things are very less useful for them due to their BEGIN...END blocks not capable of produce multiple rows result, nor subfunctions not able to be pipelined, etc. > However, it's not entirely clear to me how client applications handle > named parameters (which will be replaced with "?")? It seems to me > unnamed parameters "?" (which in client applications will most likely be > named) leave either in the or in the request > itself. I vote for them to remain only in the request. > Firebird does not have any support for client named parameters. As Mark told, it will require client support. > Just try to implement EXECUTE STATEMENT with your syntax and support for > named parameters, then it will become clear what I'm talking about. My > option is this. > I suppose if standalone statements is going to have support of named parameters as EXECUTE STATEMENT, undeclared named parameters are going to be automatically declared and comes after the explicit ones. > Syntax: > > execute sql > [] > do > > Here is how it can be used: > > execute sql > declare function subfunc (i1 integer) returns integer > as > begin > return i1; > end > > declare procedure subproc (i1 integer) returns (o1 integer) > as > begin > o1 = i1; > suspend; > end > do > select subfunc(?) + o1 > from subproc(?) > This reduces a lot the usefulness of the statement and I do not agree. A path I can follow is to be like Oracle and have the "parameters" declared as variables: - execute sql declare variable p1 integer = ?; declare variable p2 integer = ?; declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) - But I still prefer parameters to be parameters, like in EXECUTE BLOCK. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
14.08.2022 2:28, Adriano dos Santos Fernandes пишет: Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. Adriano Very good feature, reminds me of the functionality from Oracle WITH Procedure p1 ... AS ... FUNCTION f1 ... AS ... SELECT... However, it's not entirely clear to me how client applications handle named parameters (which will be replaced with "?")? It seems to me unnamed parameters "?" (which in client applications will most likely be named) leave either in the or in the request itself. I vote for them to remain only in the request. Just try to implement EXECUTE STATEMENT with your syntax and support for named parameters, then it will become clear what I'm talking about. My option is this. Syntax: execute sql [] do Here is how it can be used: execute sql declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(?) + o1 from subproc(?) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
14.08.2022 2:28, Adriano dos Santos Fernandes пишет: Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. Adriano Very good feature, reminds me of the functionality from Oracle WITH Procedure p1 ... AS ... FUNCTION f1 ... AS ... SELECT... However, it's not entirely clear to me how client applications handle named parameters (which will be replaced with "?")? It seems to me unnamed parameters "?" (which in client applications will most likely be named) leave either in the or in the request itself. I vote for them to remain only in the request. Just try to implement EXECUTE STATEMENT with your syntax and support for named parameters, then it will become clear what I'm talking about. My option is this. Syntax: execute sql [] do Here is how it can be used: execute sql declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(?) + o1 from subproc(?) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Looks like a good idea to me. -- Mgr. Jiří Činčura https://www.tabsoverspaces.com/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Em dom., 14 de ago. de 2022 03:55, Mark Rotteveel escreveu: > > > Would this still allow the full SELECT syntax (including WITH clauses, > OFFSET/FETCH, GROUP BY, etc), and things like RETURNING for INSERT, etc? > Yes. > I assume this is a toplevel statement only, so it can't occur as a > derived table or subquery inside another statement. Is that correct? > Yes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 14-08-2022 01:28, Adriano dos Santos Fernandes wrote: Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. An interesting idea. I do find the ability to specify question mark parameters at the top and inside the body slightly confusing, but I understand why it is handy to allow this. I do think this statement will complicate the parser I have in Jaybird to detect statement types and whether or not it has RETURNING, but I can adjust. Would this still allow the full SELECT syntax (including WITH clauses, OFFSET/FETCH, GROUP BY, etc), and things like RETURNING for INSERT, etc? I assume this is a toplevel statement only, so it can't occur as a derived table or subquery inside another statement. Is that correct? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 13/08/2022 20:28, Adriano dos Santos Fernandes wrote: > and it's to use with SELECT, UPDATE, DELETE and MERGE And also INSERT and UPDATE OR INSERT... Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] New statement: EXECUTE SQL
Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
