inno setup access violation using ADO

Question

I'm working on a inno setup installer in which I need to execute multiple SQL-Management Studio scripts with the 'GO' Statement. My approach is to open the DB connection, loop through the files, splitting them into the GO-Blocks and execute each block. The first file runs through as expected. On the second file, when it comes to execution I get an access violation in method "ExecSQLMulti" at the line:

ADOCommand.ActiveConnection := DBConnection;

I think the issue is that the DBConnection object is still referenced by the previous ADOCommand object. But when I try this:

ADOCommand.ActiveConnection := null();

I get the error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another...

"Main" Method, iterates through the sql files:

function NKInstall_CreateDatabase: boolean;
var
  SQLScripts: TArrayOfString;
  ScriptTransactions: TStringList;
  i,ItemCount: integer;
  myDBConnection: Variant;
begin
  if not _PreconditionsOK_CreateDB() then begin
    Result := True;
    exit;
  end;

  if not ConnectToDatabase(SQLServerInstance.Text,myDBConnection) then exit;
  SQLScripts := CollectSQLScripts();
  ItemCount := GetArrayLength(SQLScripts)-1;
  ScriptTransactions := TStringList.Create();

  for I := 0 to ItemCount do begin
    SetProgressText('Datenbank wird erstellt...','Script: '+SQLScripts[i]);

    if LoadSQLScript(ExpandConstant('{app}\NKHLP\DB\'+SQLScripts[i]), ScriptTransactions) then begin
      if not ExecSQLMulti(myDBConnection,ScriptTransactions) then begin
        InstallSummary.Lines.Add('- DB Erstellung: Script '+SQLScripts[i]+' konnte nicht (komplett) ausgeführt werden.');
        //Result := False;   //*** Für Testphase des installers auskommentiert, damit Installation nicht abbricht.
        Result := True;
        gDBCreated := False;
        exit;
      end;
      ScriptTransactions.Clear();
    end
    else begin
      MsgBox('SQL Script '+ExpandConstant('{app}\NKHLP\DB\'+SQLScripts[i])+' konnte nicht eingelesen werden!',mbError,MB_OK);
      InstallSummary.Lines.Add('- DB Erstellung: Script '+SQLScripts[i]+' konnte nicht eingelesen werden.');
      //result := false;    //*** Für Testphase auskommentiert, damit Installation nicht abbricht.
      result := True;
      gDBCreated := False;
      exit;
    end;
    //Log('File done...'); //Debug
  end;
  gDBCreated := True;
  Result := True;
end;

Establishes connection to DB:

function ConnectToDatabase(DataSourceName: string; var Connection: Variant): boolean;
begin
log('Connecting...');
  try
    Connection := CreateOleObject('ADODB.Connection');
    Connection.CursorLocation := 3;
    Connection.ConnectionString := 'Provider=SQLOLEDB;' +                   // provider
                                   'Data Source=' + DataSourceName +';' +   // server name
                                   'Initial Catalog=master;' +              // default database
                                   'Integrated Security=SSPI;';             // Use Windows Authentication
    Connection.Open;
    Result := True;
  except
    Connection := Null();
    MsgBox('Die Verbindung zur Instanz "'+DataSourceName+'" konnte nicht hergestellt werden: '+#10#13+#10#13+GetExceptionMessage,mbError,MB_OK);
    Result := False;
  end
end;

Executes all the GO-Blocks:

function ExecSQLMulti(var DBConnection: Variant; Transactions: TStringList): boolean;
var
  ADOCommand: Variant;
  i: integer;
  CurrentQuery: string;
begin
  if Transactions.Count = 0 then begin
    Result := True;
    exit;
  end;
  try
    ADOCommand := CreateOleObject('ADODB.Command');
    ADOCommand.CommandType := adCmdText;
    ADOCommand.ActiveConnection := DBConnection;

    for i := 0 to Transactions.Count-1 do begin
      CurrentQuery := Transactions[i];
      if Length(CurrentQuery) > 0 then begin
        ADOCommand.CommandText := CurrentQuery;
        ADOCommand.Execute(NULL,NULL,adExecuteNoRecords);
      end;
    end;
    result := True;
  except
    MsgBox('Fehler beim ausführen eines SQL Befehls: '+GetExceptionMessage()+#10#13#10#13+
           'Query: '+CurrentQuery,
           mbError,
           MB_OK);
  end;
end;

Did you resolve this issue? I'm facing the same problem today. — k3flo
Actually I can't remember what the exact problem was. If I remember right the problem was, how I created and passed the connection object. I will post the refactored version. — Marco Rebsamen

Marco Rebsamen Marco Rebsamen · Accepted Answer · 2017-12-22T10:11:16

It seems like my approach of how I created and passed the connection object was wrong or just not working. I've refactored it to this:

function RunSQLScript(ScriptPath: String; SQLInstance: String; DBName: string; CommandTimeout: integer): boolean;
var
  ScriptTransactions: TStringList;
  ADOCommand: Variant;
  myDBConnection: Variant;
begin
  ScriptTransactions := TStringList.Create();

  if not ConnectToDatabase(SQLInstance, DBName, myDBConnection) then exit;

  ADOCommand := CreateOleObject('ADODB.Command');
  ADOCommand.ActiveConnection := myDBConnection;
  ADOCommand.CommandTimeout := CommandTimeout;
  ADOCommand.CommandType := adCmdText;

  if LoadSQLScript(ScriptPath, ScriptTransactions) then begin
    log('Executing Transactions...');
    if not ExecMultiTransaction(ADOCommand, ScriptTransactions) then begin
      MsgBox('SQL Script '+ScriptPath+' konnte nicht (komplett) ausgeführt werden!',mbError,MB_OK);
      Result := False;
    end
    else begin
      log('Sucessful');
      Result := True;
    end;
  end
  else begin
    MsgBox('SQL Script '+ScriptPath+' konnte nicht eingelesen werden!',mbError,MB_OK);
    result := False;
  end;
end;

function ExecMultiTransaction(var ADOCommand: Variant; Transactions: TStringList): boolean;
var
  i: integer;
  CurrentQuery: string;
begin
  if Transactions.Count = 0 then begin
    Result := True;
    exit;
  end;
  try
    for i := 0 to Transactions.Count-1 do begin
      CurrentQuery := Transactions[i];
      if Length(CurrentQuery) > 0 then begin
        ADOCommand.CommandText := CurrentQuery;
        ADOCommand.Execute(NULL,NULL,adExecuteNoRecords);
      end;
    end;
    result := True;
  except
    MsgBox('Fehler beim ausführen eines SQL Befehls: '+GetExceptionMessage()+#13#10#13#10+
           'Query: '+CurrentQuery,
           mbError,
           MB_OK);
  end;
end;

In the RunSQLScript function I now create the connection and pass only the ADOCommand object to the ExecMultiTransaction function where I change the ADOCommand.CommandText property and execute the command. This way I have one connection per script.

inno setup access violation using ADO

2 Answers