UDF that sends EMail - Win32 only
Status: Beta
Brought to you by:
igmac
Menu
▾
▴
Hi.
I needed the ability to generate an EMail on certain DB conditions (ie, a triggered event) in InterBase/Firebird. Couldn't find anything that did it as a UDF, so tackled one myself.
You specify SMTP host, to, from, subject and body. Nice and simple. The library creates a thread to handle the email transmission asynchronously, as it's possible that it will block for some time. It uses a mutex (critsection actually) to ensure that it's threadsafe (from both internal and external threads).
Disadvantages:
1. It returns a result value (currently hardcoded to Integer Zero).
This has no meaning. Seeing as the UDF returns to the caller
asap, it does not yet know if the send will succeed or fail.
Think UDP.
2. There is no way to determine if the send was successful or not,
other than the brute force way of including yourself in the To:
list, and checking that you got your copy, and assume a 99%
chance so did everyone else. Alternatively, I could go and
have the UDF connect back to the server and write the send
results to another table. That strikes me as nasty and untidy
and dependant.
3. Executing the function N hundred/thousand/million times will
undoubtedly cause a problem, as Win32 will rapidly run out of
threads. You get one thread per email send request.
None of these pose a problem for me right now, as I send a very
occasional email. Think exception not rule.
Thinking of 'the right way' leads me to consider dropping all that clever thread stuff, and allow the function to block, thereby returning a meaningful and valuable result.
That however leads to a possibly very slow SQL statement execution if it's triggered. And the poor sucker that, for example, extracts 5 items from inventory and causes the Available + OnOrder quantity to fall below MinLevel, which fires a trigger that creates an automatic email order to the supplier, gets to wait while we have a TCP/IP SMTP negotiation take place.
For the above nasty case to be true, I'm assuming that all triggers run under the same thread as the original SQL query. In other words, if *any* trigger, however remotely linked, before or after, blocks, then the original SQL query waits for it to complete.
I'm darn sure that's gotta be the way IB is written, but can somebody in this list confirm that for me?
However, I suspect that if I instead insert my proposed email in, say, the table EMail_Out, and instead of using an 'after insert' trigger, I 'post_event', then the server *will* create a new thread to asynchronously handle the event notification.
Which solves the first problem.
The next problem is that a 'post_event' can't cause a trigger to fire... So I would at a minimum have to connect back into the server from the UDF, register interest in the appropriate event. Now of course, the UDF knows there is email, and I can do a normal sql select, send, sql update with results, etc.
That still strikes me as nasty. The UDF is dependant on the event name (that's ok I believe), but I'm uncomfortable with the UDF being dependant on a table name and structure in order to send and report on email status.
What appeals to me as a simple elegant solution, is to extend the server's trigger mechanism to cater for not only tables, but also events.
Comments?
I've stuck the UDF source under http://sourceforge.net/projects/fireudflib/
There's a duplicate copy of this email in the FireUDFLib :: Public Forums :: Open Discussion forum, so feel free to reply wherever you're comfortable.
Regards,
Ian Macintosh
I posted that message in the firebird-devel mailing list btw. Thought it appropriate to put a copy here.