List:General Discussion« Previous MessageNext Message »
From:DreamWerx Date:February 14 2006 7:46pm
Subject:Stored procedure issue.
View as plain text  
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 ;$$

-----
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