MySQL Forums
Forum List  »  Events (Job Scheduler)

Re: Problems with Scheduling event to Call Stored Procedure
Posted by: Mark Milodragovich
Date: October 06, 2009 11:48AM

Here is a working example of a scheduled stored procedure:

-- 1 To check whether job scheduler is on
select @@event_scheduler;

-- 2 Create test table:
CREATE TABLE myevent (
id integer, dt datetime DEFAULT NULL )ENGINE=InnoDB
CHARACTER SET latin1 COLLATE latin1_swedish_ci;

-- 3 Create Stored procedure: (If you’re using Query Browser, you need to run this script on a script editor tab: file>new script tab)
DELIMITER $$
CREATE PROCEDURE test2()
READS SQL DATA
BEGIN
BEGIN
declare vid integer;
set vid= (select count(*)+1 from myevent);
insert into myevent values (vid,now());
END;
END $$
DELIMITER;

-- 4 Create event – etiher with an insert or with a call - “call” is not working correctly
CREATE EVENT TestEvent2
ON SCHEDULE EVERY 5 SECOND STARTS '2009-09-03 00:00:00' ENDS '2009-10-06 17:44:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
call test2();

-- 5 See your event in the event scheduling table
select * from mysql.event;

-- 6 See your test table get populated
select * from myevent;

-- 7 Drop your event from the scheduler
drop event TestEvent2;

-- 8 Drop your test stored procedure to clean up after yourself
drop procedure test2;

-- 9 Drop your test table to clean up even more after yourself
drop table myevent;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problems with Scheduling event to Call Stored Procedure
14729
October 06, 2009 11:48AM


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.