Zeos TZQuery and Sqlite3 database.

[BSaidus]

Hello.
I wonder if there is a way to put multiple inserts in a ZQuery component.
Code like:

Code: Pascal  [Select][+][-]

1.   self.ZQuery1.Close;
2.   self.ZQuery1.SQL.Clear;
3.   self.ZQuery1.SQL.Add('BEGIN TRANSACTION;');
4.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 1 WHERE id = 1;');
5.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 2 WHERE id = 2;');
6.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 3 WHERE id = 3;');
7.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 4 WHERE id = 4;');
8.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 5 WHERE id = 5;');
9.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 6 WHERE id = 6;');
10.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 7 WHERE id = 7;');
11.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 8 WHERE id = 8;');
12.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 9 WHERE id = 9;');
13.   self.ZQuery1.SQL.Append('UPDATE customers SET state = 10 WHERE id = 10;');
14.   self.ZQuery1.SQL.Append('COMMIT TRANSACTION;');
15.  
16.   Memo1.Append(self.ZQuery1.SQL.Text);
17.  
18.   try
19.     self.ZQuery1.ExecSQL;
20.   except on E: Exception do
21.     begin
22.       Memo1.Append(e.Message);
23.     end;
24.   end;
25.   ZTable1.Refresh;
26.  

The code executes without error, but no changes are made.
Thank you.

[Hansvb]

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

Code: Pascal  [Select][+][-]

1. procedure TForm1.BitBtn4Click(Sender : TObject);
2. var
3.   i: Integer;
4.   SQLtext: String;
5. begin
6.   ZConnection1.Connected:= True;
7.   for i:= 1 to 10 do begin
8.     SQLtext:= 'update CUSTOMERS set STATE = :STATE where ID = :ID;';
9.     ZQuery1.SQL.Text:= SQLtext;
10.     ZQuery1.ParamByName('ID').AsInteger:= i;
11.     ZQuery1.ParamByName('STATE').AsInteger:= i;
12.     ZQuery1.ExecSQL;
13.     // autocommit is on so
14.   end;
15.  
16.   ZConnection1.Connected:= False;
17. end;  

[BSaidus]

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

Code: Pascal  [Select][+][-]

1. procedure TForm1.BitBtn4Click(Sender : TObject);
2. var
3.   i: Integer;
4.   SQLtext: String;
5. begin
6.   ZConnection1.Connected:= True;
7.   for i:= 1 to 10 do begin
8.     SQLtext:= 'update CUSTOMERS set STATE = :STATE where ID = :ID;';
9.     ZQuery1.SQL.Text:= SQLtext;
10.     ZQuery1.ParamByName('ID').AsInteger:= i;
11.     ZQuery1.ParamByName('STATE').AsInteger:= i;
12.     ZQuery1.ExecSQL;
13.     // autocommit is on so
14.   end;
15.  
16.   ZConnection1.Connected:= False;
17. 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]

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]

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

Of course, you can also do this with a single query consisting of several UPDATEs, such as

Code: Pascal  [Select][+][-]

1.       self.ZQuery1.Close;
2.       ZQuery1.SQL.Clear;
3.  
4.       ZConnection1.StartTransaction; //add this
5.  
6.       ZQuery1.SQL.Add('UPDATE customers SET state = 1 WHERE id = 1;');
7.       ZQuery1.SQL.Add('UPDATE customers SET state = 2 WHERE id = 2;');
8.       ZQuery1.SQL.Add('UPDATE customers SET state = 3 WHERE id = 3;');
9.       ZQuery1.SQL.Add('UPDATE customers SET state = 4 WHERE id = 4;');
10.       ZQuery1.SQL.Add('UPDATE customers SET state = 5 WHERE id = 5;');
11.       ZQuery1.SQL.Add('UPDATE customers SET state = 6 WHERE id = 6;');
12.       ZQuery1.SQL.Add('UPDATE customers SET state = 7 WHERE id = 7;');
13.       ZQuery1.SQL.Add('UPDATE customers SET state = 8 WHERE id = 8;');
14.       ZQuery1.SQL.Add('UPDATE customers SET state = 9 WHERE id = 9;');
15.       ZQuery1.SQL.Ads('UPDATE customers SET state = 10 WHERE id = 10;');
16.      
17.       ZConnection1.Commit; //and this
18.      
19.       Memo1.Append(self.ZQuery1.SQL.Text);
20.      
21.       try
22.         ZQuery1.ExecSQL;
23.       except on E: Exception do
24.         begin
25.           Memo1.Append(e.Message);
26.         end;
27.       end;
28.       ZTable1.Refresh;
29.      

[BSaidus]

Of course, you can also do this with a single query consisting of several UPDATEs, such as

Code: Pascal  [Select][+][-]

1.       self.ZQuery1.Close;
2.       ZQuery1.SQL.Clear;
3.  
4.       ZConnection1.StartTransaction; //add this
5.  
6.       ZQuery1.SQL.Add('UPDATE customers SET state = 1 WHERE id = 1;');
7.       ZQuery1.SQL.Add('UPDATE customers SET state = 2 WHERE id = 2;');
8.       ZQuery1.SQL.Add('UPDATE customers SET state = 3 WHERE id = 3;');
9.       ZQuery1.SQL.Add('UPDATE customers SET state = 4 WHERE id = 4;');
10.       ZQuery1.SQL.Add('UPDATE customers SET state = 5 WHERE id = 5;');
11.       ZQuery1.SQL.Add('UPDATE customers SET state = 6 WHERE id = 6;');
12.       ZQuery1.SQL.Add('UPDATE customers SET state = 7 WHERE id = 7;');
13.       ZQuery1.SQL.Add('UPDATE customers SET state = 8 WHERE id = 8;');
14.       ZQuery1.SQL.Add('UPDATE customers SET state = 9 WHERE id = 9;');
15.       ZQuery1.SQL.Ads('UPDATE customers SET state = 10 WHERE id = 10;');
16.      
17.       //ZConnection1.Commit; //and this    -> this do not anything, What commit & you never execute Query.
18.      
19.       Memo1.Append(self.ZQuery1.SQL.Text);
20.      
21.       try
22.         ZQuery1.ExecSQL;
23.         ZConnection1.Commit;   // Here possible it works.
24.       except on E: Exception do
25.         begin
26.           ZConnection1.Rollback;   // if any exception happen
27.           Memo1.Append(e.Message);
28.         end;
29.       end;
30.       ZTable1.Refresh;
31.      

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

[Sieben]

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]

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