使用ADO使用GO语句执行大型SQL脚本文件

我编写了一个小组件（仍在工作，但仍在工作），该组件需要一个大型SQL脚本文件，并根据GO语句将其拆分为不同的“块”，然后逐个执行它们。

我知道的主要缺陷是无法检测到GO语句何时位于注释块中，例如...

/*
GO
*/

还有其他问题吗？

注意：执行是由A）将TADOConnection分配给Connection属性，B）将脚本加载到SQL属性中，以及C）调用Execute函数。

 unit SQLExec;

interface

uses
  Windows, Classes, SysUtils, DB, ADODB,
  Dialogs;

const
  SE_ERR_NONE = 0;
  SE_ERR_UNKNOWN = 1;
  SE_ERR_CONNECTION_FAIL = 2;
  SE_ERR_INVALID_CONNECTION = 3;
  SE_ERR_PARSE = 4;
  SE_ERR_EXECUTE = 5;

type
  ESQLExecScriptException = class;
  TSQLExecBlock = class;
  TSQLExecBlocks = class;
  TSQLExec = class;


  ESQLExecScriptException = class(Exception)
  private
    FErrorCode: Integer;
    FBlock: TSQLExecBlock;
  public
    constructor Create(const Msg: string; const ErrCode: Integer;
      ABlock: TSQLExecBlock);
    property ErrorCode: Integer read FErrorCode write FErrorCode;
    property Block: TSQLExecBlock read FBlock;
  end;


  TSQLExecStatus = (sePending, seExecuting, seSuccess, seFail);
  TSQLExecResult = (srSuccess, srConnFail, srSQLFail);

  TSQLExecOption = (soUseTransactions, soAbortOnFail, soForceParse);
  TSQLExecOptions = set of TSQLExecOption;

  TSQLBlockEvent = procedure(Sender: TSQLExec; Block: TSQLExecBlock) of object;

  TSQLExecBlock = class(TObject)
  private
    FOwner: TSQLExecBlocks;
    FSQL: TStringList;
    FStatus: TSQLExecStatus;
    FLine: Integer;
    FMessage: String;
    function GetSQL: TStrings;
    procedure SetSQL(const Value: TStrings);
    function GetIndex: Integer;
  public
    constructor Create(AOwner: TSQLExecBlocks);
    destructor Destroy; override;
    property Index: Integer read GetIndex;
    property Status: TSQLExecStatus read FStatus;
    property SQL: TStrings read GetSQL write SetSQL;
    property Line: Integer read FLine;
    property Message: String read FMessage;
  end;

  TSQLExecBlocks = class(TObject)
  private
    FOwner: TSQLExec;
    FItems: TList;
    function GetItem(Index: Integer): TSQLExecBlock;
  public
    constructor Create(AOwner: TSQLExec);
    destructor Destroy; override;
    function Add: TSQLExecBlock;
    procedure Delete(const Index: Integer);
    function Count: Integer;
    function IndexOf(ABlock: TSQLExecBlock): Integer;
    procedure Clear;
    property Items[Index: Integer]: TSQLExecBlock read GetItem; default;
  end;

  TSQLExec = class(TComponent)
  private
    FSQL: TStringList;
    FBlocks: TSQLExecBlocks;
    FConnection: TADOConnection;
    FOptions: TSQLExecOptions;
    FParsed: Boolean;
    FOnBlockStart: TSQLBlockEvent;
    FOnBlockFinish: TSQLBlockEvent;
    FSplitWord: String;
    function GetSQL: TStrings;
    procedure SetSQL(const Value: TStrings);
    procedure SetConnection(const Value: TADOConnection);
    procedure SQLChanged(Sender: TObject);
    procedure Invalidate;
    procedure SetSplitWord(const Value: String);
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure ParseSQL;
    function Execute: TSQLExecResult;
    function LineCount: Integer;
    function BlockCount: Integer;
    property Parsed: Boolean read FParsed;
    property Blocks: TSQLExecBlocks read FBlocks;
  published
    property SQL: TStrings read GetSQL write SetSQL;
    property Connection: TADOConnection read FConnection write SetConnection;
    property Options: TSQLExecOptions read FOptions write FOptions;
    property SplitWord: String read FSplitWord write SetSplitWord;
    property OnBlockStart: TSQLBlockEvent read FOnBlockStart write FOnBlockStart;
    property OnBlockFinish: TSQLBlockEvent read FOnBlockFinish write FOnBlockFinish;
  end;


