Advantage Developer Zone


Advantage Events(Notifications) Overview

Tuesday, July 8, 2008

Events also referred to as notifications, were added as a feature in Advantage version 9.0. Events can be used to alert a client when a particular action has occurred on the server. This is most commonly done using a trigger to signal an event (i.e. a table update) which will cause the server to notify all the clients waiting for the event. 

To use events you must first create an event on the server, signal the event at the proper time, and configure the client to wait for the event to occur. How the client responds to the event is handled completely by the client application.

Creating Events

Events are created using the sp_CreateEvent which registers the event on the server for a specific connection. Events are registered on the server using the Connection Path and the Event name. Therefore it is important that all clients who wish to wait for an event use the same connection path to the data. Events can be created on both free table and dictionary-bound connections. 

The sp_CreateEvent system procedure takes two parameters. First is the event name up to 200 characters in length. Second is options which is reserved for future use, pass zero for this parameter. An example is below:

EXECUTE PROCEDURE sp_CreateEvent('MyEvent', 0)

Signaling Events

When an event has occurred for which you want to notify clients about, use the sp_SignalEvent system procedure. This procedure tells the server that a particular event has occurred and will notify the client(s) waiting for the event. 

The sp_SignalEvent system procedure takes three arguments. First is the name of the event up to 200 characters in length. Second is whether or not to wait for a transaction to complete before signaling the event. Note that Advantage transactions use read committed isolation level meaning that other users cannot view changes until the transaction is committed. Third is options which is reserved for future use, pass zero for this parameter. In the example the event will be signaled immediately even if a transaction has not been committed.

EXECUTE PROCEDURE sp_SignalEvent('MyEvent', false, 0)

For security purposes on a dictionary-bound connection the sp_SignalEvent system procedure can only be called by a trigger or stored procedure. Calling sp_SignalEvent from a client application will cause a 5054 “Permission Denied”. To signal events from a client connected to a dictionary, create a simple stored procedure to wrap the sp_SignalEvent call. Clients using free table connections can call the sp_SignalEvent system procedure directly.

Waiting for Events

Each client which wants to be notified when and event occurs needs to call either the sp_WaitForEvent or sp_WaitForAnyEvent system procedures. Each of these procedures returns the Event Name and Event Count, the number of times the event was signaled. These system procedures use an efficient wait mechanism which limits the amount of traffic between client and server and does not consume any worker threads while waiting. 

The sp_WaitForEvent system procedure call takes four parameters. First the event name up to 200 characters. Second the timeout, how long the client will wait before ending the procedure, in milliseconds. Third the poll interval which is used for local server connections, passing 0 specifies the default value 300 milliseconds. The last parameter is options, which is reserved for future use, pass zero for this parameter. The example will wait for five seconds for MyEvent to be signaled.

EXECUTE PROCEDURE sp_WaitForEvent('MyEvent', 5000, 0, 0)

The sp_WaitForAnyEvent system procedure call takes three parameters. First is the timeout, second the poll interval and finally options. The example will wait for 5 seconds and return if any event the client has created on the connection is signaled.

EXECUTE PROCEDURE sp_WaitForAnyEvent(5000, 0, 0)

It is important that the client call sp_CreateEvent before waiting for events. If the client calls sp_WaitForEvent with an event name which doesn’t exist a 5041 “Object not Found” error will be returned. The sp_WaitForAnyEvent will still run but it will return an empty resultset since there are no events registered for the client. 

Additionally since the client has registered a particular event, notifications will be queued up for the client until one of the wait system procedures is called. Therefore it is not necessary to constantly wait for events. This also ensures that the client does not miss an event it is registered for if the event is signaled while the client is not running a wait system procedure.

Removing Events

Events can be removed from the server using either the sp_DropEvent or sp_DropAllEvents system procedures. These procedures remove the event for the connection they are called on. Other connections which may be using the event will not be effected. The sp_DropEvent procedure drops a specific event name for the client, whereas the sp_DropAllEvents procedure removes all events for the client. 

The sp_DropEvent system procedure takes two parameters. First the event name up to 200 characters. Next the options which is reserved for future use, pass zero for this parameter. The example removes the event MyEvent.

EXECUTE PROCEDURE sp_DropEvent('MyEvent', 0)

The sp_DropAllEvents procedure will remove all events for the connection. It only requires a single parameter called options, which is reserved for future use, pass zero for this parameter.


Using Events

There are many ways to use events from your application. One method is to use one of the wait system procedures at regular intervals. If an event has been signaled your application can react appropriately. Once an event is registered for a connection, event signals will be queued until requested. However, the application will be busy until the wait call is completed. If items have been queued then the call will return immediately otherwise it will wait until a signal is sent or until the timeout has been reached. 

Another method is to create a separate thread and connection to wait for the events. You will need to create another connection for this thread since it will be used to wait for events and it will be unable to process other operations. Since events are connection specific, you will need to create the event on this new connection using the same path as all the other clients to ensure that the application is notified whenever the event is signaled. Example code demonstrating this technique will be available shortly.


Events are a powerful mechanism for alerting clients of changes to the data. Clients can listen for events by following a simple process of creating an event and waiting for the event. The events are signaled using stored procedures or triggers and all clients who are waiting for an event will be notified. Events are specific to a connection and may require a dedicated connection to provide a prompt response.