Link to home
Start Free TrialLog in
Avatar of tirbanqs
tirbanqsFlag for Philippines

asked on

SQL Query Log

Hi,

I am making a Delphi program that will execute SQL script on a multiple DB.
I have a checklist container for the DB which the user will select.
When the SQL script is executed, how can I log each SQL statement executed?
The log is placed at the bottom (Memo container) of the form.

example:
use DB1 -> logs the database DB1 to Memo
alter table CUSTOMER
add LASTNAME varchar(20) NULL
GO -> logs the statement in Memo (either successful or has errors)

use DB2 -> logs the database DB2 to Memo
alter table USER
add USERNAME varchar(20) NULL
GO -> logs the statement in Memo (either successful or has errors)

This program is like the MSSQL query analyzer where the log is at the bottom...

Thanks
Avatar of mildurait
mildurait
Flag of Australia image

I'm no expert in dephi, but you could write a wrapper sub-routine that takes the sql (string/text) as a parameter and writes the sql parameter to a log table in sql server after it executes the sql, together with success/failure, username, ipaddress & timestamp etc.
simply add sql statement to your memo

memo1.lines.Add(query1.sql.text);
Avatar of tirbanqs

ASKER

mildurait:
Can you give me an example? I quietly don't understand your suggestion. In delphi would be great. thanks

bokist:
Thanks for the reply but that's not what I want.
I don't know Delphi "At All", so someone with Delphi skills will need to fill in the gaps, and my advice can only be "broad".  However, in vb we would set up a recordset object and then add a new record to the table and populate each field accordingly and run the update method of the recordset object.  I don't know if that even computes in Delphi.

On the sql side of things, suggest creating a table called tblLog with PK iLogID int, identity, sLogSQL, varchar/text depending on the expected size of your sql statements, sLogIP, sLogComputer,dLogDate (Default=GetDate()),sLogUserName, bLogSuccess bit (and any others you want to store).




what you want exactly : to store sql statement into memo or to save statemenst somewhere(table in DB) ???  
Avatar of Geert G
i suspect you have big script ?
and you want each piece of the script logged ?

i used to have this create db script.
after each statement block, you need to put "GO" on a single line

aha, found it, it looks really ancient too  :)
the thing was, this loaded certain scripts and ran them in a thread use AdoConnection
without further ado, here it is

if you need to ellaborate further, just comment
procedure TThrUpdateDatabase.Execute;
const
   MaxScripts = 5;
   Scripts: Array[1..MaxScripts] of string = ('update.sql', 'constraints.sql', 'procedures.sql', 'views.sql', 'triggers.sql');
var
	oConnection : TADOConnection;
	RecordsAffected: Integer;
	Script, Script2, AllScripts: TStringlist;
	Ok, DoScript: Boolean;
	I, N: Integer;
  function Prog(Index: Integer): Integer;
  begin
    Result := Index + 1;
    if Result > MaxScripts then
      Result := MaxScripts + 1;
  end;
