Hello,
  Is there an easy way to repair all the tables from all databases
within the catalog ?

Thank you

Charalampos

You need a script with a nested "for" statement, and a variable for the DBName & TableName, plus a bit of WHILE:

I can't promise the following is perfect, as I have just cut & pasted from a different script ... but it gives you something close to  workable:


SCRIPT
BEGIN
 DECLARE DBCrsr CURSOR FOR DBStmt;
 DECLARE TBLCrsr CURSOR FOR TBLStmt;
 DECLARE DBName VARCHAR(60);
 DECLARE TBLName VARCHAR(40);    

PREPARE DBStmt FROM
' SELECT
Name
FROM Configuration."Databases"
';

OPEN DBCrsr;
FETCH FIRST FROM DBCrsr ('Name') INTO DBName;
WHILE NOT EOF(DBCrsr) DO
  BEGIN
     PREPARE TBLStmt FROM
     ' Using "'+DBName+'"'
    +' SELECT Name FROM Configuration."Tables" ';
     OPEN TBLCrsr;
     FETCH FIRST FROM Crsr ('Name') INTO TBLName;
     WHILE NOT EOF(TBLCrsr) DO
        IF NOT (TBLName IS NULL) OR NOT (TBLName='') THEN
          EXECUTE IMMEDIATE
             'REPAIR TABLE "'+TBLName+
             '" ;
        FETCH NEXT FROM Crsr ('Name') INTO TBLName;
        END IF;
     END WHILE;
  END;      
  FETCH NEXT FROM DBCrsr ('Name') INTO DBName;
  END WHILE;                                  

END

Adam

Unless something has changed recently I don't think you can "use" databases with variables (ie you need to know the
names of the databases you are going to use.

Modifying your script.a bit I think something like the following should work

SCRIPT
BEGIN
 DECLARE TBLCrsr CURSOR FOR TBLStmt;
 DECLARE TBLName VARCHAR(40);

 USE DATABASE1;
 PREPARE TBLStmt FROM ' SELECT Name FROM Information.Tables ';
 OPEN TBLCrsr;
 FETCH FIRST FROM TBLCrsr ('Name') INTO TBLName;
 WHILE NOT EOF(TBLCrsr) DO
     EXECUTE IMMEDIATE 'REPAIR TABLE '+TBLName;
     FETCH NEXT FROM TBLCrsr ('Name') INTO TBLName;
  END WHILE;

 USE DATABASE2;
 PREPARE TBLStmt FROM ' SELECT Name FROM Information.Tables ';
 OPEN TBLCrsr;
 FETCH FIRST FROM TBLCrsr ('Name') INTO TBLName;
 WHILE NOT EOF(TBLCrsr) DO
     EXECUTE IMMEDIATE 'REPAIR TABLE '+TBLName;
     FETCH NEXT FROM TBLCrsr ('Name') INTO TBLName;
  END WHILE;

END

John

Sorry John you are quite right, it is "USE [Database]" rather than "USING" and it needs to be a separate segment of the script rather than within the SQL statement. And it is not possible to use a variable in the script in this way:

USE DBName;

It results in the error

"Error #401 The database DBName does not exist"

i.e. the compiler can't read DBName as a variable at that point.

... so unless someone else shows us a way, I don't think it is possible to iterate all the databases in a session within a single script.

Charalampos


I know you posted within the sql group, but I don't understand why people (apart from John Hay are SO fixated on an sql only solution. The easy solution is to use Delphi (or programming language of your choice, to write the appropriate sql statements and execute them one at a time or write a script full of EXECUTE IMMEDIATES and then execute that.

I have a form which is called with a few different parameters (OK its just for one database but it would be easy to get the rest) to VERIFY / REPAIR / OPTIMISE the tables. It allows the user to tick which of the tables are to be acted on and provides visual feedback.

I've pasted a coup[le of the procedures below.


Roy Lambert


procedure TRandOTablesForm.PrepList;
var
Cntr: integer;
begin
DBInfo.ExecSQL; <<<<<<< SELECT Name FROM Information.Tables
Tables.ClearRows(1, Tables.RowCount - 1);
Tables.RowCount := DBInfo.RecordCount + 1;
Cntr := 1;
while not DBInfo.Eof do begin
 Tables.Cells[0, Cntr] := DBInfo.FieldByName(F_Name).AsString;
 Tables.AddCheckBox(1, Cntr, True, False);
 Tables.Cells[2, Cntr] := 'waiting';
 DBInfo.Next;
 inc(Cntr);
end;
DBInfo.Close;
end;

procedure TRandOTablesForm.DoItClick(Sender: TObject);
var
Cntr: integer;
state: boolean;
Reindex: boolean;
begin
Reindex := (UpperCase(Tables.Cells[1, 0])[1] = 'R') and (nlhMessageDlg('Do you want to regenerate the indices as well?', mtWarning, [mbYes, mbNo], 0) = mrYes);
DoIt.Visible := False;
SetAll.Visible := False;
SetNone.Left := Tables.Left;
SetNone.Width := Tables.Width;
SetNone.Caption := 'Stop after completing the current table';
Update;
dm.TfREngine.Close;
NotInProgress := False;
Progress.Visible := True;
Started := '';
Ended := '';
for Cntr := 1 to Tables.RowCount - 1 do begin
 Application.ProcessMessages;
 if NotInProgress then Break;
 Tables.Row := Cntr;
 if Tables.GetCheckBoxState(1, Cntr, state) and state then begin
  Tables.Cells[2, Cntr] := 'Processing';
  Tables.RepaintRow(Cntr);
  if Reindex then DeleteFile(Tables.Cells[0, Cntr] + dm.TfrSession.LocalTableIndexExtension);
  Actioneer.Close;
  Actioneer.SQL.Text := UpperCase(Tables.Cells[1, 0]) + ' TABLE ' + Tables.Cells[0, Cntr];
  Actioneer.ExecSQL;
  Tables.Cells[2, Cntr] := 'Done';
  Tables.RepaintRow(Cntr);
 end else Tables.Cells[2, Cntr] := 'Not processed';
 Tables.ScrollInView(0, Cntr);
