Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


500+ points - How do should i handle multi-user access to a sql-server 2k/ado/delphi program?

Posted on 2003-12-07
Medium Priority
Last Modified: 2010-04-05
Hi everyone,

I've asked around a few times, and have read a couple of books on ado, and one on sql-server 2k.

I still can't get around on how to implement a scalable, multi-user program, using client side cursors.

At the moment, i'm using SQL Queries in order to read the data from the database.

I want to use Client side cursors because its more scalable, and will be running on > 30 machines.  

Do i have to create a 3-tier software?
If a 3-tier CAN be avoided, then how should i go about it?

I've gone from Server-side cursors, to filters/locates with client side, then used sql queries, tried stored procedures, so i have some experience on how to work with all these, but...

what i need is when  user 1 is on a record, and user 2 changes the record details, when user 1 scrolls off and on the record again, i want him to see the changes.  Even more, when user 2 is changing the data, i want user 1 to be blocked from editing the details BUT with Client-Side cursors...

Any example code is greatly appreciated (either in sql or delphi/ado)...

This is verrrryyy urgent to me, so i would provide even more points if answer is right on....

Question by:nick-p
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +4

Expert Comment

ID: 9895223

What are you using for data access? Are you using plain ADO library shipping with Delphi?
If so, are you changing anything of the plain settings provided by default in the ADOConnection
component, a part from ConnectionString?

LVL 27

Expert Comment

ID: 9895297
>what i need is when  user 1 is on a record, and user 2 changes the
>record details, when user 1 scrolls off and on the record again, i
>want him to see the changes.  
this is not possible, except you refresh allways the dataset

>Even more, when user 2 is changing
>the data, i want user 1 to be blocked from editing the details BUT
>with Client-Side cursors...
also this is not possible on sql-databases
-> the rule is there, try to commit the changes,
on error -> another user was faster

well there may be a workaround
-> add a numberfield
-> before editing,
-> read this field
-> is the value the same as before,increment the field, otherwise refresh the record and increment this field
-> begin editing

meikl ;-)
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 9895377
How about using MIDAS (< D6 ) or DataSnap (D7) ?
That way you can control the flow of transactions pretty much as you like.
In the DataSetProvider there are events like OnDataRequest, AfterRowRequest and BeforeRowRequest. All Events that you could use.
Yes I think you would have to create a 3-tier (if you are going to use the DataSnap method). Database - Middleware (with the DataSetProvider) and the Client (with SocketConnection and ClientDataSet).
The SocketConnection is easiest to handle.  

  Tomas Helgi
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 9900620
I am working on similar type of app. What I did about showing modified records is (using client-side cursors)
1. Whenever USER1 changes record I call Requery() since I don't use in-place editing
2. I provided menu item Refresh that will call Requery() when user wants to see latest data changes by other users

To prevent editing Record1 when someone else is editing it, I added lock table that has record_id from the table I want to protect and userid where I store userID. What happens is, when someone wants to edit record I first go to the lock table and check if the record_id is there- if it is then it means someone else is editing the record. If it is not there, I insert it into the lock table and begin with record editing. When user is done, I delete the record_id from the locks table.

 Keep in mind that there is a chance that the record becomes locked forever if something happens while user is editing the record (i.e. for some reason I am not able to unlock the record). I solved that by deleteing all records from the lock table where user_id = currentuser when user logs out.

Expert Comment

ID: 9901667
I use a datetime field to handle this situation, on before edit I check the field hasnt changed if so I notify the user and either refresh the data or set it to a locked state(so they cant edit it), then  before post I set the current datetime on the field and post the update.

Author Comment

ID: 9908397
Thank you all for attempting to answer my question... But I havent really found anything suitable...

kretzschmar, the first one is possible with resync, not refreshing the whole dataset, but the specific record, and was wandering if you or anyone knew how to use that properly...

bogiboy can you go in more detail on how you would handle the problem if the user has a power shortage and stays logged in forever? other than that it sound like an ok idea... its just that i have to cover all aspect of failure before i release the software?

I understand that this question raises many more and could be worth more than 500 points, but i'm willing to open a second/third question for point collection... (if that is within the rules??)

Thank you so far for all your answers

Expert Comment

ID: 9924064
listenning for the moment ...

Expert Comment

ID: 9931805
>>bogiboy can you go in more detail on how you would handle the problem if the user has a power shortage and stays logged in forever?>>>>

Since this is db application and user has to login/logout, then I have a call to the cleanup procedure that gets called just before logout
SQL := 'DELETE FROM TbLocks WHERE Locked_by = ' + QuotedStr(UserID)

So, if something gets locked when system crashes, user can simply login again and logout right after that and SQL cleanup will be executed.

