Drop procedure if EXISTS rotatepjp;
DELIMITER //
CREATE PROCEDURE rotatepjp()
BEGIN
DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT;
DECLARE u_pjpstartdate, pjp_fordate, tilldate, newdate DATE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT value INTO settingval FROM settings where name = 'autorotatepjp';
IF settingval > 0 THEN
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT id, pjpstartdate, frequency FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
#open cursor
OPEN cur1;
#starts the loop
the_loop: LOOP
#get the values of each column into our variables
FETCH cur1 INTO u_id, u_pjpstartdate, u_frequency;
IF done THEN
LEAVE the_loop;
END IF;
#Do some post processing
IF u_frequency > 0 THEN
SET tilldate = DATE_ADD( u_pjpstartdate, INTERVAL u_frequency DAY );
IF tilldate = CURDATE() THEN
BEGIN
DECLARE done1 INT DEFAULT FALSE;
DECLARE cur2 CURSOR FOR SELECT user_id, beat_id, fordate FROM pjps where user_id = u_id and fordate >= u_pjpstartdate and fordate < tilldate;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
OPEN cur2;
the_loop1: LOOP
#get the values of each column into our variables
FETCH cur2 INTO pjp_userid, pjp_beatid, pjp_fordate;
IF done1 THEN
LEAVE the_loop1;
END IF;
SET newdate = DATE_ADD( pjp_fordate, INTERVAL u_frequency DAY );
INSERT INTO pjps (user_id, beat_id, fordate, created, modified )
VALUES ( pjp_userid, pjp_beatid, newdate, NOW(), NOW() );
END LOOP the_loop1;
UPDATE users set pjpstartdate = CURDATE(), modified = NOW() where id = u_id;
END;
END IF;
END IF;
END LOOP the_loop;
END;
END IF;
COMMIT ;
END //
DELIMITER ;
this code is working in innodb_version 5.5.34 but not working innodb_version 5.6.21…….any clue, where I am doing mistake???
error – #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
Best Answer
When creating a Stored Procedure, you do not need
COMMIT;
.I have never used it for Stored Procedures.
No one should have to because Stored Procedures are stored in a MyISAM table (
mysql.proc
)Please remove the
COMMIT;
before theEND //
and try again.I would also change line to say
and the end clause to
and try again
I thought you were working in the mysql client. If your problem is SQLFiddle, here is post from StackOverflow by its Author (Execute triggers stored procedures on SqlFiddle. Mysql)
Based on it, stop using
DELIMITER
altogether. Look for the fourth button under the query pane.I just installed MySQL 5.6.22 for Windows on my laptop and ran your code as is. It Works !!!
The only culprit left would be phpmyadmin. It cannot handle delimiters against the latest version of MySQL, which is 5.6. You may need an upgrade of phpmyadmin.