end;
Actioneer.Close;
DoDBCleanup;
SetNone.Visible := False;
try
 nlhMessageDlg('Process completed in ' + TimeToStr(StrToTime(Ended) - StrToTime(Started)), mtInformation, [mbOK], 0);
except
 nlhMessageDlg('Process completed', mtInformation, [mbOK], 0);
end;
Close;
end;

Adam,

<< ... so unless someone else shows us a way, I don't think it is possible
to iterate all the databases in a session within a single script. >>

That is correct.  The issue is that the USE statement introduces a new
binding context for functions/procedures, so it can't be unknown at compile
time, hence the reason for not allowing variables/expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim

Its great to take on Roy's point that "you can do it all in Delphi anyway" ... which is true, but once you have a server set up and some silly bureaucrats with "security policies" about what is allowed to run on it, building a solution which is an EXE & installing & running it can be a pain, whereas you can almost always run a bit of SQL against the DB if you ask politely.

What we really need is a "casting" keyword a la TIMESTAMP:

i.e.

WHERE CreatedOn > TIMESTAMP '2012-01-01 00:00';

would be:

USE DATABASE 'SomeDBName';

--

I know this may be hard to achieve for the technical reasons you mention, but it would be brilliant!

Adam

>Its great to take on Roy's point that "you can do it all in Delphi anyway" ... which is true, but once you have a server set up and some silly bureaucrats with "security policies" about what is allowed to run on it, building a solution which is an EXE & installing & running it can be a pain, whereas you can almost always run a bit of SQL against the DB if you ask politely.

You missed my point, and are misquoting me as well! - naughty boy

Delphi happens, for me anyway, to be the easy solution. You could equally well (I think) use Tim's built in language to query the configuration and catalogs to write a script, save that as a procedure and then have the user run that procedure. Unless its changed I don't think you can create a procedure in another one and then call it.

You could also write an external function and install as part of the server and then just call the function.

Finally something like repairing all databases and tables unless dome by a sysadmin who's used to staring blankly at a screen whilst some long job chunters away unseen yu need a visual element.

For many many years, including my pre-Delphi ones I have always built in some sort of management routines into apps. With Delphi and ElevateDB (and DBISAM before it) its trivial to do so and I heartily recommend it to all and sundry.

Roy Lambert

Adam,

<< I know this may be hard to achieve for the technical reasons you mention,
but it would be brilliant! >>

Actually, it's impossible to achieve unless I stop having the compiler bind
functions and procedures at compile time, which is pretty much a non-starter
since it would get rid of being able to call such functions in basic
expressions in the SQL/PSM:

BEGIN
  IF MyFunction() THEN
      -- Do something
  END IF;
END

The compiler has to know which database is in effect when it compiles the
MyFunction() reference so that it knows a) whether the function exists in
the current database and b) do the parameters match, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

John, Can you be so kind as to point out what I am doing wrong ? Thanks in advance.

                 try
                   EDBEngine.Active:= False;
                   EDBEngine.ExclusiveFileAccess:= True;
                   EDBDatabase.Database:= 'Configuration';
                   EDBEngine.Active:= True;
                   if EDBQuery1.Active then EDBQuery1.Close;
                   EDBQuery1.Params.Clear;
                   EDBQuery1.SQL.Clear;
                   EDBQuery1.SQL.Add('SCRIPT '+
                                     'BEGIN '+
                                     '  DECLARE TBLCrsr CURSOR FOR TBLStmt; '+
                                     '  DECLARE TBLName VARCHAR(40); '+
                                     '  USE ABC; '+
                                     '  PREPARE TBLStmt FROM '+Chr(39)+'SELECT Name FROM Information.Tables;'+Chr(39)+' '+
                                     '  OPEN TBLCrsr; '+
                                     '  FETCH FIRST FROM TBLCrsr '+Chr(39)+'Name'+Chr(39)+' INTO TBLName; '+
                                     '  WHILE NOT EOF(TBLCrsr) DO '+
                                     '    EXECUTE IMMEDIATE '+Chr(39)+'REPAIR TABLE TBLName;'+Chr(39)+' '+
                                     '    EXECUTE IMMEDIATE '+Chr(39)+'OPTIMIZE TABLE TBLName;'+Chr(39)+' '+
                                     '    FETCH NEXT FROM TBLCrsr '+Chr(39)+'Name'+Chr(39)+' INTO TBLName; '+
                                     '  END WHILE; '+
                                     'END;');
                   EDBQuery1.ExecSQL;
                   screen.cursor:= crDefault;
                   MiscObject.MyMessageDlg(MiscObject.TxT('REINDEXED, Restart Required !'), mtInformation, [mbOK], 0, fMain, mrOK, []);
                 except
                   screen.cursor:= crDefault;
                   MiscObject.MyMessageDlg(MiscObject.TxT('ERROR, Restart Required !'), mtInformation, [mbOK], 0, fMain, mrOK, []);
                 end;