Re: [Firebird-devel] New statement: EXECUTE SQL

2022-08-15 Thread Adriano dos Santos Fernandes
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

2022-08-15 Thread Adriano dos Santos Fernandes
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

2022-08-15 Thread Adriano dos Santos Fernandes
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

2022-08-15 Thread Dimitry Sibiryakov

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

2022-08-15 Thread Vlad Khorsun

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

2022-08-15 Thread Dmitry Yemanov

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

2022-08-15 Thread Vlad Khorsun

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


[Firebird-devel] Is there a way to get the server to send deferred messages?

2022-08-15 Thread Mark Rotteveel
Is there a way to get the server to send deferred messages without 
having to read an additional response? Messages like the response to 
op_batch_create are deferred, meaning you have to trigger a non-deferred 
action to get the response.


I tried to send op_dummy to the server, but that had no effect. I could 
send an op_ping or another simple request, but that requires consuming 
an additional response.


This is mostly for test purposes, so if using a ping or similar is the 
only way, then so be it, but if there is something I missed, then that 
would be appreciated.

--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel


Re: [Firebird-devel] Do repeated op_batch_create leak the batch?

2022-08-15 Thread Mark Rotteveel

On 15-08-2022 12:47, Alex Peshkoff via Firebird-devel wrote:

On 8/13/22 14:59, Mark Rotteveel wrote:
I'm implementing batch execution in Jaybird. Looking at the code of 
rem_port::batch_create(P_BATCH_CREATE* batch, PACKET* sendL) in 
server.cpp, it looks like sending multiple op_batch_create requests 
for the same statement handle could leak a previously created batch, 
as it doesn't call statement->rsr_batch->release() before assigning a 
new batch to statement->rsr_batch.


Is my assessment correct?



Seems to be so. In the engine there is a check avoiding opening batch 
when batch or cursor is already opened, sane should be added to remote 
server. Add a ticket please.


https://github.com/FirebirdSQL/firebird/issues/7262

--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel


Re: [Firebird-devel] New statement: EXECUTE SQL

2022-08-15 Thread Mark Rotteveel

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] Do repeated op_batch_create leak the batch?

2022-08-15 Thread Alex Peshkoff via Firebird-devel

On 8/13/22 14:59, Mark Rotteveel wrote:
I'm implementing batch execution in Jaybird. Looking at the code of 
rem_port::batch_create(P_BATCH_CREATE* batch, PACKET* sendL) in 
server.cpp, it looks like sending multiple op_batch_create requests 
for the same statement handle could leak a previously created batch, 
as it doesn't call statement->rsr_batch->release() before assigning a 
new batch to statement->rsr_batch.


Is my assessment correct?



Seems to be so. In the engine there is a check avoiding opening batch 
when batch or cursor is already opened, sane should be added to remote 
server. Add a ticket please.





Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel


Re: [Firebird-devel] New statement: EXECUTE SQL

2022-08-15 Thread Adriano dos Santos Fernandes
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

2022-08-15 Thread Simonov Denis via Firebird-devel

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

2022-08-15 Thread Dimitry Sibiryakov

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

2022-08-15 Thread 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...
> 

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] Parallel workers in isc_action_svc_repair + isc_spb_rpr_sweep_db

2022-08-15 Thread Jiří Činčura
>There is isc_spb_rpr_par_workers. I should have been more careful 
> reading the question, sorry.

Thanks. I was grep-ing for `parallel`, expecting something like 
isc_spb_bkp_parallel_workers. :)

-- 
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] Status of isc_dpb_parallel_workers

2022-08-15 Thread Jiří Činčura
> So, I don't see what should be added to the README.parallel_features
> regarding gbak. Do you have good idea ?

A simple pointer to doc/README.gbak. Just to make sure people reading about the 
parallel features in engine don't miss the gbak too.

Something like "Parallel backups and restores are now supported too." (with 
link).

-- 
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

2022-08-15 Thread Simonov Denis via Firebird-devel

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

2022-08-15 Thread Simonov Denis via Firebird-devel

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