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" <bcantwell@firescope.net>
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/mysql?unsub=m...rt@visolve.com
>
>
>
>
> --
> 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
>
>