I thought I'd done this before but no - I can't define a global event - a function called by and event yes but not the event itself. I'll go back to creating a subclassed table. I'm also going to look to see if I can do something about automating the creation of an ERROR trigger which might be a better solution.

Roy Lambert

Judd


This is a quick & dirty solution (the code is sound it the implementation that I'm not happy with).

Pop this procedure somewhere - for the tables where you want logging create an OnPostError event and call this function from in there.

I want to finish off something I started a few months ago then I'll get back to this.


procedure ErrorLogging(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
var
Qry: TEDBQuery;
function MakeQuery(iDatabase: TEDBDatabase): TEDBQuery;
begin
 Result := TEDBQuery.Create(nil);
 Result.SessionName := iDatabase.SessionName;
 Result.DatabaseName := iDatabase.DatabaseName;
 Result.RequestSensitive := True;
end;
begin
Qry := MakeQuery(TEDBTable(DataSet).Database);
if TEDBTable(DataSet).Database.Execute('SELECT * FROM Information.Tables WHERE Name = ''ErrorLog''') = 0 then begin
 Qry.SQL.Text := 'CREATE TABLE "ErrorLog"';
 Qry.SQL.Add('(');
 Qry.SQL.Add('"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,');
 Qry.SQL.Add('"_FileKey" VARCHAR(60) COLLATE "ANSI_CI",');
 Qry.SQL.Add('"_RecordKey" VARCHAR(60) COLLATE "ANSI_CI",');
 Qry.SQL.Add('"_TimeStamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,');
 Qry.SQL.Add('"_UserNum" INTEGER,');
 Qry.SQL.Add('"_Msg" CLOB COLLATE "ANSI_CI",');
 Qry.SQL.Add('CONSTRAINT "PK" PRIMARY KEY ("_ID")');
 Qry.SQL.Add(' )');
 Qry.ExecSQL;
 Qry.Close;
 Qry.SQL.Text := 'CREATE INDEX "Table" ON "ErrorLog" ("_FileKey" COLLATE "ANSI_CI")';
 Qry.ExecSQL;
 Qry.Close;
 Qry.SQL.Text := 'CREATE INDEX "Record" ON "ErrorLog" ("_RecordKey" COLLATE "ANSI_CI")';
 Qry.ExecSQL;
 Qry.Close;
 Qry.SQL.Text := 'CREATE INDEX "When" ON "ErrorLog" ("_TimeStamp")';
 Qry.ExecSQL;
 Qry.Close;
 Qry.SQL.Text := 'CREATE INDEX "Who" ON "ErrorLog" ("_UserNum")';
 Qry.ExecSQL;
 Qry.Close;
end;
Qry.SQL.Text := 'INSERT INTO ErrorLog';
Qry.SQL.Add('(_FileKey, _RecordKey, _UserNum, _Msg)');
Qry.SQL.Add('VALUES (:inFileKey, :inRecordKey, :inUserNum, :inMsg)');
Qry.Prepare;
Qry.ParamByName('inFileKey').AsString := TEDBTable(DataSet).TableName + ' - ' + TEDBTable(DataSet).Name;
Qry.ParamByName('inRecordKey').AsString := 'absolutely no idea';
Qry.ParamByName('inUserNum').AsInteger := 999; //JuddsUserNumValue;
Qry.ParamByName('inMsg').AsString := E.Message;
Qry.ExecSQL;
Qry.Close;
Qry.Free;
end;



Roy Lambert

Roy-Thanks so much for the code.  It was extremely instructive.
 
I've been so busy with getting my programs ready to be rolled out that I've been away from the forums for a good while. Wish I had replied to this earlier.
I worked out what I needed.  My errorlog is table based, so it's not using your code directly.  It seems to be working, though implemented only in specific areas for now.

The rollout was a couple of days ago, and was successful with a surprisingly small number of bugs that have surfaced so far.  There was one big deal which required restarting the EDBServer.  For the moment, indications are that it was caused by another computer on the network which has nothing to do with EDB.

Now it's time to be posting for the issues that have arisen.  Thanks again.
Judd