Zeos TZQuery and Sqlite3 database.
lazarus / 16-Databases

----------------------------------------------------------------------
BSaidus · 2025-09-19 18:46

Hello.
I wonder if there is a way to put multiple inserts in a ZQuery
component.
Code like:
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  
self.ZQuery1.Close;  self.ZQuery1.SQL.Clear; 
self.ZQuery1.SQL.Add('BEGIN TRANSACTION;'); 
self.ZQuery1.SQL.Append('UPDATE customers SET state = 1 WHERE id =
1;');  self.ZQuery1.SQL.Append('UPDATE customers SET state = 2 WHERE
id = 2;');  self.ZQuery1.SQL.Append('UPDATE customers SET state = 3
WHERE id = 3;');  self.ZQuery1.SQL.Append('UPDATE customers SET state
= 4 WHERE id = 4;');  self.ZQuery1.SQL.Append('UPDATE customers SET
state = 5 WHERE id = 5;');  self.ZQuery1.SQL.Append('UPDATE customers
SET state = 6 WHERE id = 6;');  self.ZQuery1.SQL.Append('UPDATE
customers SET state = 7 WHERE id = 7;'); 
self.ZQuery1.SQL.Append('UPDATE customers SET state = 8 WHERE id =
8;');  self.ZQuery1.SQL.Append('UPDATE customers SET state = 9 WHERE
id = 9;');  self.ZQuery1.SQL.Append('UPDATE customers SET state = 10
WHERE id = 10;');  self.ZQuery1.SQL.Append('COMMIT TRANSACTION;');  
Memo1.Append(self.ZQuery1.SQL.Text);   try    self.ZQuery1.ExecSQL; 
except on E: Exception do    begin      Memo1.Append(e.Message);   
end;  end;  ZTable1.Refresh; 
The code executes without error, but no changes are made.
Thank you.

----------------------------------------------------------------------
Hansvb · 2025-09-20 07:48

I have made a quick and dirty example where update works. See
BitBtn4Click in the example

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
TForm1.BitBtn4Click(Sender : TObject);var  i: Integer;  SQLtext:
String;begin  ZConnection1.Connected:= True;  for i:= 1 to 10 do
begin    SQLtext:= 'update CUSTOMERS set STATE = :STATE where ID =
:ID;';    ZQuery1.SQL.Text:= SQLtext;   
ZQuery1.ParamByName('ID').AsInteger:= i;   
ZQuery1.ParamByName('STATE').AsInteger:= i;    ZQuery1.ExecSQL;    //
autocommit is on so  end;   ZConnection1.Connected:= False;end;

----------------------------------------------------------------------
BSaidus · 2025-09-20 08:29

Quote from: Hansvb on September 20, 2025, 09:48:12 amI have made a
quick and dirty example where update works. See BitBtn4Click in the
example

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
TForm1.BitBtn4Click(Sender : TObject);var  i: Integer;  SQLtext:
String;begin  ZConnection1.Connected:= True;  for i:= 1 to 10 do
begin    SQLtext:= 'update CUSTOMERS set STATE = :STATE where ID =
:ID;';    ZQuery1.SQL.Text:= SQLtext;   
ZQuery1.ParamByName('ID').AsInteger:= i;   
ZQuery1.ParamByName('STATE').AsInteger:= i;    ZQuery1.ExecSQL;    //
autocommit is on so  end;   ZConnection1.Connected:= False;end; 
Hello @Hansvb,
Thank you for your effort, I know how to deal with this using the
methode you presented, But, I want to execute a block or sql
statements in one shot, just like I posted.
I can realize it using TZSQLProcessor witch is suitable to do that. I
think TZQuery can execute Multiple sql statements in one shot.

Thanks you.

----------------------------------------------------------------------
paweld · 2025-09-20 09:17

You can do this using a transaction in ZConnection, you just have to
start and end the transaction yourself. In the attachment, there is a
modified example by @Hansvb, where you can compare the execution times
for a query per transaction and all queries in one transaction.

----------------------------------------------------------------------
CharlyTango · 2025-09-20 13:47

@BSaidus could you explain why you want to process SQL statements this
way?
There must be a good reason.

I'm my opinion TZSQLProcessor does it by slicing one statement out of
the SQL text and fires it against the DB. Nothing else.
For me there is no real advantage instead of doing it myself.

----------------------------------------------------------------------
paweld · 2025-09-20 14:34

Of course, you can also do this with a single query consisting of
several UPDATEs, such as
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      
self.ZQuery1.Close;      ZQuery1.SQL.Clear;      
ZConnection1.StartTransaction; //add this      
ZQuery1.SQL.Add('UPDATE customers SET state = 1 WHERE id = 1;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 2 WHERE id = 2;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 3 WHERE id = 3;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 4 WHERE id = 4;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 5 WHERE id = 5;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 6 WHERE id = 6;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 7 WHERE id = 7;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 8 WHERE id = 8;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 9 WHERE id = 9;');     
ZQuery1.SQL.Ads('UPDATE customers SET state = 10 WHERE id = 10;');   
     ZConnection1.Commit; //and this          
Memo1.Append(self.ZQuery1.SQL.Text);           try       
ZQuery1.ExecSQL;      except on E: Exception do        begin         
Memo1.Append(e.Message);        end;      end;      ZTable1.Refresh;

----------------------------------------------------------------------
BSaidus · 2025-09-20 17:15

Quote from: paweld on September 20, 2025, 04:34:40 pmOf course, you
can also do this with a single query consisting of several UPDATEs,
such as
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      
self.ZQuery1.Close;      ZQuery1.SQL.Clear;      
ZConnection1.StartTransaction; //add this      
ZQuery1.SQL.Add('UPDATE customers SET state = 1 WHERE id = 1;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 2 WHERE id = 2;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 3 WHERE id = 3;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 4 WHERE id = 4;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 5 WHERE id = 5;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 6 WHERE id = 6;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 7 WHERE id = 7;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 8 WHERE id = 8;');     
ZQuery1.SQL.Add('UPDATE customers SET state = 9 WHERE id = 9;');     
ZQuery1.SQL.Ads('UPDATE customers SET state = 10 WHERE id = 10;');   
     //ZConnection1.Commit; //and this    -> this do not anything,
What commit & you never execute Query.          
Memo1.Append(self.ZQuery1.SQL.Text);           try       
ZQuery1.ExecSQL;        ZConnection1.Commit;   // Here possible it
works.      except on E: Exception do        begin         
ZConnection1.Rollback;   // if any exception happen         
Memo1.Append(e.Message);        end;      end;      ZTable1.Refresh; 
 

I'll test this, but I think , the ZQuery can not execute multiple
statements.

----------------------------------------------------------------------
Sieben · 2025-09-20 19:28

Same with Lazarus own SQLdb btw - while TSQLQuery can not process
multiple statements, the TSQLConnection classes - which anyway handle
all database specific stuff - usually can. The TQuery components are
just front end containers using TSQLConnections to do all the
communications with the database server. TSQLConnection.ExecuteDirect
is the method to look at. Might be similar with Zeos.

----------------------------------------------------------------------
GAN · 2025-09-20 22:50

Quote from: Sieben on September 20, 2025, 09:28:01 pm...
TSQLConnection.ExecuteDirect is the method to look at. Might be
similar with Zeos.

I use Zeos and can confirm that Zeos uses the same method.
TZconnection.ExecuteDirect should solve your issue.