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.

Answer 2

Try this code and let us know if the same problem arises:

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

This way you can also speed up the MySQL handling of this INSERT query, as it does in one batch and not twice.

EDIT #1:

I'm not an expert in Zeos, but with other languages, you could try to execute the query one by one:

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

EDIT #2: Transactions

One question on Stackoverflow has many good examples about using transactions in MySQL. Although, the examples are written for PHP, I'm sure you could find some good pointers there. Make sure that your tables on the MySQL server are InnoDB not MyISAM.

Answer 3

I'm not an expert in ZEOS either, but looking at the source, have you set MultiStatements property of TZUpdateSQL to true?

Answer 4

Have you tried TZSQLProcessor? Said that the component was made for such needs ( as in ZSqlProcessor.pas unit):

{**
  Implements a unidatabase component which parses and executes SQL Scripts.
}