Wednesday, 14 August 2013

MySQL Cursor Loop And Loop Handler Issue

MySQL Cursor Loop And Loop Handler Issue

I am having one cursor loop to process one result, I am facing problem
like, I have one handler for cursor with not found check, upon not check
it is setting up one variable to detect the end of result, but as I am
having one query between that loop which is also resulting in not found
condition so it is ultimately setting cursor loop handler variable and my
loop terminating prematurely.
Following is pseudo code
DECLARE CURSOR tmp as SELECT something FROM somewhere WHERE 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
REPEAT
FETCH tmp INTO somevariable;
IF NOT v_done THEN
/* v_done turn to 1 after following select execute */
SELECT notfound INTO someone1 FROM somemorething WHERE 0;
END IF;
UNTIL v_done END REPEAT;
CLOSE tmp;
this is happening with MySql version 5.5.29 (local) and when I check on
another server (Production) version 5.1.54, same code will not causing any
issue and loop is not breaking.
I am not sure if this is related to mysql version change, if that is the
case what can be the ideal code in this kind of situation?
I understand I can always make COUNT logic to check row exists or not? but
if found I also need its columns, so I have to write to queries each time,
one check count and another query give me result if count return positive
number.
Also, I can write another block for that query and give it another not
found handler so my parent variable will not be overwritten.
But if there is any 3rd possibility

No comments:

Post a Comment