事务中的多个参数化的Delphi SQL更新 [英] Multiple parameterized Delphi SQL updates within a transaction

问题描述

我正在尝试使用Delphi XE8中的参数化查询在同一循环中更新两个不同的SQL表.我还想将整个事情包装在一个事务中，这样，如果循环中的任何事情失败，则不会更新任何表.

I am trying to update two different SQL tables in the same loop using parameterized queries in Delphi XE8. I also want to wrap the whole thing in a transaction, so that if anything in the loop fails, neither table gets updated.

我真的不知道我在做什么，不胜感激.

I don't really know what I'm doing, would appreciate some help.

下面的代码是我要实现的目标的简化版本，也是有关如何实现的最佳猜测.但是我一点也不十分确定，特别是使用两个连接到"SQL连接"组件的数据集.

The code below is a simplified version of what I'm trying to achieve, and my best guess as to how to go about it. But I'm not really sure of it at all, particularly the use of two datasets connected to the 'SQL connection' component.

SQL_transaction.TransactionID :=1;
SQL_transaction.IsolationLevel:=xilREADCOMMITTED;
SQL_connection.BeginTransaction;
Try
  { Create connections }

  SQL_dataset1              :=TSQLDataSet.Create(nil);  
  SQL_dataset1.SQLConnection:=SQL_connection;

  SQL_dataset2              :=TSQLDataSet.Create(nil);  
  SQL_dataset2.SQLConnection:=SQL_connection;

  { Create queries }

  SQL_dataset1.CommandType:=ctQuery;
  SQL_dataset1.CommandText:={ some parameterized query updating table A }

  SQL_dataset2.CommandType:=ctQuery;
  SQL_dataset2.CommandText:={ some parameterized query updating table B }

  { Populate parameters and execute }

  For I:=0 to whatever do
  begin
    SQL_dataset1.ParamByName('Table A Field 1').AsString:='Value';
    SQL_dataset1.ExecSQL; 

    SQL_dataset2.ParamByName('Table B Field 1').AsString:='Value';
    SQL_dataset2.ExecSQL; 
  end;

  SQL_connection.Commit(SQL_transaction);
except
  SQL_connection.Rollback(SQL_transaction);  
end;

我正在使用Delphi XE8，数据库可以是SQL Server或SQLite.

I am using Delphi XE8, and the database can be either SQL server or SQLite.

推荐答案

事务处理的逻辑是正确的(除了@whosrdaddy提到的缺少的异常重新引发).数据集实例缺少 try..finally 块有什么问题.除非您应该停止使用 TSQLConnection 使用 TTransactinDesc 记录的不推荐使用的方法(在构建应用程序时始终检查编译器警告.).您还可以切换到 TSQLQuery 组件.尝试这样的事情:

The logic of your transaction handling is correct (except the missing exception re-raise mentioned by @whosrdaddy). What is wrong are missing try..finally blocks for your dataset instances. Except that you should stop using TSQLConnection deprecated methods that are using the TTransactinDesc record (always check the compiler warnings when you're building your app.). And you can also switch to TSQLQuery component. Try something like this instead:

var
  I: Integer;
  Query1: TSQLQuery;
  Query2: TSQLQuery;
  Connection: TSQLConnection;
  Transaction: TDBXTransaction;
begin
  ...  
  Query1 := TSQLQuery.Create(nil);
  try
    Query1.SQLConnection := Connection;
    Query1.SQL.Text := '...';

    Query2 := TSQLQuery.Create(nil);
    try
      Query2.SQLConnection := Connection;
      Query2.SQL.Text := '...';

      Transaction := Connection.BeginTransaction;
      try
        // fill params here and execute the commands
        for I := 0 to 42 to
        begin
          Query1.ExecSQL;
          Query2.ExecSQL;
        end;
        // commit if everything went right
        Connection.CommitFreeAndNil(Transaction);
      except
        // rollback at failure, and re-raise the exception
        Connection.RollbackFreeAndNil(Transaction);
        raise;
      end;
    finally
      Query2.Free;
    end;
  finally
    Query1.Free;
  end;
end;

这篇关于事务中的多个参数化的Delphi SQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！