Certain names and stuff transformed to safeguard my work.
DELIMITER // CREATE PROCEDURE glt(IN howMany INT) BEGIN DECLARE f VARCHAR(32); DECLARE done INT DEFAULT 0; DECLARE curs CURSOR FOR SELECT DISTINCT id FROM tpd; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN curs; DROP TABLE IF EXISTS lt; CREATE TEMPORARY TABLE lt LIKE tpd; REPEAT FETCH curs INTO f; IF NOT done THEN INSERT INTO lt SELECT * FROM tpd WHERE id = f ORDER BY TIME DESC LIMIT howMany; END IF; UNTIL done END REPEAT; CLOSE curs; END
The above mentioned code provides the following error on the linux machine, although not a mac machine despite both being situation-sensitive filesystems and getting exactly the same MySQL version:
ERROR 1064 (42000) at line 172: 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 'howMany; END IF; UNTIL done END REPEAT; CLOSE curs; END' at line 16 Bye
Use Prepare Statement for Place query. Take a look at http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html
It appears it may be like:
PREPARE stmt FROM 'INSERT INTO lt SELECT * FROM tpd WHERE id = f ORDER BY TIME DESC LIMIT ?'; EXECUTE stmt USING howMany;
You can't make use of a parameter within the
limit clause here.
You are able to only do this when utilizing PDO. Within this context this is a syntax error.