Multiple statements Delphi TZquery (Zeos) error

Question

im trying to make a multiple statement query like this :

// without the second insert the query works fine.
// i need 2 querys to work because later, i'll do inserts on different kind of tables.
// that's why i need 2 querys, not a single query which insert 2 records.   

with ZQuery1 do
    begin
        SQL.Clear;
        SQL.Add('insert into client (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+');');
        SQL.Add('insert into client (name,age) values ('+QuotedStr('doe')+','+QuotedStr('21')+');');
        ExecSQL;
    end;

i got this error message : SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into client (name,age) values ('doe','21')' at line 2;

i already check the manual, The components TZQuery and TZUpdateSql (from zeos lib ) provide the possibility to execute multiple statements, internally.

EDIT [SOLVED]

Thank you GregD, after run several tests, transactions works fine for me! that's how i use, to help others in the future.

try
    ZConnection.AutoCommit := True;
    ZConnection.StartTransaction;
    
    With ZQuery Do
    begin
        SQL.Clear;
        SQL.Add('insert into clients (name,age) values ('+QuotedStr('john')+','+QuotedStr('20')+')');
        ExecSQL;
        SQL.Clear;
        SQL.Add('insert into clients (name,age) values ('+QuotedStr('doe')+','+QuotedStr('21')+')');
        ExecSQL;
    end;
    
    ZConnection.Commit; 
except
    ZConnection.Rollback
end;

This is how AutoCommit property actually works in Zeos:

when AutoCommit is True, then the transactions are commited automatically after each executed SQL statement, but you can use the StartTransaction command explicitely to prevent this auto commiting, until you explicitely call Commit.

when AutoCommit is False, you should not call StartTransaction. Then the transaction is started automatically, but it will not commit automatically after every executed statement.

procedure StartTransaction The StartTransaction procedure starts a new transaction within the connected database. It should be only used when AutoCommit property is TRUE. Whenever you try to call it with AutoCommit set to false, an SInvalidOpInNonAutoCommit will be raised. This behaviour is expected, as StartTransaction should be used as a escape to the AutoCommit mode. When you call StartTransaction, the AutoCommit is "turned off", and then, when you call Commit or Rollback, the AutoCommit is "turned on" again. If you're working with AutoCommit set to false, new transactions are created automatically and you choose how you will close them (Commit or Rollback).

procedure Commit Commit current statements to the database. Should be used only in non-AutoCommit mode (where every statement is auto-commited, making this procedure useless) or when you are in AutoCommit mode and want to finish a transaction opened by StartTransaction procedure. Commiting finishes the current transaction, if there's any. If you don't want to save your satatements to the database, you should use the Rollback procedure.

procedure Rollback Rolls back all previous statements in current transaction. Should be used only in non-AutoCommit mode (where every statement is auto-commited, making this procedure useless) or when you are in AutoCommit mode and want to finish a transaction opened by StartTransaction procedure. The Rollback finishes the current transaction, if there's any. If you don't want to loose your satatements, you should use the Commit procedure.

Answer 1

I have no idea about Zeos and multiple statements, but that's not really the problem here. You've caused a major security issue with your query (SQL injection) and a slow method of performing them at all (concatenated strings that can't be cached and reused).

If you properly stop using string concatenation to form your queries, and use parameterized statements instead, you don't need to worry about the multiple statements at all:

with ZQuery1 do
begin
  SQL.Clear;
  SQL.Add('insert into client (name,age)');
  SQL.Add('values (:Name, :Age);'
  ParamByName('Name').AsString := 'John';
  ParamByName('Age').AsInteger := 20;
  ExecSQL;
  ParamByName('Name').AsString := 'Doe';
  ParamByName('Age').AsInteger :- 21;
  ExecSQL;
end;

The query will now run faster (because the DBMS can compile it once and reuse it multiple times (the "caching" I mentioned), you don't have the SQL injection risk any longer, and the multiple statements are no longer needed.