MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:June 28 2007 10:33pm
Subject:dynamic sql in proc
View as plain text  
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 ;
Thread
Geographic math problemM528 Jun
  • Re: Geographic math problemPhilip Hallstrom28 Jun
  • Re: Geographic math problemmos28 Jun
  • Re: Geographic math problemDavid T. Ashley28 Jun
    • dynamic sql in procBryan Cantwell28 Jun
  • Re: dynamic sql in procViSolve DB Team2 Jul