dynamic sql in proc

From: Bryan Cantwell <bcantwell(at)firescope.net>
Date: Thu Jun 28 2007 - 18:33:39 EDT

I have the following proc... when I run it I get a response that says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1".

I just want a programatic way to upgrade db engine to innodb where I don't know exactly what tables exist...

DELIMITER $$ DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$ CREATE PROCEDURE `MYISAMtoINNODB`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_name VARCHAR(255);
DECLARE cur1 CURSOR FOR select table_name from information_schema.tables where table_schema='firescope' and table_type='BASE TABLE' and engine='MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;
REPEAT
        FETCH cur1 INTO table_name;
  SET @table_name=table_name;

	IF NOT done THEN
     SET @stmt_text=CONCAT("ALTER TABLE ", @table_name, "  ENGINE =
InnoDB");
     PREPARE stmt FROM @stmt_text;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END $$ DELIMITER ;

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com

Received on Thu Jun 28 18:34:54 2007