implementation

{ ESQLExecScriptError }

constructor ESQLExecScriptException.Create(const Msg: string;
  const ErrCode: Integer; ABlock: TSQLExecBlock);
begin
  inherited Create(Msg);
  ErrorCode := ErrCode;
  FBlock:= ABlock;
end;

{ TSQLExecBlock }

constructor TSQLExecBlock.Create(AOwner: TSQLExecBlocks);
begin
  FOwner:= AOwner;
  FSQL:= TStringList.Create;
  FStatus:= sePending;
  FMessage:= '';
end;

destructor TSQLExecBlock.Destroy;
begin
  FSQL.Free;
  inherited;
end;

function TSQLExecBlock.GetIndex: Integer;
begin
  Result:= FOwner.FItems.IndexOf(Self);
end;

function TSQLExecBlock.GetSQL: TStrings;
begin
  Result:= TStrings(FSQL);
end;

procedure TSQLExecBlock.SetSQL(const Value: TStrings);
begin
  FSQL.Assign(Value);
end;

{ TSQLExecBlocks }

constructor TSQLExecBlocks.Create(AOwner: TSQLExec);
begin
  FOwner:= AOwner;
  FItems:= TList.Create;
end;

destructor TSQLExecBlocks.Destroy;
begin
  Clear;
  FItems.Free;
  inherited;
end;

function TSQLExecBlocks.Add: TSQLExecBlock;
begin
  Result:= TSQLExecBlock.Create(Self);
  FItems.Add(Result);
end;

procedure TSQLExecBlocks.Clear;
begin
  while Count > 0 do
    Delete(0);
end;

function TSQLExecBlocks.Count: Integer;
begin
  Result:= FItems.Count;
end;

procedure TSQLExecBlocks.Delete(const Index: Integer);
begin
  TSQLExecBlock(FItems[Index]).Free;
  FItems.Delete(Index);
end;

function TSQLExecBlocks.GetItem(Index: Integer): TSQLExecBlock;
begin
  Result:= TSQLExecBlock(FItems[Index]);
end;

function TSQLExecBlocks.IndexOf(ABlock: TSQLExecBlock): Integer;
begin
  Result:= FItems.IndexOf(ABlock);
end;

{ TSQLExec }

constructor TSQLExec.Create(AOwner: TComponent);
begin
  inherited;
  FSQL:= TStringList.Create;
  FSQL.OnChange:= SQLChanged;
  FBlocks:= TSQLExecBlocks.Create(Self);
  FConnection:= nil;
  FOptions:= [soUseTransactions,soAbortOnFail];
  FSplitWord:= 'go';
end;

destructor TSQLExec.Destroy;
begin
  FBlocks.Free;
  FSQL.Free;
  inherited;
end;

procedure TSQLExec.ParseSQL;
var
  X: Integer;
  S: String;
  B: TSQLExecBlock;
begin
  FBlocks.Clear;
  B:= FBlocks.Add;        //Add first block
  B.FLine:= 0;            //Assign the starting line # of block
  try
    for X := 0 to FSQL.Count - 1 do begin
      S:= FSQL[X];          //Get copy of line to string
      if Pos('use ', LowerCase(Trim(S))) = 1 then begin
        //FSQL[X]:= '';       //Temporarily disabled
      end else
      if SameText(FSplitWord, Trim(S)) then begin
        B:= FBlocks.Add;    //Add a new block
        B.FLine:= X;        //Assign the starting line # of block
      end else begin
        B.SQL.Append(S);    //Add SQL script to current block
      end;
    end;
    FParsed:= True;
  except
    on e: Exception do begin
      raise ESQLExecScriptException.Create(e.Message, SE_ERR_PARSE, B);
    end;
  end;
end;

function TSQLExec.Execute: TSQLExecResult;
var
  B: TSQLExecBlock;
  X: Integer;
  R: Integer;
  EM: String;
