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...
Can SQL Server accomplish this? If yes, which edition?
Thank you
--
Joseph I. Ceasar
CLS Computer SolutionsAn aspect of the new ADO.NET v2.0 is "Query Notifications" and this is fully
implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details on
msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dnvs
05/html/querynotification.asp
There is a good CTP (effectively Beta-4) to get early experience on at MS
site msdn.microsoft.com/SQL/2005/default.aspx
HTH
Dick
"Joseph I. Ceasar" wrote:
> 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...
> Can SQL Server accomplish this? If yes, which edition?
> Thank you
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>
>|||Very interesting.....
I check out that document. It does say that this technique should not be
used for data that changes very frequently. Does anyone have any idea of
what technique to use for data that does change frequently?
"Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
> An aspect of the new ADO.NET v2.0 is "Query Notifications" and this is
> fully
> implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details
> on
> msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dn
vs05/html/querynotification.asp
> There is a good CTP (effectively Beta-4) to get early experience on at MS
> site msdn.microsoft.com/SQL/2005/default.aspx
> HTH
> Dick
> "Joseph I. Ceasar" wrote:
>|||You can't eat the cake and have it. If you want to be notified, it would be
hard to write your own
with lower resource consumption than Query Notifications (based on the smart
technique that QN
uses). If that is to steep for you (too frequent data changes, use a polling
technique. A polling
technique can show stale data, which is why it *can* be less resource intens
ive in a highly changing
environment.
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:O%23c7aoDxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Very interesting.....
> I check out that document. It does say that this technique should not be
used for data that
> changes very frequently. Does anyone have any idea of what technique to u
se for data that does
> change frequently?
> "Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
> news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
>
Showing posts with label delphi. Show all posts
Showing posts with label delphi. Show all posts
Sunday, February 12, 2012
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
>
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
>
Subscribe to:
Posts (Atom)