Event alerts are database objects that are used to signal events in solidDB® databases. Together with stored procedures, the events can be used for automating administrative. You can make your application use event alerts instead of polling, which uses more resources.
The events mechanism is based on one connection waiting on an event until another connection posts that event. More than one connection may wait on the same event. If multiple connections wait on the same event, all waiting connections are notified when the event is posted. A connection may also wait on multiple events, in which case it will be notified when any of those events are posted.
There are two types of events:
▪User-defined events
User-defined events can only be used within stored procedures.
User-defined event objects are created with the CREATE EVENT statement and removed with the DROP EVENT statement.
The CREATE EVENT defines the event name and a set of parameters.
The name can be any user-specified alphanumeric string.
Event parameters must be local variables or parameters in the stored procedure where the event is triggered.
▪System events
System events can be used within stored procedures; additionally, you can also wait on a system event outside a stored procedure using the ADMIN EVENT command.
The system event names and parameters are described in System events.
When an application calls a stored procedure that waits for a specific event to happen, the application is blocked until the event is posted and received. In multi-threaded environments, separate threads and connections can be used to access the database during the event wait. All clients that are waiting for the posted event will receive the event.
Events are always posted (sent) inside stored procedures using the POST EVENT stored procedure statement. User events are also received inside stored procedures.
Each connection has its own event queue. The events to be collected in the event queue are specified (registered) with the REGISTER EVENT stored procedure statement and removed from the queue with the UNREGISTER EVENT statement.
Procedures are made to wait for an event to happen with the WAIT EVENT stored procedure statement:
wait_event_statement::=WAITEVENT [event_specification...] ENDWAITevent_specification::=WHEN event_name [(parameters)] BEGIN statementsENDEVENT
You may wait on either system-defined events or user-defined events.
Tip: With system events, you may also wait on an event without using a stored procedure by using the ADMIN EVENT statement. However, you cannot post events using ADMIN EVENT.
If you want to stop the stored procedure waiting for an event, you can use ODBC function SQLCancel() called from a separate thread in the client application. This function cancels executing statements. Alternatively, you can create a specific user event and send it. The waiting stored procedure must be modified to wait for this additional event. The client application recognizes this event and exits the waiting loop.
Access rights and events
The creator of an event or the database administrator can grant and revoke access rights on that event. Access rights can be granted to users and roles. If a user has “SELECT” access right on an event, then the user has the right to wait on that event. If a user has the INSERT access right on an event, then the user may post that event.