============================= SCRIPT 1=============================
-- SCRIPT NAME: MultiWaitExamplePart1.sql -- PURPOSE:
-- This code shows how to wait on more than one event.
-- If you run this demonstration, you will see that a "wait" lasts only
-- until one of the events is received. Thus a wait on multiple events
-- is like an "OR" (rather than an "AND"); you wait until event1 OR
-- event2 OR ... occurs.
--
-- This demo uses 2 scripts, one of which waits for an event(s) and one
-- of which posts an event.
-- To run this example, you will need 2 consoles.
-- 1) Run this script (MultiWaitExamplePart1.sql) in one window. After
-- this script reaches the point where it is waiting for the event, then
-- start Step 2.
-- 2) Run the script MultiWaitExamplePart2.sql in the other window.
-- This will post one of the events.
-- After the event is posted, the first script will finish.
-- Create the 3 different events on which we will wait.
CREATE EVENT event1;
CREATE EVENT event2(i INTEGER);
CREATE EVENT event3(i INTEGER, c CHAR(4));
-- When an event is received, the process that is waiting on the event
-- will insert a record into this table. That lets us see which events
-- were received.
CREATE TABLE event_records(event_name CHAR(10));
-- This procedure inserts a record into the event_records table.
-- This procedure is called when an event is received.
"CREATE PROCEDURE insert_a_record(event_name_param CHAR(10))
BEGIN
  EXEC SQL PREPARE insert_cursor
  INSERT INTO event_records (event_name) VALUES (?);
  EXEC SQL EXECUTE insert_cursor USING (event_name_param);
  EXEC SQL CLOSE insert_cursor;
  EXEC SQL DROP insert_cursor;
END";
-- This procedure has a single "WAIT" command that has 3 subsections;
-- each subsection waits on a different event.
-- The "WAIT" is finished when ANY of the events occur, and so the
-- event_records table will hold only one of the following:
-- "event1",
-- "event2", or
-- "event3".
"CREATE PROCEDURE event_wait(i1 INTEGER)
  RETURNS (eventresult CHAR(10))
  BEGIN
    DECLARE i INTEGER;
    DECLARE c CHAR(4);
-- The specific values of i and c are irrelevant in this example.
i := i1; c := 'mark';
-- Set eventresult to an empty string.
eventresult := '';
-- Will we exit after any of these 3 events are posted, or must
-- we wait until all of them are posted? The answer is that
-- we will exit after any one event is posted and received.
WAIT EVENT
-- When the event named "event1" is received...
WHEN event1
BEGIN eventresult := 'event1';
  -- Insert a record into the event_records table showing that
  -- this event was posted and received.
  EXEC SQL PREPARE call_cursor
  CALL insert_a_record(?);
  EXEC SQL EXECUTE call_cursor USING (eventresult);
  EXEC SQL CLOSE call_cursor;
  EXEC SQL DROP call_cursor;
RETURN;
END EVENT
WHEN event2(i)
  BEGIN eventresult := 'event2';
  EXEC SQL PREPARE call_cursor2
  CALL insert_a_record(?);
  EXEC SQL EXECUTE call_cursor2 USING (eventresult);
  EXEC SQL CLOSE call_cursor2;
  EXEC SQL DROP call_cursor2;
  RETURN;
END EVENT
WHEN event3(i, c)
  BEGIN eventresult := 'event3';
  EXEC SQL PREPARE call_cursor3
  CALL insert_a_record(?);
  EXEC SQL EXECUTE call_cursor3 USING (eventresult);
  EXEC SQL CLOSE call_cursor3;
  EXEC SQL DROP call_cursor3;
  RETURN;
END EVENT
END WAIT
END";
COMMIT WORK;
-- Call the procedure that waits until one of the events is posted.
CALL event_wait(1);
-- See which event was posted.
SELECT * FROM event_records;

=========================== SCRIPT 2 ===================================
-- SCRIPT NAME: MultiWaitExamplePart2.sql -- PURPOSE:
-- This is script 2 of 2 scripts that show how to wait for multiple
-- events. See the instructions at the top of MultiWaitExamplePart1.sql.
-- Create a stored procedure to post an event.
"CREATE PROCEDURE post_event1
BEGIN
  -- Post the event.
  POST EVENT event1;
END";
--Create a stored procedure to post the event.
  "CREATE PROCEDURE post_event2(param INTEGER)
BEGIN
  -- Post the event.
  POST EVENT event2(param);
END";
--Create a stored procedure to post the event.
"CREATE PROCEDURE post_event3(param INTEGER, s CHAR(4))
BEGIN
  -- Post the event.
  POST EVENT event3(param, s);
END";
COMMIT WORK;
-- Notice that to finish the "wait", only one event needs to be posted.
-- You may execute any one of the following 3 CALL commands to post an
-- event.
-- We've commented out 2 of them; you may change which one is de
-- commented.
CALL post_event1();
--CALL post_event2(2);
--CALL post_event3(3, 'mark');