3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ what language is this? \$\endgroup\$ – Malachi Nov 22 '13 at 17:28
  • \$\begingroup\$ @Malachi, it's Pascal Script from Inno Setup. \$\endgroup\$ – TLama Nov 22 '13 at 17:29
  • \$\begingroup\$ I wish there was a better tag that we could put on this question. @TLama, should it have an SQL tag? \$\endgroup\$ – Malachi Nov 22 '13 at 17:33
  • 1
    \$\begingroup\$ @Malachi, I would keep it as it is. Although it's about parsing SQL script, it's related more to that Pascal Script than SQL language itself. \$\endgroup\$ – TLama Nov 22 '13 at 17:37
  • 1
    \$\begingroup\$ @TLama, I agree. I had to create that pascal-script tag. maybe it will take off and people will be using it a lot, and this question will get the attention it deserves \$\endgroup\$ – Malachi Nov 22 '13 at 17:39
5
\$\begingroup\$

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;
\$\endgroup\$
  • \$\begingroup\$ I guess Inc() function is not supported in Inno. \$\endgroup\$ – Govs Nov 25 '13 at 9:49
  • \$\begingroup\$ Sorry for that. I didn't even tried to compile my version (hence my note "untested"). I just wanted to show you my thoughts ;-) \$\endgroup\$ – TLama Nov 25 '13 at 10:00
  • 1
    \$\begingroup\$ Yes I understand. Just for reference if any body want to use the code. :) \$\endgroup\$ – Govs Nov 25 '13 at 10:22
  • \$\begingroup\$ Updated the function LoadScriptFromFile(). The script after last GO was not getting added. Also it will work if the script does not contain any GO syntax. \$\endgroup\$ – Govs Nov 25 '13 at 10:30
  • 1
    \$\begingroup\$ @TLama Your input would be appreciated on this meta question regarding the Pascal/Pascal-Script/Delphi tags we have here on Code Review. \$\endgroup\$ – Simon Forsberg Jun 10 '14 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.