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

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;
i, len: Integer;
strToQuery, str: String;
begin
try
'Provider=SQLOLEDB;' +               // provider
'Data Source=(local)\SQL2000;' +   // server name
'Initial Catalog=databasename;' +       // default database
'User Id=userId;' +                // user name
try
tString:= TStringList.Create;
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
strToQuery:='';        //Clear the string to start adding till next GO
end else strToQuery:= strToQuery +#13#10+ str;  //Add the script to strToQuery
end;
finally
tString.Free;
end;
except
MsgBox(GetExceptionMessage, mbError, MB_OK);
end;
end;


Any suggestion to update the code will be much appreciated.

• what language is this?
– Malachi
Nov 22, 2013 at 17:28
• @Malachi, it's Pascal Script from Inno Setup. Nov 22, 2013 at 17:29
• I wish there was a better tag that we could put on this question. @TLama, should it have an SQL tag?
– Malachi
Nov 22, 2013 at 17:33
• @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. Nov 22, 2013 at 17:37
• @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
– Malachi
Nov 22, 2013 at 17:39

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
tString.Free;
end;


Should become:

ADOConnection.Open;
try
tString := TStringList.Create;
try
...
finally
tString.Free;
end;
finally
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 := '';

for I := 0 to ScriptFile.Count - 1 do
begin
if Pos('go', LowerCase(Trim(ScriptFile[I]))) = 1 then
begin
Result := Result + 1;
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
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
end;
finally
CommandList.Free;
end;
...
end;

• I guess Inc() function is not supported in Inno.
– Govs
Nov 25, 2013 at 9:49
• Sorry for that. I didn't even tried to compile my version (hence my note "untested"). I just wanted to show you my thoughts ;-) Nov 25, 2013 at 10:00
• Yes I understand. Just for reference if any body want to use the code. :)
– Govs
Nov 25, 2013 at 10:22
• 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.
– Govs
Nov 25, 2013 at 10:30
• @TLama Your input would be appreciated on this meta question regarding the Pascal/Pascal-Script/Delphi tags we have here on Code Review. Jun 10, 2014 at 17:27