begin
  Result:= srSuccess;
  if (soForceParse in FOptions) or (not FParsed) then
    ParseSQL;
  //Begin transaction if configured
  if soUseTransactions in FOptions then
    FConnection.BeginTrans;
  try
    if not FConnection.Connected then begin
      try
        FConnection.Connected:= True;
      except
        on e: Exception do begin
          Result:= srConnFail;
          EM:= 'Error connecting to database: '+e.Message;
          raise ESQLExecScriptException.Create(EM, SE_ERR_CONNECTION_FAIL, nil);
        end;
      end;
    end;
    for X := 0 to FBlocks.Count-1 do begin
      B:= FBlocks[X];
      B.FStatus:= seExecuting;
      if Assigned(FOnBlockStart) then
        FOnBlockStart(Self, B);
      try
        if Trim(B.SQL.Text) <> '' then begin
          FConnection.Execute(B.SQL.Text);
        end;
        B.FStatus:= seSuccess;
      except
        on e: Exception do begin
          B.FStatus:= seFail;
          Result:= srSQLFail;
          if soAbortOnFail in FOptions then begin
            EM:= 'Error on Line '+IntToStr(B.Line)+': '+e.Message;
            raise ESQLExecScriptException.Create(EM, SE_ERR_EXECUTE, B);
          end;
        end;
      end;
      if Assigned(FOnBlockFinish) then
        FOnBlockFinish(Self, B);
    end; //of for loop
    //Commit transaction if configured
    if soUseTransactions in FOptions then
      FConnection.CommitTrans;
    //Everything succeeded
    Result:= srSuccess;
  except
    on e: Exception do begin
      Result:= srSQLFail;
      //Rollback transaction if configured
      if soUseTransactions in FOptions then
        if soAbortOnFail in FOptions then
          FConnection.RollbackTrans;
      raise e; //Re-raise exception
    end;
  end;
end;

procedure TSQLExec.Invalidate;
begin
  FParsed:= False;
  FBlocks.Clear;
end;

function TSQLExec.LineCount: Integer;
begin
  Result:= FSQL.Count;
end;

function TSQLExec.BlockCount: Integer;
begin
  if not FParsed then
    ParseSQL;
  Result:= FBlocks.Count;
end;

function TSQLExec.GetSQL: TStrings;
begin
  Result:= TStrings(FSQL);
end;

procedure TSQLExec.SetConnection(const Value: TADOConnection);
begin
  FConnection := Value;
end;

procedure TSQLExec.SetSplitWord(const Value: String);
begin
  FSplitWord := Value;
  Invalidate;
end;

procedure TSQLExec.SetSQL(const Value: TStrings);
begin
  FSQL.Assign(Value);
  Invalidate;
end;

procedure TSQLExec.SQLChanged(Sender: TObject);
begin
  Invalidate;
end;

end.

ZeosLib开源组件TZSQLProcessor，成熟（约9年的开发），重量轻，多平台（多个数据库引擎和连接库）库-解决了相同的问题。您可以将解决方案与其源代码进行比较，以找到缺失/不同的点

注意：致力于Google代码：code.google.com/p/sql-executer/source/browse

这完全取决于您的代码“拆分器”的工作方式。给您留下深刻印象的是您逐行解析。如果您有一个适当的解析器，可以按字符进行解析并识别开始/结束注释，则它将仅保持状态“我现在在注释中”而忽略所有内容。

@JanDoggen的确如此，但是恐怕完整的解析会大大降低它的速度。实际上，这似乎比Microsoft的OSQL工具快约5倍。我还更新了它以支持注释块：code.google.com/p/sql-executer/source/browse/SQLExec.pas

我现在在GitHub上进行了很多更改，还有可以用于生产的完整演示应用程序：github.com/djjd47130/sql-executer/tree/SQL-Exec-V2

#1 楼

不幸的是，我无法测试您的代码，但能够读取您的代码。很好。您的代码总体上写得很好，并且您似乎遵循了我所知道的大多数Delphi编码约定。（是的，非Delphi用户，使用F表示字段名称，使用T表示类型，并且在每个枚举常量之前添加几个字母是Delphi约定）。

已经有一段时间了，因为我使用了Delphi，但我相信我在这里要说的大多数内容对您都是有用的。

枚举常量命名

说到枚举约定约定，我相信约定是对所有常量使用类型和前缀的大写字母。

TSQLExecStatus和se变为TSQLExecResult，但我不明白sr，我会想到TSQLExecOption或so。

间距不一致

