Bug #17153 Cursors impossible on dynamic SQL
Submitted: 6 Feb 2006 13:46 Modified: 6 Feb 2006 21:29
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.0.18 OS:Any (any)
Assigned to:
Triage: Triaged: D5 (Feature request)

[6 Feb 2006 13:46] Beat Vontobel
Description:
MySQL should allow cursors on dynamic SQL (prepared statements).

How to repeat:
delimiter //
CREATE PROCEDURE dynamic_cursor()
        DETERMINISTIC
        READS SQL DATA
        SQL SECURITY INVOKER
BEGIN
        PREPARE prp FROM 'DECLARE cur CURSOR FOR SELECT * FROM information_schema.TABLES';
        EXECUTE prp;
        OPEN cur;
        CLOSE cur;
END //
ERROR 1324 (42000): Undefined CURSOR: cur

Suggested fix:
Don't check for existence of cursors at define time but only on execution of a stored routine.
[6 Feb 2006 14:23] Valerii Kravchuk
Thank you for a useful feature request.
[6 Feb 2006 14:33] Giuseppe Maxia
This would be definitely a most welcome feature.
So far, to overcome this missing functionality, I have to use temporary tables
and copying data around, which is neiter efficient nor elegant.
If this addition could make it to MySQL 5.1, before the beta version is frozen,
it would be really great!

Regards
Giuseppe
[6 Feb 2006 21:29] Beat Vontobel
Thanks Valeriy for accepting, thanks Giuseppe for supporting. I also wrote a little blog post to support this request: http://www.futhark.ch/mysql/130.html
[13 Jun 2007 7:05] Domas Mituzas
Worklog task for this feature request: 
http://forge.mysql.com/worklog/task.php?id=3433