Solved

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

Posted on 2006-11-29
6
799 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:softwarea
6 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 100 total points
ID: 18041660
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
 

Author Comment

by:softwarea
ID: 18043468
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
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 100 total points
ID: 18044064
  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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 26

Assisted Solution

by:EddieShipman
EddieShipman earned 100 total points
ID: 18046959
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
 

Author Comment

by:softwarea
ID: 18051914
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18053824
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now