FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: manoranjan_sahu AT agilent.com [mailto:manoranjan_sahu AT agilent.com]
> Sent: June 9, 2003 12:14 PM
>
> From my unix system, my application needs to be notified of 
> any change(insert, update, delete) to a table (say T1). Does 
> freetds has any api I can use to do this task?

This question crops up from time to time.  Basically, the answer is No.
There's nothing built-in, and there's no efficient way to create one.  

The first, completely unfixable (by you) problem is that there's no kind of
"tell me when this table changes" anywhere in SQL or the TDS protocol, nor
any way afaik to trick the system into sending you a message when a table's
state changes.  The TDS protocol is half-duplex, meaning you only hear from
the server when you've asked for something; you'll never receive a
spontaneous message.  And the one thing you'd like most to ask, but can't,
is "tell me when this table changes".  

You're therefore reduced to polling.  Polling is not only inefficient; it
also scales badly.  Because there's no IPC between server processes (spids)
and no analog to wall(1), each process is forced to do its own polling.  For
10 users, that's no problem.  For 10,000, it's infeasible.  

I'm oversimplifying a little bit: Sybase offers a facility called
"registered procedures" that do what you want.  It's part of OpenServer,
which FreeTDS doesn't support.  Both vendors also offer hooks into the
server's OS, where you could work some IPC and/or broadcast magic.  

--jkl


-----------------------------------------
The information contained in this transmission may contain privileged and 
confidential information and is intended only for the use of the person(s) 
named above. If you are not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, any 
review, dissemination, distribution or duplication of this communication is 
strictly prohibited. If you are not the intended recipient, please contact 
the sender immediately by reply e-mail and destroy all copies of the original 
message. Please note that we do not accept account orders and/or instructions 
by e-mail, and therefore will not be responsible for carrying out such orders 
and/or instructions.