Home Multiple statements Delphi TZquery (Zeos) error
Reply: 0

Multiple statements Delphi TZquery (Zeos) error

user3854
1#
user3854 Published in June 18, 2018, 3:38 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.329289 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO