Dan,
>I tried using prepared statements to make a procedure that 
>converts every table in a schema from ARCHIVE to MyISAM...
"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yet supported."
(http://dev.mysql.com/doc/refman/5.0/en/sqlps.html)

PB

-----

Burke, Dan wrote:
I tried using prepared statements to make a procedure that converts
every table in a schema from ARCHIVE to MyISAM (there's about three
dozen archive tables here).  But for some odd reason it will give an
error after doing the first table, and abort.  The really odd thing is
that the table it gives the error on does successfully get converted.

Any thoughts?  Is there a better way to do this?  Or am I just doing
something stupid?

This is the error directly below... and the full procedure below that.

 
ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
'./CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)



drop procedure if exists CONVERT_TABLES;
delimiter //
create procedure CONVERT_TABLES()
begin
        DECLARE t char(255);
        DECLARE done INT DEFAULT 0;
        DECLARE cur1 CURSOR for SELECT TABLE_NAME from
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
= 'MyISAM';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

        OPEN cur1;

        REPEAT
                FETCH cur1 INTO t;
                IF NOT done THEN
                        set @x = CONCAT('alter table ', t, ' engine =
myisam');
                        prepare z from @x;
                        execute z;
                        deallocate prepare z;

                END IF;

        UNTIL done END REPEAT;

        CLOSE cur1;

END; //
delimiter ;