CODEWITHSUNDEEP
mysql
HorusKol
HorusKol

Reputation: 8706

Stored procedure with loops and prepared statements - why does it not work?

I am trying to selectively flush a limited set of tables in a particular database - and after bit of investigation (swearing and cussing), I've arrived at:

DROP PROCEDURE IF EXISTS local_flush_cache;

DELIMITER $$

CREATE PROCEDURE local_flush_cache()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a VARCHAR(64);
DECLARE crsr CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema=database() AND table_name LIKE 'cache_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN crsr;

read_loop: LOOP
FETCH crsr INTO a;

IF done THEN
LEAVE read_loop;
END IF;

SET @s = CONCAT('DELETE FROM ', a);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END LOOP;

CLOSE crsr;

END$$
DELIMITER ;

The only thing is - it doesn't seem to be working. When I check the contents of the cache tables, they've not been cleared.

When I call the procedure, the only output I get is:

mysql> call local_flush_cache();
Query OK, 0 rows affected (0.00 sec)

And then when I get a row count from one of the cache tables, I get a non-zero result.

Upvotes: 1

Views: 958

Answers (2)

HorusKol
HorusKol

Reputation: 8706

After taking a lunch break - the problem ended up being the query parameter for the select in the procedure:

DECLARE crsr CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema=database() AND table_name LIKE 'cache_%';

The table I was testing was actually called cache - so this would obviously not be matched by the parameter above. The line actually needs to be:

DECLARE crsr CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema=database() AND table_name LIKE 'cache%';

This works great now.

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

Since you are setting the delimiter to $$ on the first line, you need to change the semicolon on the second line to $$ instead.

Upvotes: 1

Related Questions