From: Peter Brawley
Date: February 14 2006 10:11pm
Subject: Re: Stored procedure issue.
List-Archive: http://lists.mysql.com/mysql/194899
Message-Id: <43F2557D.3010909@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>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