EXECUTE BLOCK of Firebird not supported?
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2024-03-09 07:49

I have tried to run Execute Block using TSQLQuery, but simply it is
not executed. My example would be :

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     with
query1 do begin         // SQL.Add('Set Term # ; ');        
SQL.Add('execute block as begin ');         SQL.Add ('update table1
set a1=1, a2=1 where a0=1;');         SQL.Add ('update table1 set
a1=2, a2=2 where a0=2;');         SQL.Add('end');         //
SQL.Add('#');         // SQL.Add('Set Term ; #');         
Transaction.Active := True;                try            ExecSQL;   
        (Transaction as TSQLTransaction).Commit;             Result :=
'Success';         except             (Transaction as
TSQLTransaction).Rollback;             Result := 'Failure';     end;
There are no errors captured within try..except block and string
"Success" is returned. But the database tables are not updated.

I tried both of including "set term #" (which are commented out in the
above example), and without them. Neither worked.

In Flamerobin's ISQL, including "set term #" version worked.


Following TSQLScript version works fine.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     with
SQLScript1 do begin         Script.Add ('update table1 set a1=1, a2=1
where a0=1;');         Script.Add ('update table1 set a1=2, a2=2 where
a0=2;');          Transaction.Active := True;                try     
     Execute;             (Transaction as TSQLTransaction).Commit;   
        Result := 'Success';         except             (Transaction
as TSQLTransaction).Rollback;             Result := 'Failure';    
end;
I think I have written on this issue in the past. I'd like to check
whether Lazarus does not support "Execute Block" through TSQLQuery.

----------------------------------------------------------------------
egsuh · 2024-03-09 08:03

I did myself some more test, and found out that the UPDATE command
must be in one line, without SET TERM. In the code snippet above,
Update statement is in one line but my real program is in two lines
splitting before where.

So following example should work.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i       with
query1 do begin            SQL.Add('execute block as begin ');       
   SQL.Add ('update table1 set a1=1, a2=1 where a0=1;');           
SQL.Add ('update table1 set a1=2, a2=2 where a0=2;');           
SQL.Add('end');                 Transaction.Active := True;          
ExecSQL;            (Transaction as TSQLTransaction).Commit;      end;

----------------------------------------------------------------------
egsuh · 2024-03-11 07:47

I made a test program, and this makes big difference in favor of
"execute block".

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrFirebird.Button1Click(Sender: TObject);var   t1, t2 : TDateTime; 
 i : integer;begin   // - Comparing EXECUTE BLOCK and TSQLScript
performance ---    with SQLQuery1 do begin      SQL.Clear;     
SQL.Add('delete from test where code >= 10000;');     
Transaction.Active := True;      ExecSQL;      (Transaction as
TSQLTransaction).Commit;   end;     t1 := now;   with SQLQuery1 do
begin      SQL.Clear;      SQL.Add('EXECUTE BLOCK AS BEGIN ');     
for i := 10001 to 10100 do begin          SQL.Add(Format('insert into
TEST (Code, Description) values (%d, ''Text for %d'');',             
   [i, i]));      end;      SQL.Add('END');       Transaction.Active
:= True;      ExecSQL;      (Transaction as TSQLTransaction).Commit; 
 end;   t2 := now;    memo1.lines.add('time for execute block = ' +
IntToStr(MillisecondsBetween(t2, t1)));    with SQLScript1 do begin  
  Script.Clear;      for i := 10101 to 10200 do begin         
Script.Add(Format('insert into TEST (Code, Description) values (%d,
''Text for %d'');',                  [i, i]));      end;      
Transaction.Active := True;      Execute;      (Transaction as
TSQLTransaction).Commit;   end;   t1 := now;    memo1.lines.add('time
for TSQLScript = ' + IntToStr(MillisecondsBetween(t1, t2)));end;

Execute block = around 18 msec
TSQLScript  = around 98 msec

I hope somebody confirm the results, as I might have made mistakes
like calculating milliseconds between, etc.

----------------------------------------------------------------------
rvk · 2024-03-11 08:05

Quote from: egsuh on March 11, 2024, 08:47:21 amI hope somebody
confirm the results, as I might have made mistakes like calculating
milliseconds between, etc.
I don't even need to test this. TSQLScript parses the SQL and sends
the SQL per command (separated by ; ) to the server.
Your execute block would just execute everything directly on the
server in one go.

Do keep in mind that you probably want to commit a loop per few
thousand records. Adding millions in one transaction can become slow
and inefficient too.

BTW. I find the need for everything on one line per ; a bit strange.
It shouldn't be a requirement for EXECUTE BLOCK but maybe TSQLQuery
does something with it.

----------------------------------------------------------------------
rvk · 2024-03-11 10:29

Quote from: rvk on March 11, 2024, 09:05:08 amBTW. I find the need for
everything on one line per ; a bit strange. It shouldn't be a
requirement for EXECUTE BLOCK but maybe TSQLQuery does something with
it.
I've just tested this and it works fine.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQL.Clear;      SQL.Add('EXECUTE BLOCK AS BEGIN ');      for i :=
10001 to 10100 do begin          SQL.Add(Format('insert into TEST
(Code)', []));          SQL.Add(Format('values (''Text for %d'');',
[i]));      end;      SQL.Add('END');So, for me, the lines of INSERT
don't need to be on one line.
(I can't imagine this would be any different for UPDATE.)

----------------------------------------------------------------------
egsuh · 2024-03-11 14:32

QuoteSo, for me, the lines of INSERT don't need to be on one line.
(I can't imagine this would be any different for UPDATE.)
I think it should work with UPDATE as well (but I haven't tested).
I might have made some mistakes, and it's rather cumbersome to check
because DB handlings of my app are over the Internet. So for now I
believe it will work, and test again when I have another similar
situation.

----------------------------------------------------------------------
egsuh · 2024-03-12 00:34

I made a small test program, and splitting lines do not hinder correct
operations even in "UPDATE" statement. Probable other mistakes were
intermixed at the first trials.

I think EXECUTE BLOCK could be quite useful, when many operations have
to be done on the fly.

Just wondering how LAZARUS will take care of parameters. Firebird
supports parameters only in the form of

  execute block (smallest int = ?, largest int = ?)

https://firebirdsql.org/refdocs/langrefupd20-execblock.html

----------------------------------------------------------------------
ttomas · 2024-03-12 01:46

You can use
execute block (smallest int = :small, largest int = :larg)
small & larg is param names in your TQuery
smallest & largest is used in execute block

----------------------------------------------------------------------
egsuh · 2024-03-12 02:16

@ttomas,

execute block (smallest int = :small, largest int = :larg)

This runs well in Lazarus, but simply does not work in Database.

----------------------------------------------------------------------
rvk · 2024-03-12 06:14

Quote from: egsuh on March 12, 2024, 03:16:45 amexecute block
(smallest int = :small, largest int = :larg)

This runs well in Lazarus, but simply does not work in Database.
What does not work in the Database? Be more specific.
What error messages do you get from Firebird (with what SQL)?

----------------------------------------------------------------------
egsuh · 2024-03-12 06:21

QuoteWhat error messages do you get from Firebird (with what SQL)?
Please see the attached.

----------------------------------------------------------------------
rvk · 2024-03-12 06:23

Quote from: egsuh on March 12, 2024, 07:21:03 amQuoteWhat error
messages do you get from Firebird (with what SQL)?
Please see the attached.
Works fine for me. Must be something else in the SQL or calling code.

----------------------------------------------------------------------
rvk · 2024-03-12 06:28

For example... this code works fine for me.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
SQLQuery1.SQL.Add('execute block (counter int = :cnt) AS BEGIN');   
SQLQuery1.SQL.Add('     while ( counter > 0 ) do begin');   
SQLQuery1.SQL.Add('       insert into TEST (Code) values (''aa'');'); 
  SQLQuery1.SQL.Add('       counter = counter - 1;');   
SQLQuery1.SQL.Add('     end');    SQLQuery1.SQL.Add('     suspend;'); 
  SQLQuery1.SQL.Add('END');   
SQLQuery1.Params.ParamByName('cnt').AsInteger := 15;

----------------------------------------------------------------------
egsuh · 2024-03-12 06:38

What is the version of your Firebird? Mine is 3.0.

Can you test following codes for me, please?  Sorry for the
inconvenience.

I cannot run this code in the FlameRobin window (because it does not
accept parameters other than ?).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    with
SQLQuery1 do begin      SQL.Clear;      SQL.Add('EXECUTE BLOCK (lowend
int=:lend, highend int=:hend)');      SQL.Add('AS');         
SQL.Add('declare i int;');      SQL.Add('BEGIN');         
SQL.Add('Delete from Test where code >= 10000;');           SQL.Add
('i=lowend;');          SQL.Add ('while (i <= highend) do begin');   
     // SQL.Add('insert into TEST (Code, Description) values (:i,
''Text for '' || :i);');          SQL.Add('insert into TEST (Code)
values (:i);');          sQL.Add ('i = :i+1;');         
sQL.Add('end');      SQL.Add('END');       memo1.lines.text :=
SQL.text;      // exit;       Transaction.Active := True;     
ParamByName('lend').AsInteger := 10001;     
ParamByName('hend').AsInteger := 10020;       try         ExecSQL;   
    (Transaction as TSQLTransaction).Commit;       
 showmessage('success');      finally        (Transaction as
TSQLTransaction).Rollback;        showmessage('fail');      end; 
 end;

----------------------------------------------------------------------
rvk · 2024-03-12 06:56

Quote from: egsuh on March 12, 2024, 07:38:57 amWhat is the version of
your Firebird? Mine is 3.0.

Can you test following codes for me, please? First... you are using :i
for + 1. You should just use i = i + 1.

After that I also got the bad parameter number error.
The problem is probably in the fact that there is a :i inside.
It is seen (by fpc?) as an extra parameter which is defined, throwing
off the number of parameters.

If you change the :i in INSERT into a hard number, you see it works
correctly.
So it's the use of parameters INSIDE the execute block that's the
problem. Not the passing itself.

BTW. You can see that clearly by adding
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
writeln(SQLQuery1.Params.Count.ToString);It says the block has 3
parameters.

----------------------------------------------------------------------
egsuh · 2024-03-12 07:43

In the beginning, I tested with i without :. But, the result was the
attached image, which still is.

Simple statements work fine, like delete, or insert. Problem may have
something to do with variable i.

----------------------------------------------------------------------
rvk · 2024-03-12 07:49

Quote from: egsuh on March 12, 2024, 08:43:51 amIn the beginning, I
tested with i without :. But, the result was the attached image, which
still is.

Simple statements work fine, like delete, or insert. Problem may have
something to do with variable i.
The problem is using parameters inside the insert which itself is
inside a execute block.
In that case you get two sets of parameters.
Fpc should only consider the outside parameters but somehow also
includes the ones inside.

I'm not sure if that's a problem of fpc or firebird.
Normally in IBX I do a prepare and get the correct number of
parameters.
(I haven't tested this with IBX yet)

----------------------------------------------------------------------
egsuh · 2024-03-12 08:27

@rvk,

I have to write as attached to make it run in FlameRobin ISQL command
window (is this ISQL or DSQL? ^^).
Variables defined in the block need colons only is some cases...  I
cannot run this from within Lazarus TSQLQuery.

Well, I do not need this feature right now, and will take time to test
others.

Thank you for your kind help and time.  Please do not waste your time
any more.

----------------------------------------------------------------------
rvk · 2024-03-12 08:28

FYI in IBX (TIBDatabase & TIBQuery) this works correctly too.

(I normally work with IBX  )

----------------------------------------------------------------------
Zvoni · 2024-03-12 09:06

Every time i see those Query.SQL.Add lines i get stomach cramps....
I hope you have an ...SQL.Clear; as the first line every time you go
into there?

You do know, that the SQL-Property of TSQLQuery is a TStrings?

If you want to debug, check, whatever else your SQL-Statements, use a
TStringList, build your SQL-Statement there, do your checks, logging,
whatever, assign it once to the SQL-Property, and fire!

----------------------------------------------------------------------
rvk · 2024-03-12 09:21

Quote from: Zvoni on March 12, 2024, 10:06:28 amEvery time i see those
Query.SQL.Add lines i get stomach cramps....
I hope you have an ...SQL.Clear; as the first line every time you go
into there?
You do know, that the SQL-Property of TSQLQuery is a TStrings?
There are several TSQLQuery.SQL.Clear in this topic... so, yes, we
know 

I also normally build a string before assigning it to
TSQLQuery.SQL.Text.
But sometimes I do use TSQLQuery.SQL.Add (and adding the
TSQLQuery.SQL.Clear becomes second nature if you know what you're
doing)

BTW Using a TStringList to build it, instead of just a string, I
normally find a bit of overkill. A string works just as fine.

----------------------------------------------------------------------
Zvoni · 2024-03-12 10:02

Quote from: rvk on March 12, 2024, 10:21:50 amBTW Using a TStringList
to build it, instead of just a string, I normally find a bit of
overkill. A string works just as fine.
Not if you try to "blackbox" your Database-Access-Code, say, in a
separate lib (dll/so).
I found it easier to debug in the Frontend using a StringList or
TMemo, kinda like
watching in "realtime" when the user "constructs" the SQL-Statement by
choosing the offered options.
and then i just pass the Text-Property of the Stringlist/Memo to the
SQL.Text-Property of the Query-Object

----------------------------------------------------------------------
ttomas · 2024-03-12 10:41

FirebirdSQL internally accept only ? for parameters.
Named parameters :param is client library (IBX, Zeos, Lazarus,
IBPP...) implementation specific. FlameRobin is develop in CPP with
IBPP.
This is a problem with client library implementation of named
parameters or wrong SQL.

----------------------------------------------------------------------
rvk · 2024-03-12 10:53

Quote from: ttomas on March 12, 2024, 11:41:45 amFirebirdSQL
internally accept only ? for parameters.
Named parameters :param is client library (IBX, Zeos, Lazarus,
IBPP...) implementation specific. FlameRobin is develop in CPP with
IBPP.
This is a problem with client library implementation of named
parameters or wrong SQL.
Yep. I was expecting sqldb to do a prepare and retrieve the number of
parameters but apparently there it goes wrong.
With IBX it works correctly so it probably does query the server for
the needed parameters.

BTW... Firebird does work with named parameters at some points.
See https://firebirdsql.org/refdocs/langrefupd25-psql-execstat.html
Quote*) Named parameters must be preceded by a colon (“:”) in the
statement itself, but not in the parameter assignments.But not for
EXECUTE BLOCK:
QuoteThe server only accepts question marks (“?”) as placeholders for
the input values, not “:a”, “:MyParam” etc., or literal values. Client
software may support the “:xxx” form though, which it will preprocess
before sending it to the server.

----------------------------------------------------------------------
Nicole · 2024-03-12 15:23

The best people in this forum answered you and they will make this
block work, by sure.

However those people wrote a thing, which I use for Firebird with
great success.
https://www.mwasoftware.co.uk/downloads/category/5-ibx-current

----------------------------------------------------------------------
egsuh · 2024-03-15 03:15

QuoteHowever those people wrote a thing, which I use for Firebird with
great success.
https://www.mwasoftware.co.uk/downloads/category/5-ibx-current

Thank you for your advice, but I know of IBX and had tried it already.

I use TSQLQuery because it and TBufdataset share TCustomBufDataSet,
which means both share codes for savetostream and loadfromstream, so
that I can construct so-called-by-Delphi briefcase model --- I need
this because the dataset that my application operates on is not
directly connected to the Database.