List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 14 2006 10:11pm
Subject:Re: Stored procedure issue.
View as plain text  
>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

Thread
Stored procedure issue.DreamWerx14 Feb
  • Re: Stored procedure issue.Peter Brawley14 Feb
  • APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2persant mpote16 Feb
    • Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2SGreen16 Feb
    • Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2mysql16 Feb