I haven’t used database events before and I have a situation that at
the first day of every month an inventory check has to been done which
will check the quantity against a minimum-safety quantity and if it’s
less than the safety quantity, it has to fire a notification, or later
maybe order automatically

So I need an event driven solution and I was wondering if database
events would fit this scenario in the following sense:

Month changes and event is fired
Event handler is called which makes the check by firing a db procedure
In result someone has to get the notification, who would that be, an
application? or can it fire an email?

nikosv wrote:

Ingres does not presently have the means to watch the clock.

That is the way events get handled, yes.

Once you can fire a database procedure it can do anything a database
procedure can do using SQL alone, but nothing else. At present there is
no way to send an email from a DBP, although you can get it to send a
message to a listening program that can send the email.

I've run into your kind of situation many times and I think I ought to
discourage you from this approach, even if you could set up a timer
based DBP. In the end I've always ended up writing a cron job (or an at
job, or an Autosys job) to do it.

A wider point that I'd make is that rules and procedures that "do"
things, like updating the database or sending emails, prove to be very
difficult to work with. Everyone becomes wary of the database and of
mysterious, magical, unexpected things happening as side-effects. I
have come to the conclusion that rules and procedures are brilliant for
enforcing database integrity rules of various kinds, and maybe
replication tasks, but easily become a nightmare if you try to use them
for much more. (I say that after having once worked on a system with
over 3,000 rules.)

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.

can you elaborate more on the usage of events? In what cases could I
use an event?
I guess that an event is application related rather than database
related (i.e a rule), in the following sense:
app-->event-->db proc-->notification-->app
(notice that I use the 'app' term since it's been voted word of
2010!
http://www.i-programmer.info/news/82...d-of-3010.html
)

If the inventory check would happen in realtime with the use of an
update rule on the table it would make more sense than having to do
the check every month.

Hi Niko,

In that case a database RFP would detect the update situation and raise an appropriately named event. The event may need some text to clarify exactly what was happening.

You then need a separate process...typically an ESQL-C program which is *already* connected to the database and has registered to receive the event.

On reciept of the event that program can determine on the basis of the event name and event text what the hell it is supposed to do. Start your inventory program for example although as Roy said a cron based approach is probably the better option there.

One thing that I use events for is allowing specific non-priviliged users to flag a request to the ingres user. The ingres user can then perform the task requested. For example I have a case where the user needs to run a very significant job on a database, its best done with no one else connected to the database. The users are supposed to disconnect...but theres always some clown who won't. So they raise an event, I trap the event and then execute an IMA job to remove all sessions connected to that database and then contact the user to let them know its safe to proceed.

Martin Bowes

-----Original Message-----
From: nikosv [mailto:nikosv01 (AT) gmail (DOT) com]
Sent: 20 January 2011 10:43
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Database events


can you elaborate more on the usage of events? In what cases could I
use an event?
I guess that an event is application related rather than database
related (i.e a rule), in the following sense:
app-->event-->db proc-->notification-->app
(notice that I use the 'app' term since it's been voted word of
2010!
http://www.i-programmer.info/news/82...d-of-3010.html
)

If the inventory check would happen in realtime with the use of an
update rule on the table it would make more sense than having to do
the check every month.
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi Niko.

In summary a database event is a way of notifying an interested externalprogram that something has happened. Take for an example a simple SMS messaging system.

On insert of a new row into a message table you want the message to be sent by SMS to a mobile phone.

Two solutions:

1/ batch - every X minutes (crontab)

A batch program reads the table, transmits the "new" messages and marks them as sent. Then it exits.

2/ real-time

a) You create 2 x DB events (NewMessage and PleaseExit) in the database..
b) You create a DBrule on insert to message table to raise dbevent NewMessage

c) You have an improved batch program (again started by crontab at start of day) but this time program continues running : -

register for dbevent NewMessage and PleaseExit.

while (1 == 1)
do
transmit all "new" messages and mark them as sent.
wait for DBEVENT NewMessage or PleaseExit.
if DBEvent is PleaseExit then exitloop.
done


With this flow, every time you insert a new row onto the database, the DB rule will fires the db event NewMessage. The batch program (if you stated it ) will wakeup, find the event name is NewMessage and loop round to send all the new messages... If you want to shut down the program (at end ofday) - you raise the dbevent PleaseExit and it will drop out...



The main design point about events is they should be used as wakeup callsto external programs (that are running and registered to receive them). Please do not use events to communicate information (there is a text field - but events can be lost if no one is listening and then if you make a rollback... !!).

Enjoy

Gary

Nice summary, Gary

I would like to add that database events are not queued and left around
if there is no one to listen to them. That is why often you will see
some additional architectural elements for queue handling along with the
DBEvents. For example, your db procedure (in this example) might insert
the "message" plus some additional information into a "queue" table and
raise a dbevent indicating something is available. That way you will
not lose information if the notification daemon process is not running
at the time. An alternative is to have some type of "processed" flag in
the original data. That can have a performance or locking penalty
however.


--
daryl.monge (AT) ingres (DOT) com
------------------------------------------------------------------------
daryl.monge (AT) ingres (DOT) com's Profile: http://community.ingres.com/forum/member.php?userid=736
View this thread: http://community.ingres.com/forum/sh...ad.php?t=12940