I'm having some issues creating a stored procedure to optimize tables
in the database.
PREPARE accepts only CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT,
SET and UPDATE.
PB
-----
DreamWerx wrote:
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
------
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-----
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
