MySQL Stored Procedures – How to Use Error Handler with Prepare/Execute

error handlingMySQLstored-procedures

I have a procedure defined like this:

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
    table_schema=db and table_name like pattern and
    create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

The idea is to drop tables matching a certain pattern that have a create_time <= a specified date.

When executed, I get an error:

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

How can I declare a handler for an exception in a procedure that is using a prepared statement?

Best Answer

The problem is, when you try to EXECUTE your DYNAMIC query the query returns NULL when there's not found rows.

Test:

mysql> CALL drop_tables_like(
    -> '6', #myint INT, 
    -> 'onare', #pattern VARCHAR(255), 
    -> 'PRUEBA'); #db VARCHAR(255))
+--------+
| sqlSTR |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

So, try to first COUNT to verify if exists any row with your pattern:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_tables_like`(
myint INT, 
pattern VARCHAR(255), 
db VARCHAR(255))
BEGIN
DECLARE CONTINUE HANDLER FOR 1064
SELECT (now() - interval  myint day) into @owa;
SET group_concat_max_len = 65535;
# [DINAMYC QUERY]
SET @TotalRows=(select COUNT(*)
from information_schema.tables where table_schema=db and table_name like pattern and create_time <= @owa);
IF (@TotalRows>0) THEN
    SELECT concat('drop table ', group_concat(table_name)) into @str_sql
    FROM information_schema.tables
    WHERE
        table_schema=db and table_name like pattern and
        create_time <= @owa;
    PREPARE stmt FROM @str_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
ELSE
    SELECT 'There\'s not rows to update.' as sDESC_ERROR;
END IF;
END$$

Result if there's not rows to update:

mysql> CALL drop_tables_like( '6',  'onare',  'PRUEBA');
+-----------------------------+
| sDESC_ERROR                 |
+-----------------------------+
| There's not rows to update. |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
Related Question