Also, I provided administartor user with ability to clear all locks for particular user using the same SQL statement, but I provide (messageDlg) administrator with information (to confirm) that the action should be performed only if the User is not currently logged in. That's to ensure it will not delete valid locks.

How other users will know that the record is locked by someone else? Well, in all dialogs that are used to edit records, I put invisible TLabel somewhere on the top of dialog with red font color that becomes visible only if the record is locked. "The record is currently in use by <Locked_by>". I also disable Save/OK button in that situation so the user is still able to read data.

Another thing is, I check for locking when user wants to delete the master record if someone is editing child record at the same time. That is to ensure no orphaned records will be created in delete actions.

Expert Comment

ID: 9931845
One more thing --- I use TbLocks for locking data from multiple tables. Just create a column in TbLocks (let's say Locked_Table_id) that indicates from which table Locked_Record_id is coming. This way you can lock whatever you need to.

Author Comment

ID: 9943388
Ok, that sounds reasonable... but what happens, if the user's pc crashes, and he/she doesn't bother to log in again or to turn on the pc?  

Do you know how i can implement a sort of timed trigger in sql server, that can check if the user is 'actively' logged in (not just a ghost) and if the user is not, but locks from that user exist, to remove them?

What about the rest? I understand that these are more questions based on the main one, so this question goes to 1500 points now for EACH person's answer  (no splitting)... ;-)

So, how can I properly handle client side access to the database?  

I am using sql queries at the moment, and it wont really scale well, when many pc's go on the network...

I have to close/set sql command/open an sql query fill the combobox, then go to the next query for another table do the same, etc... for all 20-25 comboboxes...( yes, i have to have that many)... this over the network brings loads of traffic, so it can't handle more than 20 pc's max...  

So that's why i want to use client side, but i don't know how to check for consistency...

I mean, a reduced traffic option would be the resync command property (or similar?) that would only check the timestamp, and if it changed since last read, it would fetch the new data...

BUT i want to use timestamp, and NOT the datetime field for more accuracy, but I can't read timestamp values from delphi ( i tried bcd, string, int, etc...) but it won't work...

??? Any ideas?

Expert Comment

ID: 9943760
>>>Ok, that sounds reasonable... but what happens, if the user's pc crashes, and he/she doesn't bother to log in again or to turn on the pc? >>>

As I said,  <<<Also, I provided administartor user with ability to clear all locks for particular user using the same SQL statement, but I provide (messageDlg) administrator with information (to confirm) that the action should be performed only if the User is not currently logged in. That's to ensure it will not delete valid locks.>>>

That doesn't have to be special Admin, give that right to all users I you can trust them that they will not use it inappropriately, or give it to the ceratain number of users. How often will pc crash? Every day? Every week? I don't think so. So far I haven't had any complaints from users about this mechanism.

About timestamp: If you use MSSQL how about using stored procedure that will return records that have been changed? That way you don't have to wory about Delphi timestamp problems that you have. The best way to load combos with data that were changed after timestamp value is to create stored procedures (yes, 20-25 of them) with parameters (if you need) and they will execute faster then sending same set of queries to the server over and over.

In my case, although we can argue about principles, most of database read/write operations are done by views/stored procs because the server creates plan for them once and there is no need for any special steps like in case when you issue SQL statement.
Good practice (that I read about and use now) is : do not let users access tables directly, but use views and/or stored procedures. If you change db structure (tables) you can still just update view and (if view returns the same columns as before the change) there is no need to change application code. And it works faster.
LVL 27

Expert Comment

ID: 9943997
i never needed such complicated control
about user access to a database,
even i've coded tons of database-apps.

just use a sql-based databaseserver,
and if a user modifies tables,
just start a transaction and try
to commit the transaction just after finished
the user modification, if it fails,
well user go back to start and try again
(you will see, this happens really rarely),
whereas any other "solution" will allways have
unknown fallbacks)

ensure that the transaction time is as short as possible,
plan your database-structure best as possible
- use contstraints and foreignkeys
(not only for lesser coding on your frontend,
also to ensure the data-consistenceon serverside)

about your traffic issue, if you want to display
this info you must transport it through the net,
and i would only think about that, if the network
becomes really a bottleneck. i guess you
don't want to transfer the whole database to the client
-> use small datasets, just enough for the users work.

in conclusion:
my advice would be
- let the database ensure about the consistence
- let the forntend display what is needed and not more

just my two cents

meikl ;-)

Expert Comment

ID: 9944295

I agree with kretzschmar.

If you really need to do the way you said, you can use batch updates. Here it depends on your strategy, for example you can apply the batch after a row/or multiple rows have been posted to your current dataset.

