Using ADO to run sql script (with GO syntax) in inno setup

Question

I am using this code to run my sql scripts in inno setup. However ADO gives an error if the script contains GO syntax within it. I added my own code to overcome this problem.

My code below splits the script at each GO statement and run each section separately. I think this could be done in better ways.

[code]
const
 adCmdText = $00000001;
 adExecuteNoRecords = $00000080;

procedure RunScript();
var
 tString: TStringList;
 ADOCommand: Variant;
 ADORecordset: Variant;
 ADOConnection: Variant;  
 i, len: Integer;
 strToQuery, str: String;
begin
 try
 ADOConnection := CreateOleObject('ADODB.Connection'); 
 ADOConnection.ConnectionString := 
  'Provider=SQLOLEDB;' +               // provider
  'Data Source=(local)\SQL2000;' +   // server name
  'Initial Catalog=databasename;' +       // default database
  'User Id=userId;' +                // user name
  'Password=password;';                   // password
 ADOConnection.Open;
 try
  ADOCommand := CreateOleObject('ADODB.Command');
  ADOCommand.ActiveConnection := ADOConnection;
   tString:= TStringList.Create;
   tString.LoadFromFile('C:\sqlscript.sql');
   len:= tString.Count;
   strToQuery:='';
   for i:=0 to len-1 do begin
    str := trim(tString[i]);          
    if POS('go', lowercase(str)) = 1  then begin
     //If GO is found run the script added in to strToQuery since last GO
     ADOCommand.CommandText := strToQuery;                           
     ADOCommand.Execute(NULL, NULL, adCmdText or adExecuteNoRecords);
     strToQuery:='';        //Clear the string to start adding till next GO
     end else strToQuery:= strToQuery +#13#10+ str;  //Add the script to strToQuery
    end;
    ADOCommand.CommandText := strToQuery;                           
    ADOCommand.Execute(NULL, NULL, adCmdText or adExecuteNoRecords);
 finally
  ADOConnection.Close;
  tString.Free;
 end;
 except
  MsgBox(GetExceptionMessage, mbError, MB_OK);
 end;
end;

Any suggestion to update the code will be much appreciated.

Answer 1

1. Function block

Standalone action like loading a script from file and splitting it into SQL commands by the GO command, I would move into a separate function. That function might have the following prototype:

function LoadScriptFromFile(const FileName: string; out CommandList: TStrings): Integer;
begin

end;

Having such function will isolate parsing and execution parts of the script. You would first load the script from a file and then in the other part of the script iterate the parsed commands and execute each one.

2. Object finalization

Always try to encose object finalization into a separate try..finally block. In your code it applies to the tString object. This part:

ADOConnection.Open;
try
  tString := TStringList.Create;
  ...
finally
  ADOConnection.Close;
  tString.Free;
end;

Should become:

ADOConnection.Open;
try
  tString := TStringList.Create;
  try
    ...
  finally
    tString.Free;
  end;
finally
  ADOConnection.Close;
end;

3. Useless variable

The len variable is used for only one purpose and its value is used only once, so it's actually useless there. I would remove it and write directly:

for i := 0 to tString.Count - 1 do
begin
  ...
end;

4. Summary

Except the above (not so major) things I would say your code is fine. The string concatenation with line breaks you have used should be fine for ADO connected to the SQL Server, I think.

For the sake of completness, here is what I'd write (untested!). Please note, that every SQL command is expected to be "terminated" by the GO command in the input file (that requires also your original code):

function LoadScriptFromFile(const FileName: string; out CommandList: TStrings): Integer;
var
  I: Integer;
  SQLCommand: string;
  ScriptFile: TStringList;
begin
  Result := 0;
  ScriptFile := TStringList.Create;
  try
    SQLCommand := '';
    ScriptFile.LoadFromFile(FileName);

    for I := 0 to ScriptFile.Count - 1 do
    begin
      if Pos('go', LowerCase(Trim(ScriptFile[I]))) = 1 then
      begin
        Result := Result + 1;
        CommandList.Add(SQLCommand);
        SQLCommand := '';
      end
      else
        SQLCommand := SQLCommand + ScriptFile[I] + #13#10;
    end;

    //EDIT: If there is no GO syntax present int the file AND
    //To add the script after the last GO - Govs
    CommandList.Add(SQLCommand);
    Result:= Result + 1;
  finally
    ScriptFile.Free;
  end;
end;

procedure RunScript(const FileName: string);
var
  I: Integer;
  CommandList: TStrings;
  ...
begin
  ...
  // here is already established connection
  CommandList := TStringList.Create;
  try
    if LoadScriptFromFile(FileName, CommandList) > 0 then
      for I := 0 to CommandList.Count - 1 do
      begin
        // execute each command
        ADOCommand.CommandText := CommandList[I];
        ADOCommand.Execute(NULL, NULL, adCmdText or adExecuteNoRecords);
      end;
  finally
    CommandList.Free;
  end;
  ...
end;