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 ;$$ ----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]