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 actions. 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 can 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 can also wait on multiple events, in which case the connection is notified when any of those events are posted.
There are two types of events:
▪ User-defined events
User-defined events can be used only within stored procedures.
User-defined event objects are created with the CREATE EVENT statement, see CREATE EVENT, and removed with the DROP EVENT statement, see DROP EVENT.
The CREATE EVENT statement defines the event name and a set of parameters.
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.
The system event names and parameters are described in SQL: 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 receive the event.
Events are always posted (sent) inside stored procedures by using the POST EVENT clause in the CREATE PROCEDURE statement, see CREATE PROCEDURE. 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 clause in the CREATE PROCEDURE statement and removed from the queue with the UNREGISTER EVENT clause.
Procedures are made to wait for an event to happen with the WAIT EVENT clause in the CREATE PROCEDURE statement:
You can wait on either system-defined events or user-defined events.
Note With system events, you can also wait on an event without using a stored procedure by using the ADMIN EVENT statement, see ADMIN EVENT. However, you cannot post events by using the ADMIN EVENT statement.
If you want to stop the stored procedure waiting for an event, you can use the ODBC function SQLCancel(), Call the SQLCancel() function from a separate thread in the client application to cancel 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 the 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 can post that event.