Mein Hat Mein Hat - 5 months ago 25
SQL Question

MySQL Cursor Returns Null

The problem is quite clear but I have tried many things to fix it, including using different variable names than table fields. The fetched value always returns null. The value that is assigned to the cursor fetch is the same data type (int(11) - key_id field). What I am doing is to assign fetched key_id value from tmp_valuesss table into @my_key_id int(11) variable but it keeps coming as null.

/*
debug-to be removed.
DROP PROCEDURE calculate_average_acceleration;
*/

DELIMITER //
CREATE PROCEDURE calculate_thingy
(
IN table_name VARCHAR(100)
)
BEGIN
DECLARE SQL_STATEMENT NVARCHAR(8000);

drop table if exists tmp_valuesss;

SET @SQL_STATEMENT = CONCAT('CREATE TABLE IF NOT EXISTS tmp_valuesss AS (SELECT * FROM ', table_name, ')');
PREPARE STMT FROM @SQL_STATEMENT;
EXECUTE STMT;

alter table tmp_valuesss add the_field float;

begin

DECLARE XXX, YYY, ZZZ FLOAT;
declare my_key_id int(11); /*variable that will be assigned from the value in cursor*/
DECLARE done INT DEFAULT FALSE; /*for cursor break*/

DECLARE cr_cursor cursor for select key_id from tmp_valuesss; /*cursor declaration*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /*break thingy*/

open cr_cursor;

read_loop: LOOP

IF done THEN
LEAVE read_loop;
END IF;

select @my_key_id;

FETCH cr_cursor INTO my_key_id;
END LOOP;

close cr_cursor;

end;

END //
DELIMITER ;

Answer

You are mistaking User Variables (the ones with an @ sign) with Local Variables (the ones with DECLARE).

Your User Variable was never set and thus always null.

Also, DEALLOCATE any PREPARE var.

drop procedure if exists calculate_thingy;
delimiter $$
CREATE PROCEDURE calculate_thingy
(
    IN table_name VARCHAR(100)
)
BEGIN
    DECLARE SQL_STATEMENT NVARCHAR(8000);

    drop table if exists tmp_valuesss;

    SET @SQL_STATEMENT = CONCAT('CREATE TABLE IF NOT EXISTS tmp_valuesss AS     (SELECT * FROM ', table_name, ')');    
    PREPARE STMT FROM @SQL_STATEMENT;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT; -- Drew added -------------------

    alter table tmp_valuesss add the_field float;

    begin

        DECLARE XXX, YYY, ZZZ FLOAT;
        declare my_key_id int(11); /*variable that will be assigned from the     value in cursor*/
        DECLARE done INT DEFAULT FALSE; /*for cursor break*/

        DECLARE cr_cursor cursor for select key_id from tmp_valuesss;     /*cursor declaration*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /*break     thingy*/

        open cr_cursor;

        read_loop: LOOP

            IF done THEN
                LEAVE read_loop;
            END IF;

            -- select @my_key_id; -- Drew removed -------------------

            FETCH cr_cursor INTO my_key_id;
            select my_key_id; -- Drew added -------------------
        END LOOP;

        close cr_cursor;

    end;

END $$
DELIMITER ;
Comments