How to dinamicly create events in several databases
I have tried to create dinamicly events on several databases. I have tried such code:
CREATE PROCEDURE event_DelOldestLinks()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE site_schema varchar(50);
DECLARE start_time datetime DEFAULT '2015-03-17 06:00:00';
DECLARE cur1 CURSOR FOR SELECT table_schema FROM information_schema.tables where table_schema like 'ls_%' and table_name = 'link_links';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO site_schema;
IF done THEN
LEAVE read_loop;
END IF;
set start_time = start_time + INTERVAL 10 MINUTE;
SET @sql_text = CONCAT('
CREATE EVENT ', site_schema, '.DelOldestLinks ON SCHEDULE EVERY 1 DAY STARTS "', start_time , '" ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
DELETE FROM ', site_schema, '.link_links WHERE DATEDIFF( NOW() , `datadodania` ) >5;
END
');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur1;
END //
DELIMITER ;
but I get error: Error Code: 1295. This command is not supported in the prepared statement protocol yet.
Is it possible to create events using stored procedure and cursor?
Subject
Views
Written By
Posted
How to dinamicly create events in several databases
2844
March 16, 2015 10:25AM
1023
March 16, 2015 11:04AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.