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

Hi,

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?

Andrew

>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 ;-)

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.  

Regards,
  Tomas Helgi

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.

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.

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

listenning for the moment ...

>>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.

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.

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?

>>>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.

?
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 ;-)

Ok,

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)

Bogdan

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)

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?

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.  
checked

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

>User 1 updates the database and leaves this record.
checked

>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'
checked

>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
(simplified)

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 ;-)

hi,

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,
Bogdan

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...

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
nick-p