Uses of Events - Firebird

Firebird events provide a signaling mechanism by which stored procedures and triggers can pass an alert to client applications when other applications commit changes to data. The client applications are set up to “listen” for specific events through a server -to-client interface, avoiding the system cost of polling for changes.

Client subsystems that poll the server for news of database state changes are not rare in the world of relational database subsystems. However, Firebird’s event notification model does not expend network or CPU resources on polling or timers. It is a subsystem of the server that is maintained on and by the server. A client “registers interest” in an event and, when it is ready, it signals that it is waiting for notification.

When a transaction commits, notifications of any events that occurred are posted to those listening client applications that are waiting. The client application can then respond to the event in some manner.

Uses for Events Notification

The Firebird events notification provisions can meet numerous application requirements that call for the means to respond rapidly to changes in database state performed by others using the system. The techniques can be used in combination with external telecoms, process control, scheduling, and messaging technologies to automate time-critical and state-critical response flows.

The possibilities are limitless in terms of scope and application. Some examples are

  • Background data replication services are prompted to expect a new item.
  • A ticketing application uses the scheme as a signal to refresh open datasets in other booking offices whenever a seat allocation or a timetable change happens.
  • An inventory application flashes a “stock out” message to the purchasing department when an inventory item goes under its minimum stocking level.
  • Retail chains are notified when a new price list is loaded.
  • A monitoring device on processing machinery signals the store when the levels of materials are running low.

Elements of the Mechanism

The originators of events are database state -changing operations —successful INSERTs, UPDATEs, and DELETEs. Event signaling happens in triggers and stored procedures through the medium of the PSQL statement POST_EVENT.

POST _EVENT is but one piece of the mechanism —in isolation, it does nothing. It is merely a call sign that applications listen for. It transports no information about the database event it signals; it is up to the application to provide its own context for each event on the basis of its call sign.

The event mechanism itself consists of several interacting server-side and application pieces.

Server-Side Elements

The server-side elements are

  • One or more triggers or stored procedures that invoke POST_EVENT statements
  • An internal event table—the destination of the POST_EVENT calls—that maintains a list of events posted to it by procedures and triggers, for the duration of the transactions in which the events occur
  • An internal event manager subsystem that maintains a list of listeners and waiters, and acts as “traffic cop” to marshal and match up events with listeners

Application Elements

On the application side, the mechanism needs

  • An application that is capable of registering an interest in events
  • Other applications that actually perform the DML operations that the listening application is interested in

Of course, the listening application also needs its own mechanism for responding to events.

Interface Elements

Events transport from server to client uses a different pair of ports than that used for the main client/server channel (usually port 3050). The server and the client library find a random pair of ports to use for event traffic.

The software element is a routine in the client layer known as an event callback function. It is client-based code that is called by the server to inform the client of events as soon as the transaction that posted an awaited event has committed. For embedded applications, the pre-compiler, gpre, generates the code for this callback function. For dynamic applications that want to listen synchronously (refer to the nextsection), as ESQL applications do, the callback function lives in the client library. Dynamic applications can—and usually do —listen asynchronously (see the sections “Asynchronous Signaling” and “Asynchronous Listening”). For this, they need to supply a custom callback function known as an asynchronous trap (AST).

Synchronous Listening

Figure illustrates the bare-bones event model that is implemented in the ESQL language for embedded applications with the EVENT INIT and EVENT WAIT statements. Dynamic SQL has no equivalent SQL statements. For dynamic SQL applications, the same synchronous event model is implemented in the API through the isc _wait _for _event( ) function.

An ESQL application uses EVENT INIT to signal that it is listening for an event and EVENT WAIT to await the notification. It listens for notifications through an auxiliary port-to-port channel on the network, using the main connection channel’s database handle. Once EVENT WAIT is called, execution of the client application is suspended until the event notification arrives.

Synchronous signaling

Synchronous signaling

A client out in the network posts an update to a row in MYTABLE. It is received by the server and executed. During the AFTER UPDATE phase, a trigger posts an event named big_event, to notify the event manager that it has completed the update

The event manager adds the event to its list of events. At this point, the update is uncommitted and the event manager does nothing further. In its list of listeners, it sees that process X is listening for that event. Process X will now wait until one or more events named big_event are committed.

On the COMMIT, the event manager sends process X, and any other listeners waiting for big_event, a notification that big_event happened. Even if the transaction caused big_event to be posted many times, the waiting client gets a single notification.

If no processes have registered an interest in big_event, the event manager simply ignores the POST _EVENT call. Any processes currently signaling EVENT WAIT for big_event receive the notification immediately.If any processes have registered an interest in big_event, but are not waiting, the event manager retains the event until they either signal wait or cancel their interest. Once the interested applications have lost interest, big_eventwill be erased from the table.

