Re: Procedure / Prepared statements error converting table

[Peter Brawley]

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 ;

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 1/18/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]