Wednesday, January 16, 2008

Long Time, No C

In an attempt to get more two-way action, and a rest for my Vicodin-riddled brain (due to a new neck injury... thanks 2008! we're off to a great start), I'd like to post a mySQL problem. Not because I cannot find enough information about mySQL - but because there is too much - I am hopelessly attempting to trudge through the kludge.
Since I would like to reduce network traffic between mySQL and application, I'm making a stored procedure to calculate report data (rather than in the app layer). This should be simple. I wrote it, ran it, then.... nothing. It didn't break, it just always returns null. So I trimmed it back, to it's barest bones to this:
DELIMITER $$
CREATE PROCEDURE sp_my_pro$$
CREATE PROCEDURE sp_my_pro(IN a_id INT)
BEGIN
DECLARE done BOOLEAN default FALSE;
DECLARE id INT;
DECLARE my_cursor CURSOR FOR SELECT id FROM my_table WHERE id = a_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

OPEN my_cursor;
cursor_loop: LOOP
FETCH my_cursor INTO id;
SELECT id;
IF done THEN LEAVE cursor_loop; END IF;
END LOOP cursor_loop;
CLOSE my_cursor;
END$$
DELIMITER ;
This should be the simplest stored proc ever... select a row from a table by id, iterate over the result set (of 1) and select it. If I pass in 1, I expect to see 1, pass in 2, I expect 2, etc... but the result is always the same:
+------+
| id |
+------+
| NULL |
+------+
Any takers? I'm afraid I know far more about Postgres, Oracle and how-to-life-weights-improperly than about mySQL stored procs.