List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 18 2006 9:30pm
Subject:Re: Procedure / Prepared statements error converting table
View as plain text  
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 ;
>
>
>   

Attachment: [text/html]
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
Thread
Procedure / Prepared statements error converting tableDan Burke18 Jan
  • Re: Procedure / Prepared statements error converting tableSGreen18 Jan
    • Help in joining three tablesImran18 Jan
      • Re: Help in joining three tablesSGreen18 Jan
      • Re: Help in joining three tablesgerald_clark18 Jan
      • Re: Help in joining three tablesRhino18 Jan
        • Re: Help in joining three tablesSGreen18 Jan
          • Re: Help in joining three tablesRhino18 Jan
  • Re: Procedure / Prepared statements error converting tablePeter Brawley18 Jan
RE: Procedure / Prepared statements error converting tableDan Burke18 Jan