Re: time delay in *.sql file
Anitha Prasad schrieb:
> Hello,
> I need to add time delay( something like sleep 100 on unix) besfore
> my
> transaction closes the database.
> To make myself clear. For example I have a file delete.sql:
>
> DATABASE customer;> DELETE * from customer where c_num>2500;
>
>
First, I thought, I'd create a stored procedure, as follows:CREATE
PROCEDURE sleep(seconds SMALLINT)DEFINE end_time DATETIME YEAR TO
SECOND;
DEFINE curr_time DATETIME YEAR TO SECOND;
LET curr_time = CURRENT YEAR TO SECOND;
LET end_time = curr_time + seconds UNITS SECOND;
WHILE (curr_time<=end_time)
LET curr_time = CURRENT YEAR TO SECOND;
END WHILE;
END PROCEDURE;
I was surprised, IT DIDN'T WORK! I found out, the CURRENT
is only processed once when calling the procedure and always
evaluates to the same value. I guess there's no need to ask why,
it's maybe ... because of the implementation ...
Secondly, I figured out, that at least for UNIX-Systems (as Server)
that should do the trick:
CREATE PROCEDURE sleep(seconds INTEGER)DEFINE cmd_string CHAR(20);
LET cmd_string = 'sleep '||seconds;
SYSTEM cmd_string;
END PROCEDURE;
Ok, create the stored procedure sleep in your database (the latter).
Then execute it, whenever you need a delay:
EXECUTE PROCEDURE sleep(<number of seconds to delay>);
e.g.
DATABASE customer;DELETE * from customer where c_num>2500;
EXECUTE PROCEDURE sleep(5);
Regards,
Roland