以nitpick开头，这有一些不一致之处：

TSQLExecStatus = (sePending, seExecuting, seSuccess, seFail);
TSQLExecResult = (srSuccess, srConnFail, srSQLFail);

TSQLExecOption = (soUseTransactions, soAbortOnFail, soForceParse);

我建议坚持使用TSQLExecStatus（曾经有一段时间我使用sec，但我已经完全放弃了）

我还将增加此行的间距：

ErrorCode := ErrCode;
FBlock:= ABlock;

至：

EM:= 'Error on Line '+IntToStr(B.Line)+': '+e.Message;

私人成员的私人成员

EM := 'Error on Line ' + IntToStr(B.Line) + ': ' + e.Message;

访问ss我不推荐。您已经创建了Variable := Value;方法，请改用它。

function TSQLExecBlock.GetIndex: Integer;
begin
  Result:= FOwner.FItems.IndexOf(Self);
end;

最大的混乱

代码中最大的混乱是

首先，变量名称...

Result := FOwner.IndexOf(Self);

我会将Variable:=Value;命名为FOwner.FItems.IndexOf，将TSQLExecBlocks.IndexOf命名为TSQLExec.ParseSQL（因为这是一个for循环变量），或者将TSQLExec.Execute命名为B，我将其完全删除，因为它似乎没有被使用。最后，我将CurrentBlock命名为X或将其删除，因为当引发错误时还可以直接指定消息时，它只是用于保存消息的临时变量。

我也发现一些评论只是…过大。特别是在这里：

var
  B: TSQLExecBlock;
  X: Integer;
  R: Integer;
  EM: String;

第一：

  if Pos('use ', LowerCase(Trim(S))) = 1 then begin
    //FSQL[X]:= '';       //Temporarily disabled
  end else
  if SameText(FSplitWord, Trim(S)) then begin
    B:= FBlocks.Add;    //Add a new block
    B.FLine:= X;        //Assign the starting line # of block
  end else begin
    B.SQL.Append(S);    //Add SQL script to current block
  end;

是的，它被暂时禁用，我可以看到，因为这条线是改色的。为什么禁用？如果重新启用它会发生什么？

其他评论：

//FSQL[X]:= '';       //Temporarily disabled

结束其他开始B.SQL。追加//将SQL脚本添加到当前块中

除了代码已经说明的内容之外，根本不提供任何其他信息。如果将变量名称更改为我上面建议的名称，它们将更加易于记录。

if-then-if

B:= FBlocks.Add;    //Add a new block
B.FLine:= X;        //Assign the starting line # of block

为什么不这样写？

if soUseTransactions in FOptions then
  if soAbortOnFail in FOptions then
    FConnection.RollbackTrans;

考虑方法提取

这部分内容代码：

if soUseTransactions in FOptions 
  and soAbortOnFail in FOptions then
    FConnection.RollbackTrans;

可以使用方法提取，以便该方法中的代码看起来更像：

for X := 0 to FBlocks.Count-1 do begin
  B:= FBlocks[X];
  B.FStatus:= seExecuting;
  if Assigned(FOnBlockStart) then
    FOnBlockStart(Self, B);
  try
    if Trim(B.SQL.Text) <> '' then begin
      FConnection.Execute(B.SQL.Text);
    end;
    B.FStatus:= seSuccess;
  except
    on e: Exception do begin
      B.FStatus:= seFail;
      Result:= srSQLFail;
      if soAbortOnFail in FOptions then begin
        EM:= 'Error on Line '+IntToStr(B.Line)+': '+e.Message;
        raise ESQLExecScriptException.Create(EM, SE_ERR_EXECUTE, B);
      end;
    end;
  end;
  if Assigned(FOnBlockFinish) then
    FOnBlockFinish(Self, B);
end; //of for loop

如您所见，它消除了对I注释的需要

摘要

编写良好的代码，其中大部分内容都在格式化问题和一些重构的东西:)

\ $ \ begingroup \ $
至于：=中的间距不一致，我不希望有空格，但是IDE会在代码完成时自动添加一个空格。
\ $ \ endgroup \ $
–杰里·道奇（Jerry Dodge）
2014-09-17 0:25

编程黑洞网

使用ADO使用GO语句执行大型SQL脚本文件

评论

#1 楼

评论