Client app needs to know when data has changed

Hi all,
I am developing a multi user application using Delphi. Currently I am using
a database engine that is terrible. I am considering switching over to SQL
Server. One of the features that I could really use is for the client
applications to be notified when data changed. That is, if one client adds,
deletes or modifies a record, I need the other clients to be notified of
this. Currently, when a client makes a change, I alter a record in a
"changes" table. The other clients poll this table every 10 seconds to
determine if a change has been made. I want to get away from this
poling...
--
Joseph I. Ceasar
CLS Computer SolutionsJoseph,
Consider using a trigger?
HTH
Jerry
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Look into SQL Notification Services. Sounds like this is what you need.
ML|||Do you really need that? It's been my experience that detecting changes
made by other users when a modification is submitted is sufficient.
When the user presses the OK button, a stored procedure is called to commit
the change. If the row to be updated has changed, then the stored procedure
indicates in its return value that it cannot complete the update and why,
and returns the current values in output parameters. In this way you can
inform the user what changed, possibly by whom (of course, in order to do
that you need to record who made the last change within each row). You also
have access to the user's changes so no information is lost in the process.
I load a tool tip with the user's changes when they're different from what's
been returned by the stored procedure. That way, instead of simply
discarding the user's changes, they're available to the user so that he can
decide to either discard his changes, or rekey and apply them. In short,
the user performs the conflict resolution task instead of producing some
exception report and fixing the problem later.
Polling for changes every 10 seconds or registering an event handler in some
middle-tier component does not eliminate the possibility that a change could
be made by another user between polls or while an earlier event is being
processed. The exception handling mechanism described above must still
exist. I think that it would be really annoying, if not dangerous, for a
user filling out a form to be interrupted and/or to have his changes
overridden because some yahoo down the hall updated the same row. Waiting
until the form is filled out, and giving the user the ability to resolve any
conflicts that occur just makes more sense to me.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||a trigger and . . .
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Joseph,
> Consider using a trigger?
> HTH
> Jerry
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||another component(s). :-) Depends on the situation and the requirments.
"JT" <someone@.microsoft.com> wrote in message
news:uvk6b16wFHA.2652@.TK2MSFTNGP14.phx.gbl...
>a trigger and . . .
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
>|||When you say that your database engine is terrible, does this mean that you
are wanting to migrate from Oracle or MySQL to SQL Server?
Also, explain a little more about the application and how it would use the
information that data has been changed by another user. If your client is
using connected ADO recordsets, then perhaps a dynamic cursor?
http://msdn.microsoft.com/library/d...perty_oledb.asp
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Normally I would agree with this procedure. The specific case that I am
dealing with needs the info "Pushed" to the client. It's a scheduling
application. If a user creates an appointment, I need all the other users
to see that new appointment. Informing a user that a certain time-block is
taken is of no use, since I allow double booking. There are only 2
solutions here. Poling or being notified that something changed.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Do you really need that? It's been my experience that detecting changes
> made by other users when a modification is submitted is sufficient.
> When the user presses the OK button, a stored procedure is called to
> commit the change. If the row to be updated has changed, then the stored
> procedure indicates in its return value that it cannot complete the update
> and why, and returns the current values in output parameters. In this way
> you can inform the user what changed, possibly by whom (of course, in
> order to do that you need to record who made the last change within each
> row). You also have access to the user's changes so no information is
> lost in the process. I load a tool tip with the user's changes when
> they're different from what's been returned by the stored procedure. That
> way, instead of simply discarding the user's changes, they're available to
> the user so that he can decide to either discard his changes, or rekey and
> apply them. In short, the user performs the conflict resolution task
> instead of producing some exception report and fixing the problem later.
> Polling for changes every 10 seconds or registering an event handler in
> some middle-tier component does not eliminate the possibility that a
> change could be made by another user between polls or while an earlier
> event is being processed. The exception handling mechanism described
> above must still exist. I think that it would be really annoying, if not
> dangerous, for a user filling out a form to be interrupted and/or to have
> his changes overridden because some yahoo down the hall updated the same
> row. Waiting until the form is filled out, and giving the user the
> ability to resolve any conflicts that occur just makes more sense to me.
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||I've never used Notification Services, but that sounds like a place to
start.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:%23KrUB86wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Normally I would agree with this procedure. The specific case that I am
> dealing with needs the info "Pushed" to the client. It's a scheduling
> application. If a user creates an appointment, I need all the other users
> to see that new appointment. Informing a user that a certain time-block
> is taken is of no use, since I allow double booking. There are only 2
> solutions here. Poling or being notified that something changed.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server 2005 will have a feature called "Query Notification" (not the sam
e as Notification
Services). You can be notified immediately if a change is made that might af
fect the rows that has
been returned by your SELECT statement (including new rows that qualifies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.p
hx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am usi
ng
> a database engine that is terrible. I am considering switching over to SQ
L
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client add
s,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>