WAITFOR statement

Use this statement to delay processing for the current connection for a specified amount of time or until a given time.

Syntax

WAITFOR { 
DELAY time 
| TIME time }
[ CHECK EVERY integer ]
[ AFTER MESSAGE BREAK ]

time : string

Parameters

DELAY clause If DELAY is used, processing is suspended for the given interval.
TIME clause If TIME is specified, processing is suspended until the database server time reaches the time specified. If the current server time is greater than the time specified, processing is suspended until that time on the following day.
CHECK EVERY clause This optional clause controls how often the WAITFOR statement wakes up. By default, it wakes up every 5 seconds. The value is in milliseconds, and the minimum value is 250 milliseconds.
AFTER MESSAGE BREAK clause The WAITFOR statement can be used to wait for a message from another connection. In most cases, when a message is received it is forwarded to the application that executed the WAITFOR statement and the WAITFOR statement continues to wait. If the AFTER MESSAGE BREAK clause is specified, when a message is received from another connection, the WAITFOR statement completes. The message text is not forwarded to the application, but it can be accessed by obtaining the value of the MessageReceived connection property.
For more information about the MessageReceived property, see Connection properties.

Remarks

The WAITFOR statement wakes up periodically (every 5 seconds by default) to check if it has been canceled or if messages have been received. If neither of these has happened, the statement continues to wait.

WAITFOR provides an alternative to the following statement:

CALL java.lang.Thread.sleep( <time_to_wait_in_millisecs> );

Because scheduled events execute on their own connection, scheduled events are often a better choice than using WAITFOR TIME.

Permissions

None

Side effects

The implementation of this statement uses a worker thread while it is waiting. This uses up one of the threads specified by the -gn database option (the default is 20 threads).

Standards and compatibility

SQL/2003 Vendor extension.

Examples

The following example waits for three seconds:

WAITFOR DELAY '00:00:03';

The following example waits for 0.5 seconds (500 milliseconds):

WAITFOR DELAY '00:00:00:500';

The following example waits until 8 PM:

WAITFOR TIME '20:00';

In the following example, connection 1's WAITFOR statement completes when it receives the message from connection 2:

// connection 1:
BEGIN
  DECLARE msg LONG VARCHAR;
  LOOP  // forever
    WAITFOR DELAY '00:05:00' AFTER MESSAGE BREAK;
    SET msg = CONNECTION_PROPERTY('MessageReceived');
    IF msg != '' THEN
      MESSAGE 'Msg: ' || msg TO CONSOLE;
    END IF;
  END LOOP
END
// connection 2:
MESSAGE 'here it is' FOR connection 1

Discuss this page in DocCommentXchange.
Send feedback about this page using email.