Using prepared statements with cursor

Question

I put the cursor declaration in the prepared statement and then executed it, then returns an error #1324 - Undefined CURSOR: getid.

How do I solve this problem?

delimiter ;;

drop procedure if exists test2;;

create procedure test2(table_id VARCHAR(25))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE id_new INT;
    DECLARE stmt1 VARCHAR(1024);
    DECLARE stmt2 VARCHAR(1024);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @sqltext1 := CONCAT('DECLARE getid CURSOR FOR SELECT entryId FROM ',table_id,' ORDER BY entryId');
    PREPARE stmt1 FROM @sqltext1;
    EXECUTE stmt1;

    SET @id_new = 1; 

    OPEN getid;

    FETCH getid into id;
    REPEAT
        SET @sqltext2 := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');
        PREPARE stmt2 FROM @sqltext2;
        EXECUTE stmt2 USING @new_id, id;
        SET @id_new = @id_new + 1;
        FETCH getid into id;
    UNTIL done END REPEAT;
    CLOSE getid;
END
;;

CALL test2('Test');

Ravinder Reddy Ravinder Reddy · Accepted Answer · 2014-02-16T14:07:13

Some rules:

All declarations must be at one place in a sequence.
You can't use variable names in cursor declarations.
Handler declarations must be after cursor declarations.
You can't use local variable names (id) as bound parameters for prepared statements. You can only use session variables (say @_id).

To overcome such problems, you can adopt following solution.

Define a temporary table using the input parameter to the SP.
Now declare the cursor on the same table and use it.
Drop the temporary table created.

Following example should work on your tables.

delimiter $$

drop procedure if exists test2$$

create procedure test2( table_id varchar(25) )
begin
  set @temp_query = 'drop temporary table if exists temp_cursor_table';
  prepare pst from @temp_query;
  execute pst;
  drop prepare pst; -- or
  -- deallocate prepare pst;

  set @temp_table_query='create temporary table temp_cursor_table ';
  set @temp_table_query=concat( @temp_table_query, ' select entryId from ' );
  set @temp_table_query=concat( @temp_table_query, table_id );
  set @temp_table_query=concat( @temp_table_query, ' order by entryId' );

  prepare pst from @temp_table_query;
  execute pst;
  drop prepare pst;

  -- now write your actual cursor and update statements
  -- in a separate block
  begin
    declare done int default false;
    declare id int;
    declare id_new int;
    declare stmt1 varchar(1024);
    declare stmt2 varchar(1024);

    declare getid cursor for  
              select entryId from temp_cursor_table order by entryId;
    declare continue handler for not found set done = 1;

    set @id_new = 1; 

    open getid;
    fetch getid into id;
    repeat
      set @sqltext2 := concat( 'update ', table_id );
      set @sqltext2 := concat( @sqltext2, ' set entryId = ? where entryId = ?' );
      set @_id = id;
      prepare stmt2 from @sqltext2;
      execute stmt2 using @new_id, @_id;
      set @id_new = @id_new + 1;
      fetch getid into id;
    until done end repeat;
    close getid;
  end;
end;
$$

delimiter ;

Now call the procedure with table_id value.

call test2( 'Test' );

Using prepared statements with cursor

1 Answers