>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