begin
  CoInitialize(Nil);
  try
    oConnection := TADOConnection.Create(Nil);
    Ok := True;
    try
      if not Terminated and Ok then
      begin
        AllScripts := TStringlist.Create;
        try
          for I := 1 to MaxScripts do
            AllScripts.Add(Scripts[I]);
          if FSpecialScripts <> Nil then
            AllScripts.AddStrings(FSpecialScripts);
          oConnection.ConnectionString := 'Provider=SQLOLEDB;Data source='+Server+';' + 'Initial catalog='+DBName;
          oConnection.LoginPrompt := False;
          oConnection.Open(CIMSQLAdmin, CIMSQLPassword);
          oConnection.CommandTimeout := 600;
          try
            Script := TStringlist.Create;
            Script2 := TStringlist.Create;
            try
              For N := 0 to AllScripts.Count-1 do
              begin
                Script.Clear;
                Script2.Clear;
                try
                  if not Terminated and Ok then
                  begin
                    DoScript := True;
                    if (AllScripts[N] = 'constraints.sql') and not DoConstraints then
                      DoScript := False;
                    if DoScript then
                    begin
                      Script.LoadFromFile(Dir + '\' + AllScripts[N]);
                      Script2.Clear;
                      PostMessage(Receiver, wm_SetProgress, Prog(N), Script.Count);
                      for I := 0 to Script.Count-1 do
                      begin
                        if not Terminated then
                        begin
                          if (UpperCase(Trim(Script[I])) = 'GO') or (I = Script.Count-1) then
                          begin
                            if Trim(Script2.Text) <> '' then
                            try
                              oConnection.Execute(Script2.Text, RecordsAffected, []);
                            except
                              on E: Exception do
                                if MessageDlg('Update error: op lijn ' + IntToStr(I) + #13 + E.Message+ #13+
                                  'Text: ' + Script2.Text,
                                  mtError, [mbIgnore, mbCancel], 0) = idCancel then Exit;
                            end;
                            Script2.Clear;
                          end else
                          begin
                            Script2.Add(Script[I]);
                            if I = Script.Count - 1 then
                            try
                              oConnection.Execute(Script2.Text, RecordsAffected, []);
                            except
                              on E: Exception do
                                if MessageDlg('Update error: op lijn ' + IntToStr(I) + #13 + E.Message+ #13+
                                'Text: ' + Script2.Text,
                                mtError, [mbIgnore, mbCancel], 0) = idCancel then Exit;
                            end;
                          end;
                          if I mod 50 = 0 then
                            PostMessage(Receiver, wm_AddProgress, Prog(N), 50);
                          if I = Script.Count-1 then
                            PostMessage(Receiver, wm_AddProgress, Prog(N), I mod 50);
                        end else Break;
                      end;
                      if Trim(Script2.Text) <> '' then
                      try
                        oConnection.Execute(Script2.Text, RecordsAffected, []);
                      except
                        on E: Exception do
                          if MessageDlg('Update error: op lijn ' + IntToStr(I) + #13 + E.Message+ #13+
                          'Text: ' + Script2.Text,
                          mtError, [mbIgnore, mbCancel], 0) = idCancel then Exit;
                      end;
                    end else PostMessage(Receiver, wm_DisableProgress, Prog(N), 0);
                  end;
                except
                  {on E: Exception do
                  begin
                    PostMessage(Receiver, wm_DBCreateError, N, 0);
                    Ok := False;
                    MessageDlg(E.Message, mtWarning, [mbOk], 0);
                  end;}
                end;
              end;
            finally
              Script.Free;
              Script2.Free;
            end;
          except
            {on E: Exception do
            begin
            PostMessage(Receiver, wm_DBCreateError, 0, 0);
            MessageDlg(E.Message, mtWarning, [mbOk], 0);
            end;}
          end;
        finally
          AllScripts.Free;
        end;
      end;
    finally
      oConnection.Free;
    end;
    if Terminated then
      PostMessage(Receiver, wm_DBCreateError, 6, 0);
  finally
    CoUninitialize;
  end;
end;

Open in new window

you would add a routine to send the script to a memo (or a message) like below
after line 62:

AddMemo(Script2.Text);
AddMemo(Format('Lines Affected %d', [RecordsAffected]);


i see i'll have to simplify this proc a little ...
type
  TYourThread = class(TThread)
  private
    fCallingForm: TForm;
    fMemoText: string;
    fIsError: boolean;
  end;
 
procedure TYourThread.UpdMemo;
begin
  if fIsEror then 
    fCallingForm.Memo1.Lines.Add('Error !!!');
  fCallingForm.Memo1.Lines.Add(fMemoText);
end;
 
procedure TYourThread.AddMemo(aMemoText: string; aIsError: boolean = False);
begin
  fMemoText := aMemoText;
  fIsError := aIsError;
  Synchronize(UpdMemo);
end;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you could also keep it a lot simpler ...
start the SQL Profiler, tell it to put the statements in a log table,
that's it !

this profiler comes with the MSSQL Server
Thanks Geert_Gruwez. This is what I was looking. It's perfect...