Re: dynamic sql in proc

[ViSolve DB Team]

Hi,
just try like:
mysql> create procedure mi()
   -> begin
   -> declare done int default 0;
   -> declare table_name varchar(50);
   -> declare cur1 cursor for select tables.table_name from 
information_schema.tables where table_schema='test' 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 @stext=CONCAT("Alter table " , @table_name, " engine=InnoDB");
   -> prepare smt from @stext;
   -> execute smt;
   -> deallocate prepare smt;
   -> end if;
   -> until done end repeat;
   -> close cur1;
   -> end;
   -> |
Query OK, 0 rows affected (0.03 sec)
mysql> call mi();
Query OK, 2 rows affected (0.01 sec)

+-----------+
| version() |
+-----------+
| 5.0.18    |
+-----------+
1 row in set (0.00 sec)

Thanks
ViSolve DB Team.
----- Original Message ----- 
From: "Bryan Cantwell" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, June 29, 2007 4:03 AM
Subject: dynamic sql in proc


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/[EMAIL PROTECTED]




--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.9.10/875 - Release Date: 6/27/2007 
9:08 PM




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