This example uses a pair of SQL scripts to show how to use events.
This example contains two scripts. Script 1 waits on an event and Script 2 posts the event. Once the event has been posted, the event that is waiting will finish waiting and move on to the next command.
To execute this example code, you will need two consoles so that you can start the WaitOnEvent.sql script and then run the PostEvent.sql script while WaitOnEvent.sql is waiting.
In this example, the stored procedure that waits does not actually do anything after the event has posted; the script merely finishes the wait and returns to the caller. The caller can then proceed to do whatever it wants, which in this case is to SELECT the record that was inserted while we were waiting.
This example waits for only a single event, which is called “record_was_inserted”. Later in this section we will have another script that waits for multiple events using a single “WAIT”.
============================= SCRIPT 1============================= -- SCRIPT NAME: WaitOnEvent.sql -- PURPOSE: -- This is one of a set of scripts that demonstrates posting events -- and waiting on events. The sequence of steps is shown below: -- -- THIS SCRIPT (WaitOnEvent.sql) PostEvent.sql script -- -- CREATE EVENT. -- CREATE TABLE. -- WAIT ON EVENT. -- Insert a record into table. -- Post event. -- SELECT * FROM TABLE. -- -- To perform these steps in the proper order, start running this -- script FIRST, but remember that this script does not finish running -- until after the post_event script runs and posts the event. -- Therefore, you will need two open consoles so that you can leave -- this running/waiting in one window while you run the other script -- post_event) in the other window. -- Create a simple event that has no parameters. -- Note that this event (like any event) does not have any -- commands or data; the event is just a label that allows both the -- posting process and the waiting process to identify which event has -- been posted (more than one event may be registered at a time). -- As part of our demonstration of events, this particular event -- will be posted by the other user after he or she inserted a record. CREATE EVENT record_was_inserted; -- Create a table that the other script will insert into. CREATE TABLE table1 (int_col INTEGER); -- Create a procedure that will wait on an event -- named "record_was_inserted". -- The other script (PostEvent.sql) will post this event. "CREATE PROCEDURE wait_for_event BEGIN -- If possible, avoid holding open a transaction. Note that in most -- cases it's better to do the COMMIT WORK before the procedure, -- not inside it. See "Waiting on Events" at the end of this example. EXEC SQL COMMIT WORK; -- Now wait for the event to be posted. WAIT EVENT WHEN record_was_inserted BEGIN -- In this demo, we simply fall through and return from the -- procedure call, and then we continue on to the next -- statement after the procedure call. END EVENT END WAIT; END"; -- Call the procedure to wait. Note that this script will not -- continue on to the next step (the SELECT) until after the -- event is posted. CALL wait_for_event(); COMMIT WORK; -- Display the record inserted by the other script. SELECT * FROM table1;
Guidelines for committing transaction in Script 1 (Wait0nEvent.sql)
Whenever possible, complete any current transaction before waiting on an event. If you execute a WAIT inside a transaction, then the transaction will be held open until the event occurs and the next COMMIT or ROLLBACK is executed. This means that during the wait, the server will hold locks, which may lead to excessive bonsai tree growth. For details on the Bonsai Tree and preventing its growth, see “Reducing Bonsai Tree Size by Committing Transactions” in solidDB® Administration Guide.
In this example, we have put COMMIT WORK inside the procedure immediately before the WAIT. However, this is not usually a good solution; putting the COMMIT or ROLLBACK inside the “wait” procedure means that if the procedure is called as part of another transaction, then the COMMIT or ROLLBACK will terminate that enclosing transaction and start a new transaction, which is probably not what you want. If, for example, you were entering data into a “child” table with a referential constraint and you are waiting for the referenced data to be entered into the “parent” table, then breaking the transaction into two transactions would simply cause the insert of the “child” record to fail because the parent would not have been inserted yet.
The best strategy is to design your program so that you do not need to WAIT inside a transaction; instead, your “wait” procedure should be called between transactions if that is possible. By using events/waits, you have some control over the order in which things are done and you can use this to help ensure that dependencies are met without actually putting everything into a single transaction. For example, in an “asynchronous” situation you might be waiting for both a child and a parent record to be inserted, and if your database server did not have the “events” feature, then you might require that both records be inserted in the same transaction so that you could ensure referential integrity.
By using events/waits, you can ensure that the insertion of the parent is done first; you can then put the insertion of the child record in a second transaction because you can guarantee that the parent will always be present when the child is inserted. (To be more precise, you can ALMOST guarantee that the parent will be present when the child is inserted. If you break up the insertions into two different transactions, then even if you ensure that the parent is inserted before the child, there is a slight chance that the parent would be deleted before the program tried to insert the child record.)
============================= SCRIPT 2============================= -- SCRIPT NAME: PostEvent.sql -- PURPOSE: -- This script is one of a set of scripts that demonstrates posting -- events and waiting on events. The sequence of steps is shown below: -- -- WaitOnEvent.sql THIS SCRIPT (PostEvent.sql) -- -- Create event. -- Create table. -- Wait on event. -- INSERT A RECORD INTO TABLE. -- POST THE EVENT. -- Select * from table. -- Insert a record into the table. INSERT INTO table1 (int_col) VALUES (99); COMMIT WORK; -- Create a stored procedure to post the event. "CREATE PROCEDURE post_event BEGIN -- Post the event. POST EVENT record_was_inserted; END"; -- Call the procedure that posts the event. CALL post_event(); DROP PROCEDURE post_event; COMMIT WORK;