多个语句Delphi TZquery(Zeos)错误 [英] Multiple statements Delphi TZquery (Zeos) error

问题描述

im试图像这样进行多语句查询:

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;

我收到此错误消息:SQL错误:您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第2行的插入客户端(名称，年龄)值("doe"，"21")"附近使用；

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;

我已经检查了手册TZQuery和TZUpdateSql组件(来自zeos lib)，可以在内部执行多个语句.

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

谢谢GregD，经过几次测试，交易对我来说很好！ 这就是我将来用来帮助他人的方式.

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;

这是Zeos中AutoCommit属性的实际工作方式:

AutoCommit 为True时，将在每个执行的SQL语句之后自动提交事务，但是您可以明确地使用StartTransaction命令来防止自动提交，直到您明确地调用Commit.

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.

自动提交 为False时，不应调用StartTransaction.然后，该事务将自动启动，但不会在每个执行的语句之后自动提交.

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.

过程 StartTransaction StartTransaction过程在连接的数据库内启动新事务.仅当AutoCommit属性为TRUE时才应使用它.每当您尝试在AutoCommit设置为false的情况下调用它时，都会引发SInvalidOpInNonAutoCommit.此行为是预期的，因为应将StartTransaction用作对AutoCommit模式的转义.当您调用StartTransaction时，自动提交"被关闭"，然后，当您调用提交"或回滚"时，自动提交被打开".再次.如果将AutoCommit设置为false，则会自动创建新事务，并选择如何关闭它们(Commit或Rollback).

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

过程提交将当前语句提交到数据库.仅应在非AutoCommit模式(其中每个语句都自动提交，使该过程无用)或在AutoCommit模式下并想完成由StartTransaction过程打开的事务时使用.提交完成当前事务(如果有).如果您不想将满足条件保存到数据库中，则应使用回滚"过程.

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.

过程回滚回滚当前事务中的所有先前语句.仅应在非AutoCommit模式(其中每个语句都自动提交，使该过程无用)或在AutoCommit模式下并想完成由StartTransaction过程打开的事务时使用.如果有回滚，则回滚完成当前事务.如果您不想失去满足感，则应使用提交"过程.

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.

推荐答案

尝试以下代码，让我们知道是否出现相同的问题:

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;

通过这种方式，您还可以加快MySQL处理此INSERT查询的速度，就像分批进行而不是两次进行一样.

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

编辑#1:

我不是Zeos方面的专家，但是使用其他语言时，您可以尝试逐个执行查询:

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;

编辑#2 :交易

一个关于Stackoverflow的问题有许多关于在MySQL中使用事务的很好的例子.尽管这些示例是为PHP编写的，但我确信您可以在其中找到一些不错的指针.确保您在MySQL服务器上的表是InnoDB而不是MyISAM.

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.

这篇关于多个语句Delphi TZquery(Zeos)错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！