Errm, needed to take the grants table updates out of the repat loop:
CREATE PROCEDURE RenameDatabase (IN oldname CHAR(64), IN newname CHAR(64))
BEGIN
DECLARE sname CHAR(64) DEFAULT NULL;
DECLARE rows INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
CREATE DATABASE IF NOT EXISTS newname;
REPEAT
SELECT table_name INTO sname
FROM information_schema.tables AS t
WHERE t.table_schema = oldname
LIMIT 1;
SET rows = FOUND_ROWS();
IF rows = 1 THEN
SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname,
' TO ', newname, '.', sname );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
SET total = total + 1;
END IF;
UNTIL rows = 0 END REPEAT;
IF total > 0 THEN
SET @scmd = CONCAT( "UPDATE mysql.db SET Db = '",
@newname,
"' WHERE Db = '", @oldname, "'" );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
SET @scmd = CONCAT( "UPDATE mysql.columns_priv SET Db = '",
@newname,
"' WHERE Db = '", @oldname, "'" );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
FLUSH PRIVILEGES;
-- MySQL does not yet support PREPARE DROP DATABASE:
-- SET @scmd = CONCAT( 'DROP DATABASE ', oldname );
-- PREPARE cmd FROM @scmd;
-- EXECUTE cmd;
-- DEALLOCATE PREPARE cmd;
END IF;
END;
|
DELIMITER ;
PB
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
