ADO: "Auto-Inform" other users about data changes (CursorType := ctDynamic)

Hi,

I'm developing a multi-user system based on a Delphi 7 client application and a central MS SQL 2005 server database.
Connection between the application and the database is estabished via ADO.

My wish is: data changes of one user should be visible to other users immediatley.

In this context the Delphi Online help suggests to set the ADODataSet's property CursorType := ctDynamic.
ctDynamic provides a dynamic cursor which supposedly shows data changes to other users (according to the Delphi Online Help).

My problem: It is just not working. I can change any data on Client X, but none of these changes are visible on the other clients (until I refresh the dataset manually).

I then did some researches here on EE just to find out that ADO is not supporting this "auto-inform" feature.

If this is correctt, I have two questions:

1. What does the ctDynamic setting really do?
2. Which approach/workaround would you take to inform other clients about datachanges?

Thanks, Ingmar
softwareaAsked:
Who is Participating?
 
calinutzConnect With a Mentor Commented:
Create a trigger on the tables that you want to watch and somehow... make a timer and make it watch for the result of the trigger
Perhaps the trigger should insert a value in a table: "triggertable" for example (that usually is empty) and on the other hand the timer should check if the recordcount of the named triggertable to see if it's 0, if not ShowMessage('A record was changed. Please refresh datagrid'), and then empty the triggertable

Just a thought

regards
0
 
softwareaAuthor Commented:
Hi calinutz,

yes, that is a good idea. Thank you very much!
Actually I had a similar idea this night ;-)

The interesting question is: what exactly do I store in the triggertable and when exactly am I allowed to empty the triggertable?

Let's say I have 10 users. All of them must be informed about data changes! But:
 
1. Not all of my users have access to the same data. That means: some data changes might have occured in their scope, others are invisible, forbidden or not relevant to them.  So, does that mean that the triggertable must also save the information (e.g. tablename, primary key of the changed record) about which records have been changed, added or modfied? And the timer/watch on the client must compare all primary keys in the trigger table with the primary keys on the client in order to find out what is new, changed or deleted (ups, deletion is even worse...how shall I compare tom something that is not existent any more?). Puh...
2. The timers on the clients will not fire at the same time, so what does that mean for emptying the triggertable. Maybe like this: timers are fired every 30 sec. So, after a maximimum of 1 minute all timers should have fired on the clients. The relevant values in the trigger table can be deleted after this one minute. Am I correct?

Wow, that looks like a lot of work and performance issues. I really wonder: I can not be the only person on that planet with this request. How do professional systems handle this? I can not believe that they are working with timers and triggers ;-)

Ingmar
0
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
  Hi!

One solution to this is to use MIDAS functionality where you would have a server in the middle of the clients and the database
where you inform other clients to refresh their ClientDatasets when other clients make any changes to the data by raising
a flagg which triggers the dataset to refresh.
This is easy to implement with the MIDAS/DataSnap technology and you can download from Torry.net  a TCPADOServerClient components ( http://www.torry.net/authorsmore.php?id=6567  )
which is Freeware and a MIDAS alternitive and extend its functionality if you don't want to spend money on a MIDAS license.
Using a three tier architecture would be the best solution in this case if you want to have the abilty to maintain the
code easily and ability to switch to another database without any hard work.

Regards,
  Tomas Helgi

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Eddie ShipmanConnect With a Mentor All-around developerCommented:
Another way, and I've seen it in action before, is to use MSMQ messages to do that.
We wrote triggers to call SP's to create MSMQ messages and had a MSMQ Message
listener in our application.

I wasn't part of that project but know it worked and well.

This article describes how to create an MSMQ message from a SP:
http://www.developerfusion.co.uk/show/2151/

A BDN article on using MSMQ in Delphi:
http://bdn.borland.com/article/27363

Import the MSMQ Type Lib for SYSTEM32\mqoa.dll to be able to use MSMQ Objects.


0
 
softwareaAuthor Commented:
Wow, thank you all so much!

The ideas of using MIDAS (TomasHelgi) or MSMQ (EddieShipman) sound very professional. Both looks a little bit too complicated for me at the moment. Unfortunately I'm not such an experienced programmer. But I will do some more researches and maybe...

So, at least I have three interesting ideas now! And thus something to think about.

Points to all of you, if you do not mind. Thank you again!
0
 
Eddie ShipmanAll-around developerCommented:
Another thing would be just to creae messages inside your own application inside the events of the datasets.
We do that in our own inherited dataset controls. We broadcast a message whenever certain events happen.
Then all our other dataset's know because they have message listeners built-in.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.