Solved

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

Posted on 2006-11-29
6
797 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 24

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

18 Experts available now in Live!

Get 1:1 Help Now