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 [ ( <input parameter list> ) ]
[ <subroutines> ]
do <sql command>
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
