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
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
DelphiMicrosoft SQL Server
Last Comment
tirbanqs
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
memo1.lines.Add(query1.sql
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.
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()),sLogUs
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()),sLogUs
what you want exactly : to store sql statement into memo or to save statemenst somewhere(table in DB) ???
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
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;
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 ...
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;
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
start the SQL Profiler, tell it to put the statements in a log table,
that's it !
this profiler comes with the MSSQL Server
ASKER
Thanks Geert_Gruwez. This is what I was looking. It's perfect...