In my innocence, I would expect the following to work:

SCRIPT
BEGIN
 DECLARE Crsr Cursor FOR Stmt;
 DECLARE ExecCrsr Cursor FOR ExecStmt;
 DECLARE TableName VARCHAR(120);

PREPARE Stmt FROM
' SELECT Name FROM Information.Tables ';
OPEN Crsr;
FETCH FIRST FROM Crsr('Name') INTO TableName;
PREPARE ExecStmt FROM
' REPAIR TABLE ? STATISTICS  ';
WHILE NOT EOF(Crsr) DO
  OPEN ExecCrsr USING (TableName);
  CLOSE ExecCrsr;
  FETCH NEXT FROM Crsr('Name') INTO TableName;
  END WHILE;
CLOSE Crsr;


--

But it doesn't. I can't prepare the statement with a param "?" in place of the TableName, EDB tells me it expects a tablename not a param.

An alternative syntax:

WHILE NOT EOF(Crsr) DO
  EXECUTE IMMEDIATE
  ' REPAIR TABLE ' + TableName + ' STATISTICS ' ;
  FETCH NEXT FROM Crsr('Name') INTO TableName;


Works fine, but I understand that statements are quicker if they are prepared & there are other instances where it is useful to parameterize the tablename ... so I wonder whether it is possible somehow?

As an aside, it would also be useful to have SQL:

REPAIR DATABASE [Tables]

which repaired all the tables in a database.

Adam

I'm pretty sure you can't use parameters to substitute for any of the system objects


>Works fine, but I understand that statements are quicker if they are prepared & there are other instances where it is useful to parameterize the tablename ... so I wonder whether it is possible somehow?

Well yes but in this case I doubt you'll see any difference. Doing a lot of activity like updating a table or lots of selects one after another you can save a lot of time, but those activities are short themselves. REPAIR takes a bit of time.

Preparing a query basically acquires the table handle and checks that column names are valid etc and that can take a significant percentage of the time taken to run a query like "SELECT * FROM Companies WHERE _ID = :companyID'


Roy Lambert [Team Elevate]

Adam,

<< Works fine, but I understand that statements are quicker if they are
prepared & there are other instances where it is useful to parameterize the
tablename >>

This is only the case when you're executing thousands of DML statements or
more, ala INSERT.  DDL statements have to open tables in a special manner
and do special processing anyways, and would not be able to benefit from
preparation at all.  Preparation isn't really about saving time from
parsing/compilation of the statements (although there are a small amount of
such savings), rather it's about saving the time it takes to open/close the
tables involved in the statement.

Tim Young
Elevate Software
www.elevatesoft.com