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.

Osage

OT: if you're having Enterprise, Ultimate or Architect edition of Delphi or RAD Studio, try FireDAC. – Confect

Does this work without the second Insert? – Birefringence

yes, without the second insert it works like a charm :) – Osage

I always wonder who was that dumb that initiated this ugly SQL.Clear; SQL.Add(...); pattern instead of shorter SQL.Text := '...'. Like all dumb things it is ridiculously durable. – Maladminister

Answer 1

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.

Diatropism

your answer is workable and good but the question was about using multiple insert statements... But good try :) – Birefringence

Yes, but his DBA would probably prefer the multiple row INSERT statement ;-) – Diatropism

Good point. I agree. I actually had this question myself and wasn't sure if multiple queries were supported with Zeos. I don't have it on my Linux OS right now to test. Does a multiple query work with it? Just curious. Thanks! Oh, and +1 for your answer. – Birefringence

@Osage I understand that GregD's answer is not exactly what you were looking at. But is there any reason why you cannot simply create another query statement and execute it? Is there a significant gain in trying to run 2 queries in one execute statement? Just thinking aloud – Birefringence

Sorry GregD, your answer is good but 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. – Osage

@Birefringence yes, there's a reason, with multiple executions, there is the possibility of lost internet connection between executions. then some executions will not be completed and it is unacceptable. – Osage

@Rebelss, why not then use transactions? Transactions are ACID compliant and would make sure that the execution is fully completed. – Diatropism

@Osage I ain't a Zeos expert but from concepts, I don't think that a mulitple insert query would solve the problem of a lost connection. Rather, like GregD has suggested, using a Transaction seems most appropriate. And with transactions in place, it shouldn't matter even if they were multiple execute statements. – Birefringence

@itsols, thanks to you too, as GregD suggested, using Transactions works fine for me. – Osage

Answer 2

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.

Eudosia

+1, best answer so far as you address both the OP problem as well as other potential problems. – Messalina

Is this equal to insert into client (name,age) values(:Name1, :Age1), (:Name2, :Age2) in term of speed? – Sur

@SAMPro: It would depend. You'd have to test it yourself. The SQL is compiled and cached after the first execution, so the only part that would change would be the values of the parameters. I'd suspect that would be pretty fast, particularly if you're only inserting a couple of rows. If you're doing a lot of rows, I'd think the performance issue of creating the SQL and parameters and populating them would be somewhat slow as well. As usual, the answer is: benchmark them yourself to compare, using your own data and hardware, and decide which works best for you. – Eudosia

I doubt about MySQL side performance. With ur solution e.g MySQL needs to lock the table multiple times but if you prepare and exec one query it's will be much faster. And you right, for inserting couple of rows its not different. – Sur

@SAMPro: As I said (and will repeat for you): As usual, the answer is: benchmark them yourself to compare, using your own data and hardware, and decide which works best for you. – Eudosia

Answer 3

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

Dishevel

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

Tunnage

you saved my life. I was looking for something like that, but the docs are pretty poor – Iggy