Hello,

There have been several threads about VerifyTable, some describing how to write a stored procedure in order to get the Result (success / failure) of the function.

However, when wrapped into a stored procedure, the OnProgress event will only fire at 0% and 100%, so we lose the ability to have a meaningful progress bar.

According to the docs, there is a list of commands which "generate progress", and TEDBStoredProc itself accepts an "OnProgress" event handler.

Any idea how to get the best of both worlds?
Thanks

Fady


Looking at my code I displayed a simple "I'm alive and doing something" indicator. so I don't know the answer, however I do have a suggestion. Unfortunately it means some digging. Running VERIFY TABLE in EDBManager does show a progress bar that is incremented maybe the code in there can illuminate a method of achieving what you want.


Roy Lambert

Hello Roy,

I was not aware we had access to the sources of edbmgr.
I checked in C:\ProgramFiles (x86)\ElevateSoft, the default location where the EDB-ADD installs, and while there are a few source files, they seem mainly for the converter.

Maybe I'm missing something trivial?

Supposedly, I have the CS + source-code version of EDB, v2.38 b2

thank you for your time

Fady


In my case the source is in C:\D2007Ex\EDBUtils\utils\edbmgr\win32\source


Roy Lambert

Roy, this was a very constructive exercise, here's what Tim does in edbmgr:

the query is run with a plain TEDBQuery, linked to a progress event, status and log events.
those events update the GUI.

the trick is: at no point does edbmgr return a True / False value upon repair: the user will infer this by looking at the query's log messages.

One could consider extracting a success / failure flag by analyzing the log messages:
we know there are two messages, one on start and another on finish: more than that and it means the "verify" procedure had something else to say => failure.

What do you think?

Fady


I have a form for verifying / repairing / optimising tables - I've listed my code below. I'd offer to send you my unit but its D2007 with some of my homebrew components.


procedure TRandOTablesForm.DoItClick(Sender: TObject);
var
Cntr: integer;
DoFilter: boolean;
LogFile: string;
begin
slAll.Clear;
DoFilter := False;
if Mode.Checked[0] then iMode := 'VQ'  {validate quick ie structure only}
else if Mode.Checked[1] then iMode := 'VF' {full validate}
else if Mode.Checked[2] then iMode := 'REPAIR'
else if Mode.Checked[3] then iMode := 'OPTIMIZE';
DoIt.Visible := False;
Update;
Progress.Visible := True;
for Cntr := 1 to 4 do TableList.DataStores[Cntr].Data.Clear;
TableList.Reset;
TableList.First;
while not TableList.Eof do begin
 slTable.Clear;
 Progress.BlankBar;
 if Tables.SelectedRows.CurrentRowSelected then begin
  UpdateDateTime(TableList_Started);
  Tables.SelectedRows.CurrentRowSelected := False;
  UpdateStatus('Processing');
  if iMode[1] = 'V' then begin
   Panel1.Visible := True;
   nlhWaiter1.Waiting := True;
   UpdateStatus('Checking');
   ValidateTable.Close;
   if not ValidateTable.Prepared then ValidateTable.Prepare;
   ValidateTable.ParamByName('TableName').AsString := TableList_Table.AsString;
   ValidateTable.ParamByName('StructureOnly').AsBoolean := iMode = 'VQ';
   ValidateTable.ExecScript;
   if not ValidateTable.ParamByName('OK').AsBoolean then UpdateStatus('ok') else begin
    UpdateStatus('NEEDS REPAIR');
    Tables.SelectedRows.CurrentRowSelected := True;
    DoFilter := True;
    slAll.Add(slTable.Text);
   end;
   ValidateTable.Close;
   nlhWaiter1.Waiting := False;
  end else if iMode[1] = 'R' then begin
   DeleteFile(TableList_Table.AsString + dm.TfrSession.LocalTableIndexExtension);
   Actioneer.Close;
   Actioneer.SQL.Text := 'REPAIR TABLE ' + TableList_Table.AsString;
   Progress.Initialise(0, 100, TableList_Table.AsString);
   Actioneer.ExecSQL;
   UpdateStatus('Repaired');
  end else if iMode[1] = 'O' then begin
   Actioneer.Close;
   Actioneer.SQL.Text := 'OPTIMIZE TABLE ' + TableList_Table.AsString;
   Progress.Initialise(0, 100, TableList_Table.AsString);
   Actioneer.ExecSQL;
   UpdateStatus('Optimised');
  end;
  UpdateDateTime(TableList_Finished);
 end else UpdateStatus('Not processed');
 TableList.Next;
end;
Actioneer.Close;
DoDBCleanup;
slTable.Clear;
if DoFilter then begin
 TableList.Filter := '_Status <> ''ok''';
 TableList.Filtered := True;
 TableList.First;
 Update;
 Cntr := 0;
 LogFile := 'Validation';
 while FileExists(TemporaryFile(LogFile + IntToStr(Cntr) + '.log')) do inc(Cntr);
 LogFile := TemporaryFile(LogFile + IntToStr(Cntr) + '.log');
 emfOpener.AddFileToRelease('Validation', LogFile);
 slAll.SaveToFile(LogFile);
 ShellExecute(Application.Handle, nil, PChar(LogFile), nil, nil, SW_SHOW);
end;
slAll.Clear;
DoIt.Visible := True;
Progress.Visible := False;
nlhWaiter1.Waiting := False;
Panel1.Visible := False;
end;


and the ValidateTable script is

SCRIPT(IN TableName VARCHAR COLLATE ANSI_CI, IN StructureOnly BOOLEAN, OUT OK BOOLEAN)
BEGIN
DECLARE Stmt STATEMENT;
PREPARE Stmt FROM 'VERIFY TABLE '+QUOTEDSTR(TableName,'"') + IF(StructureOnly,' STRUCTURE ONLY','');
EXECUTE Stmt;
SET OK =  STMTRESULT(Stmt);
END




Roy Lambert