All Forums Database
Pterodactyl 1 post Joined 06/11
02 Jun 2011
How to sleep/pause in a Stored Procedure?

I (geniunely) need to sleep inside a stored procedure as part of a loop which checks table contents and exits the loop based on content. Let's say for 5 minutes at a time.

I don't *think* there is such a command (even though there d*mn well should be :) )

Has anybody written any code which emaulates such a command?

dnoeth 4628 posts Joined 11/04
02 Jun 2011

It should be easy to write a C-UDF which only executes a sleep(x).

But could you elaborate on what you actually try to accomplish?
"a loop which checks table contents" sounds like you're waiting for an insert into that table.
A different approach might be a QUEUE table.

Dieter

Dieter

rkolluru 1 post Joined 06/09
19 Dec 2011

You could create a stored procedure below and call it However you need to create a set of stored procedure if you need to have sleep for various times. Below is an example for delay for 1 minute

REPLACE PROCEDURE CDW_SANDBOX.DELAY_1minute()
BEGIN
DECLARE
v_delaycounter TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    REPEAT
    SET v_delaycounter=v_delaycounter;
    UNTIL CURRENT_TIMESTAMP=v_delaycounter+ INTERVAL '1' MINUTE
    END REPEAT;
END;
 

thanks

rama

mlazatin 4 posts Joined 04/11
26 Jun 2014

Please don't do the looping that Rama suggested. That will cause DBQL tables to fill up if DBQL is turned on. Sorry Rama.
 
May I suggest this:
Create a c file with the following source code:
----------------------------------
#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include <unistd.h>
void SleepInSeconds(int *SleepTimeInSeconds, char sqlState[6])
{
 sleep(*SleepTimeInSeconds);
}
----------------------------------
Then execute this SQL:
 
REPLACE PROCEDURE SYSLIB.SleepInSeconds(IN SleepTimeInSeconds INT)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!SleepInSeconds!C:\SleepInSeconds.c';

desai51 2 posts Joined 04/11
31 Jul 2014

I wonder why Rama's suggestion would fill up the DBQL.  I don't see any queries being run in his implementation of sleep.  Please let us know which DBQL table will be affected due to a loop which does not involve queries in the Stored  procedure.

dnoeth 4628 posts Joined 11/04
31 Jul 2014

It's probably not filling the Query Log or Access Log, but it's a waste of resources, it's a loop possibly consuming 100% CPU during the sleep.

Dieter

You must sign in to leave a comment.