CODEWITHSUNDEEP
mysql
T.S.
T.S.

Reputation: 19350

'While' loop is not exiting after set number of seconds

The goal here is to make it busy for 'n' seconds and then exit. This is latest version of code. I tried direct date1 < date2 in while, etc

delimiter $$
CREATE  FUNCTION `DelayResponse`(pSeconds int) RETURNS varchar(1)
BEGIN 
    DECLARE x int;
    DECLARE EndDateTime datetime ;
    DECLARE CurrDateTime datetime ;
    Set x = 0;
    set EndDateTime = DATE_ADD(now(), INTERVAL pSeconds second); 
    set CurrDateTime = now(); 

    WHILE x = 0 DO
        -- expecting to set x to 1 in pSeconds but nope
        if  CurrDateTime > EndDateTime then 
            set x = 1;
        end if;
        SET CurrDateTime = now(); 
    END WHILE;
    RETURN 'X';
END$$
delimiter ;

select DelayResponse(5) X

I can't get out the loop here. What am I missing?

Another version that does not work. Just spins running... not ending

delimiter $$
CREATE  FUNCTION `DelayResponse`(pSeconds int) RETURNS varchar(1)
BEGIN 
    DECLARE x int;
    DECLARE EndDateTime datetime ;
    Set x = 0;
    set EndDateTime = DATE_ADD(now(), INTERVAL pSeconds second); 

    WHILE x <= 0 DO
            set x = now() - EndDateTime;
    END WHILE;
    RETURN 'X';
END$$
delimiter ;

Upvotes: 2

Views: 186

Answers (2)

Shadow
Shadow

Reputation: 34232

This behaviour is a result of a documented, but less-known feature of now(). So, this is not a bug!

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

So, while @Barmar is correct in suggesting the use of sleep(), you can make your original code work with sysdate() instead of now().

Upvotes: 2

Barmar
Barmar

Reputation: 781096

You can use the built-in SLEEP() function instead of your own loop.

delimiter $$
CREATE  FUNCTION `DelayResponse`(pSeconds int) RETURNS varchar(1)
BEGIN 
    DO SLEEP(pSeconds);
    RETURN 'X';
END$$
delimiter ;

select DelayResponse(5) X

Upvotes: 1

Related Questions