Solved

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

Posted on 2006-11-29
6
798 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

912 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

16 Experts available now in Live!

Get 1:1 Help Now