An application can wait on up to 15 events per EVENT INIT request. It can spread events over multiple EVENT INIT requests, but, with synchronous events, it can wait on the handle of only one EVENT INIT request at a time.

Asynchronous Signaling

Synchronous signaling has its limitations. In particular, it requires the application to wait an indefinite time to get notification. This limiting model was extended to support asynchronous signaling.

Under this model, an application process still registers interest and waits and listens, but it is able to continue its own execution and make database requests while awaiting the notifications. The application has its own, client-side event queue to manage. Figure depicts the elements of the setup for asynchronous listening.

Asynchronous events mechanism

Asynchronous events mechanism

A stock-brokering application, for example, requires constant access to the Stocks database to provide brokers with real-time information as prices fluctuate, but it also needs to watch particular stocks continuously and trigger off the appropriate Buy or Sell procedure when certain events occur.

DSQL applications use API calls to implement events listening, both synchronous and asynchronous. There is no language equivalent in DSQL, and setting up the application interface from raw ingredients is rather complex.

An application registers interest in events by way of an events parameter buffer (EPB) that is populated by a call to the isc_event_block( ) function. One EPB can register up to 15 events, specifying different EPBs and event_ list buffers for each call. The named events need to match (case sensitively) the events that will be posted. Applications that need to respond to more than 15 events can make multiple calls to isc _event _block( ).

Synchronous Listening via the API

Setting up synchronous listening through the API is similar to what is needed for asynchronous signaling, except that it calls the isc _wait_for _event( ) function rather than isc_que_events( ). As with the ESQL equivalent, EVENT WAIT, program execution is suspended during the wait. The isc_wait_for_event( ) function listens for the wake-up notification that will occur when the server “pings” the callback function.

Asynchronous Listening

Before you can use the API continuous-signaling function isc_que_events( ), you need a callback function at the client, for the server to call when an event is posted. The term for this kind of function is an asynchronous trap, or AST.

The AST Function

The AST function has to provide some form of global flag to notify the application when the server has called it. It has to process the server’s event list into buffers that the application can access for its own management of the event queue. It must take three arguments:

  • A copy of the list of posted events
  • The length of the events_list buffer
  • A pointer to the events_list buffer

The isc_event _block( ) function accepts into its isc_callback parameter a pointer to the AST function and, into its event_function_arg parameter, a pointer to the first argument of the AST. This argument generally accepts event counts as they change.

When the application calls the function isc_que_events( ) to signal events that it wants to wait for, it passes a pointer to the AST callback function, along with a list. A single call to isc_que_events( ) can manage up to 15 events. The application calls the isc_event_counts( ) function to determine which event occurred.

Multiple isc_que_events( ) calls can be operating simultaneously in a single client/ server process. Applications switch off waiting with calls to isc _cancel _events( ).

Component Interfaces

Fortunately for nearly all of us, the pieces for implementing events in client applications have been encapsulated in classes and components for most of the application development tools that support Firebird. Comprising the AST, the encapsulated API isc_event* function calls and event parameter blocks and the client-side management of the event buffers, they are usually referred to as event alerters. The term is somewhat confusing in list forums and literature, since the triggers and stored procedures that post the POST _EVENT calls are also often referred to generically as “event alerters.”


To use an event alerter in a stored procedure or trigger, use the following syntax pattern:

POST_EVENT <event-name>;

The parameter, event_name, can be either a quoted literal or a string variable. It is case sensitive and it can begin with a numeral. Event names are restricted to 15 characters.

When the procedure is executed, this statement notifies the event manager, which stores the alert in the event table. On commit, the event manager alerts applications waiting for the named event. For example, the following statement posts an event named new _order:

POST_EVENT 'new_order';

Alternatively, using a variable for the event name allows a statement to post different events, according to the current value of the string variable (e.g., event_name).

POST_EVENT event_name;

A trigger or stored procedure that posts an event is sometimes called an event alerter. The following script creates a trigger that posts an event to the event manager whenever any application inserts data in a table:

POST_EVENT 'new_order';

Trigger or Procedure?

POST_EVENT is available to both triggers and stored procedures, so how do you decide which is the better place to post events?

The rule of thumb is to use triggers when applications need know about row-level events—either single row or multiple row, depending on the scope of the transactions— and procedures to signal those events whose impact on applications is broader.

This is only a guideline —often, procedures have row-level scope and yet the interested client wants to know about a particular operation when it happens to be performed by that procedure. A POST_EVENT from a trigger in this case would be unable to tell the listener anything about the context of the event, and the designer might wish to use the procedure -based event call-sign to ascertain which kind of caller was responsible for the work. Alternatively, the designer might wish to place the event in a trigger to ensure that a particular DML action is consistently signaled, regardless of the context in which it is executed.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics