How to log ADO connection's execute statements

Question

I am using ado connection to connect to sql 2008 from inno, and i would like to know if we can log the details into a file so that can capture the errors thrown by sql.

Note: Thru ado connection I am not just executing select querys, i am using ado connection to execute set of statements to create database, procedure, tables etc.

Answer 1

For logging a database provider specific errors, use the Errors collection of the ADO Connection object. How to log these errors to a file, shows the following pseudo-script:

procedure ConnectButtonClick(Sender: TObject);
var
  I: Integer;  
  ADOError: Variant;
  ADOConnection: Variant;  
  ErrorLog: TStringList;
begin
  ErrorLog := TStringList.Create;
  try    
    try
      ADOConnection := CreateOleObject('ADODB.Connection');
      // open the connection and work with your ADO objects using this
      // connection object; the following "except" block is the common
      // error handler for all those ADO objects
    except
      // InnoSetup scripting doesn't support access to the "Exception" 
      // object class, so now you need to distinguish, what caused the
      // error (if ADO or something else); for this is here checked if
      // the ADO connection object is created and if so, if its Errors
      // collection is empty; if it's not, or the Errors collection is
      // empty, then the exception was caused by something else than a
      // database provider
      if VarIsEmpty(ADOConnection) or (ADOConnection.Errors.Count = 0) then
        MsgBox(GetExceptionMessage, mbCriticalError, MB_OK)
      else
        // the Errors collection of the ADO connection object contains
        // at least one Error object, but there might be more of them,
        // so iterate the collection and for every single Error object
        // add the line to the logging string list
        for I := 0 to ADOConnection.Errors.Count - 1 do
        begin
          ADOError := ADOConnection.Errors.Item(I);
          ErrorLog.Add(
            'Error no.: ' + IntToStr(ADOError.Number) + '; ' +
            'Source: ' + ADOError.Source + '; ' +
            'Description: ' + ADOError.Description          
          );
        end;      
    end;
  finally
    ErrorLog.SaveToFile('c:\LogFile.txt');
    ErrorLog.Free;
  end;
end;