MySQL Forums
Forum List  »  Events (Job Scheduler)

Prepared Statement inside a Scheduled Event?
Posted by: Ian Gates
Date: November 06, 2012 08:38AM

Is there a limitation with using Prepared Statements inside a [scheduled] event.

I am trying to create an event that will rename a table at the start of each month, so it changes from 'tablename' to 'tablename_yyyymm' (e.g. tablename_201210). I have all the code, and on it's own it works fine, but when I test it in the Scheduled Event it seems to stop at 'EXECUTE stmt;'

My code is:
DELIMITER //
CREATE EVENT IF NOT EXISTS tableName_change
ON SCHEDULE
EVERY 1 MONTH STARTS '2012-12-01 00:00:00'
ON COMPLETION PRESERVE
COMMENT ''
DO
BEGIN

#Stop Replication
STOP SLAVE;

#If Month=Jan Use Last Year
SET @YearToUse = (
SELECT
CASE
WHEN MID(NOW(),6,2) = 1 THEN LEFT(NOW(),4)-1
ELSE LEFT(NOW(),4)
END AS YearToUse
);

#If Month=Jan Set Month to 12
SET @MonthToUse = (
SELECT
CASE
WHEN MID(NOW(),6,2) = 1 THEN 12
ELSE MID(NOW(),6,2)-1
END AS MonthToUse
);

#Name of new Table
SET @NewName = CONCAT('tablename_',@YearToUse,@MonthToUse);

#Rename Table to table_YYYYMM
SET @SQL = (CONCAT('RENAME TABLE tableName TO ',@NewName));
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#Create blank table
SET @SQL = (CONCAT('CREATE TABLE tablename LIKE ',@NewName));
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#Restart Replication
START SLAVE;

END //
DELIMITER;

Options: ReplyQuote


Subject
Views
Written By
Posted
Prepared Statement inside a Scheduled Event?
4414
November 06, 2012 08:38AM


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.