Then, apply batch update; if you have an error, you can reload the dataset.

If you need to let the user keep the modifications, before applying the batch, you can make a clone of the recordset. Having these 2 recordsets you can handle them as you like. (In ADO.NET is much easier to aproach this task, if you wait for Delphi 8 it will be much easier)



Author Comment

ID: 10166075
This program will not have an administrator, so it must work flawlessly by it self.

What i would like to know bottom line is...

User 1 and User 2 open the same record.  They both see the same values.  
User 1 changes the name field for example from 'nick' to 'nick1'
User 1 updates the database and leaves this record.
User 2 does not see any changes.
User 2 now decides to change the name field from 'nick' (although it already is 'nick1') to 'nick2'
User 2 tries to update the database

Consider the following facts though:
1. The program is client-side
2. They must both be able to access the same record at the same time
3. When anyone updates a record, they way i handle it at the moment is that i get all values from the edit boxes and assign them to the current record, even if they havent changed.
4. If i check the running value in the database before i update, and compare it with the edit box value, and if its not the same then do something, this wouldn't work if a single user was trying to edit a record, because say he wants to change 'nick' to 'nick1', the prog would check the edit box saying 'nick1' and the database saying 'nick' and wouldn't update the record.

Thank you in advance...  I am willing to provide with 4000 points to the person with the most acceptable answer - i believe this is fair...
Assisted answers will get 1000 points each (i'll open other topics for collection of extra points)


Author Comment

ID: 10166090
i forgot to say:

after user 2 tries to update the databse... How should i handle the situation more properly... What are all my options?
LVL 27

Expert Comment

ID: 10166442
i tested the bahaviour of the situation you expalined above with
a simple ms-access-database connected via ado used two app-instances
to simulate two users

>User 1 and User 2 open the same record.  They both see the same values.  

>User 1 changes the name field for example from 'nick' to 'nick1'

>User 1 updates the database and leaves this record.

>User 2 does not see any changes.
checked -> correct

>User 2 now decides to change the name field from 'nick' (although it already is 'nick1') to 'nick2'

>User 2 tries to update the database
thats the point, the user 2 gets a message like (translated from german):
The row to update could not be found. Some values may be changed after last read.

the update failed

you see, in this case the ole-db-driver takes care about the consistence

even u can reproduce this an update-sql-statement like

update table set field1=newvalue, field2=newvalue where field1=oldvalue and field2=oldvalue

this is reflected with the property UpdateMode=upWhereAll
in the bde components (tquery,ttable)

meikl ;-)

Expert Comment

ID: 10166556

you can keep in memory the original values of the modified records. le'ts say that the user modified records 1 and 5.

before updating the database, get the modified records (records 1 and 5) from the database and compare with the original values retrieved vy the client.
  -  if they are the same, that's no probem for updating.
  -  if they are modified (let's say just record 1 has been modified by another):
     -  update the records not affected on the database (you can update the Record 5, because it was not changed) and alert the user that some records were updated by another user (Record 1) (update the Record 1 of your recordset with the new values and eventually show it in a different color or something, in order for the user to locate easily the records modified by someone else).

of course, you can approach this in a different manner.

hope it helps,

Author Comment

ID: 10166889
kretzschmar thank you for going through the steps i described, and i am familiar with the 'row cannot be updated' problem,

question is : What should i do to handle this situation more properly.  

bpana, i'm not sure about your answer.  I dont feel that its the right aproach for my situation.  Bear in mind i have 25 edits, and comboboxes in one screen all to be updated.  Checking for all of these and holding values can get in too deep and i'm not sure how i can handle it then...

any other ideas?

everyone, thank you so far...
LVL 27

Accepted Solution

kretzschmar earned 1500 total points
ID: 10167461
>question is : What should i do to handle this situation more properly.  

well, thats a question of, what you want to do in this case

there are two options (maybe more)

the first which i most use is to inform the user
that the record was changed/deleted by another user
in case of changed, i refresh the record  and force the user
to review the changes (maybe his work is already done)
and redo if needed the changes

the second would be to cache the changed record into a variantarray,
refresh the record, playback the cached values and post the record
(here could also be userdialog, where the user could decide,
which value to keep)

but the decission how depends on the kind of app and the
worth the responsibility of the users

meikl ;-)

Author Comment

ID: 10449309
i have accepted kretzschmar answer, with a b grade, because what he said was more along the lines of what i wanted to use, but i will not provide any more points, or a better grade, because i was not covered 100%...

I also didn't give anyone the chance to cover me completely, because my whole project has been restarted, and i have to code it from the beginning. So i will be asking something along similar lines again, at a later time, so you will have the option to compete then...

Take care, and thank you all

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

722 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