|
Hi all,
I was wondering if it is possible to get up-to-second data from a database without querying for it.
I want a lock-mechanism that prevents people from editing specific rows on a windows form that have been edited by other users on other computers. The only way they are connected is by database.
An 'easy' solution would be to have a DB table that holds a boolean wether the current row is being edited and then have the application 'poll' for changes every few seconds. But there will be a lot of polling for a whole lot of nothing...
So is there any other smart way to do this?
Thanks.
It's an OO world.
|
|
|
|
|
Hi,
There are really many ways to do this, for example:
- If you want to check that no-one has made modifications after reading the data include optimistic locking. In this case include a column into the table which is modified every time the row is changed and include the value from this column in UPDATE/DELETE statements.
- If you want to prevent changes from another session after you have read the data until you release it, then take advantage of the pessimistic locks, i.e. lock the row the user is editing for example with a simple update. However this is typically not the best solution since transactions should never include user conversations.
But in any case do not include edit-state information in the table. In order to do that you would have to commit the change into to database and you would be in trouble in no time since you would have 'locked' rows left in the db.
You didn't mention the DB vendor, but for example in SQL Server there's a special datatype rowversion (timestamp in older versions) for optimistic locking.
|
|
|
|
|
Thanks for the reply.
That's already some useful information that I will certainly look into. But I do not see how it fixes the following problem yet.
The problem is that user A and user B both load the same data (into a WinForms app). Now while the data for both users is loaded user A starts to edit. At this time user B should automatically see that the row is being edited without the need of refreshing/reloading the screen (not manually anyway).
I am using SQL2008.
It's an OO world.
|
|
|
|
|
|
Wow, certainly going to check that out this weekend (maybe sooner)! Thanks 
It's an OO world.
|
|
|
|
|
You should have added "not urgent at all" to your subject line.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
That was hilarious
|
|
|
|
|
I never said it was urgent... 
But the quest for knowledge always has a top priority!
It's an OO world.
|
|
|
|
|
No problem 
|
|
|
|
|
Naerling wrote: I was wondering if it is possible to get up-to-second data from a database
without querying for it.
Usually not a good idea.
Naerling wrote: I want a lock-mechanism that prevents people from editing specific rows on a
windows form that have been edited by other users on other computers. The only
way they are connected is by database.
Definitely not a good idea for that.
Do you have a real business case to support the need for this. Not some hypothetical that you made up but a real case that one or more business people told you would happen?
Naerling wrote: So is there any other smart way to do this?
A database lock. But it isn't easy to get it correct especially when dealing with all possible error scenarios.
And it is pointless and a significant waste of time unless you have a real business case that requires it.
|
|
|
|
|
Thanks for your answer.
A rather big client of my company is currently using an application where they load many rows into many columns and many people are seeing and editing them. This was their request, and they want to be able to see when someone else is editing a row.
We are currently just checking the database every 10 seconds, but this seems like the least best solution for the problem.
The case does not really matter. Wether the form shows data on the wether or on actors playing in movies is not really a concern. As long as a row cannot be edited by anyone except the person who edited first.
Telling this customer that this is not a good idea is not a good idea... We do it their way, period 
I guess it is good luck we do not have to tell them HOW we do it, as long as we do it 
It's an OO world.
|
|
|
|
|
Naerling wrote: and they want to be able to see when someone else is editing a row.
That is a pretty specific request.
If that is the exact request then you have no choice but to
1. Track the user when they are in edit mode.
2. Mark the row as being in the editing process.
Given that you will also have other concerns such as
A. What do you do if the user goes to lunch or on vacation?
B. What happens if the users application crashes?
C. What if someone else must make an immediate update when another person who is unavailable is editing it.
D. What is the exact notification process.
The specifics of how you implement the solution depends on the architecture but writing a value to the database is one possible way to solve 2 above. It however solves only that.
Naerling wrote: Telling this customer that this is not a good idea is not a good idea... We do
it their way, period
At least as long as they are willing to pay for it and it does not impact your other customers.
|
|
|
|
|
If you use locking, that may block the system:
User starts to edit a record, then it's lunch-time and he leaves without saving the record
If you use a flag, that even may block the system:
User starts to edit a record, the flag is written to the special DB-Table, then his station went down (power failure etc.)
The only universal DB-way is - as others mentioned - to either have a 'version'-count or a 'last edited' timestamp, and if a user wants to edit a record, read that column and compare with the original value.
As long as a edited record isn't UPDATEd in the database, the DB-Server isn't (and shouldn't be) involved.
I use a mailslot for this purpose; every station listens to this, and if a record is about to be edited, the station in question sends a broadcast with the primary key of this record, so every station knows.
Also every station broadcasts a 'living' message every 5 seconds, and every station has a list of all other stations to maintain the 'living' state.
So if one station dies, all other stations will know 5 seconds later and can easily kill the 'editing' information of the died station.
With this the is even a in-application chat possible.
(Sorry for my bad english, I'm from germany and not so experienced using your lang...)
|
|
|
|
|
Hi all, I'm wondering how many guys here are experiencing the MySql bug which just popped out with the installation of VS2010 SP1, reported here:
http://bugs.mysql.com/bug.php?id=60404[^]
Sadly I'm one of them, and I'd like to know if it's just me and some other guys (in this case I'd try a fresh reinstall), or this is a general bug which must be addressed. Thanks!
|
|
|
|
|
hello guys... I wrote an application inwhich I use the user 'sa' to do some small transactions, in Sql Server. Now when I run same program on some other computer, this programs shows some debug error saying
Debug Error !
This application has requested the Runtime to terminate in its unusual way. Please contact the applications support team for more information.
Now since I have run this small EXE on my laptop, so I know nothing is wrong with my program. There got to be something wrong with my user creation. So how to create user 'sa' properly...thnx
|
|
|
|
|
Hi,
You don't create sa user. sa is created byt the SQL Server when you choose mixed security mode during the installation or later via instance properties. So sa is a special user defined when SQL Server authentication is enabled, kind like administrator in windows.
[Added]
To change the authentication mode, for example: http://msdn.microsoft.com/en-us/library/ms188670.aspx[^]
The need to optimize rises from a bad design.My articles[^]
modified on Saturday, March 12, 2011 12:44 PM
|
|
|
|
|
overloaded Name wrote: There got to be something wrong with my user creation
I suppose that is possible if
1. Your wrote your program in C/C++
2. You have a bug (or several) in your code.
Supposing that the first is true then when you attempt to interact with the database you will get error information back (if your code doesn't have bugs.) So reporting that information is useful. And if that error is such that the app can no longer proceed then the application should gracefully exit.
|
|
|
|
|
In my company we are using a SQL Server 2005 Database to manage all our client and job records. However this database was originally designed by someone in the company who didn't know much about the database. We are reaching a point in our growth were this database needs to be converted to a relational database. So I copied the database locally to work with it. I used Access (2010) to analyze the database and set up the tables. When I go through the wizard I get to the last step where it shows all the data that seems to be similar so I can select what each record should be (correcting typographical errors step). This will eliminate duplicate client names, site names, etc. However there are over 10,000 records in the database, and to go through this manually would take a long time, as it seems there is no way to start it and finish it at a later time. Does anyone have an recommendation on how I can go about making this flat database into a relational database relatively easily? There is approximately between 16,000 and 17,000 records. Thanks in advanced for any help or suggestions.
modified on Friday, March 11, 2011 11:48 PM
|
|
|
|
|
Hi,
One possibility is to use 'temporary' tables and DISTINCT queries. For example if you have a table with column SiteName and you have duplicate site names, you could do something like:
SELECT DISTINCT SiteName INTO NewSites FROM Sites;
TRNCATE TABLE Sites;
INSERT INTO Sites SELECT * FROM NewSites;
DROP TABLE NewSites;
In the real situation you most likely have more coĺumns and possibly other conditions. This is why I chose this 'temporary' table approach since you can easily check if the result is fine before you delete the original data and possibly do the duplicate elimination in smaller parts if that's more suitable.
This can also be done in-place if you have some kind of mechanism to identify the row. If a table has an Id that's preferred and if it doesn't you could perhaps modify this http://www.codeproject.com/Tips/159984/How-to-remove-duplicate-rows-in-SQL-Server-2005-wh.aspx[^] to your needs.
|
|
|
|
|
DisIsHoody wrote: This will eliminate duplicate client names, site names, etc. However there are over 10,000 records in the database, and to go through this manually would take a long time, as it seems there is no way to start it and finish it at a later time. Does anyone have an recommendation on how I can go about making this flat database into a relational database relatively easily?
Second did not cause the first problem.
That same problem can occur in a relational database.
As per the other posting you can use distinct to eliminate duplicates but only on exact matches. Consider the following entries.
Mikes Auto 431 W. Main Street
Mike Auto 431 W. Main Street
Mikes Auto 431 West Main St.
Which of them are duplicates? Can one create code that eliminate duplicates from a list like this? Yes. Is it worth it for 10,000 records? Depends.
|
|
|
|
|
Watch using Access to SQL as Microsoft helps you by creating columns that are NOT what is wanted.
I have to live with postal code being a float because the person moving data from Access to SQL did not know what they were doing. As long as we only do work in the US it is not a major problem but comparison can be tricky.
|
|
|
|
|
Hello,
We are retiring our older server, not yet, in place going to have a new server. WE need to name the new database server as our current one. Can we have two such database servers at a given time? How can we migrate from one server to another with no lag time, since it is the production server constantly collecting data.
Thank you.
|
|
|
|
|
Hi,
vanikanc wrote: WE need to name the new database server as our current one
First let's separate two totally different things:
- the name of the database server is just an alias for the IP-address. So this means that you cannot have two different machines having the same name (thus the same IP-address) at the same time or you'll have enormous problems in you network
- the name of the SQL Server instance (the service) is used to distinguish separate SQL Server services on the same machine.
So basically your question is client-related. If you want to make the switch-over to the new (physical) server you have to be able to re-configure your clients to start using the new server. For example if your old SQL Server is ServerProduction1\Instance1 (meaning for example 10.1.123.1,1433\Instance1) you would change the client to connect to ServerProduction2\Instance1 (meaning for example 10.1.123.7,1433\Instance1). Of course the instance name can also be changed.
vanikanc wrote: Can we have two such database servers at a given time?
So the answer from the client side point of view is no. Which one would they connect to?
vanikanc wrote: How can we migrate from one server to another with no lag time, since it is the
production server constantly collecting data
You restrict the changes to the original database during the transfer, transfer all the data from server 1 to the server 2, re-configure the clients to connect to server 2 and off you go.
|
|
|
|
|
guys i have a table with a self-referencing relationship.
I want to get the child records listed just below their parent.
just like:
id parent value
--- ------ ------
1 null node 1
4 1 node 1/1
6 4 node 1/1/1
5 1 node 1/2
7 1 node 1/3
2 null node 2
3 null node 3
8 3 node 3/1
any help;
Help people,so poeple can help you.
|
|
|
|
|
Include the parent id and the child id in each record and then include them as the first two fields to sort by. 
select p.id
,c.id
, .....
from some_table p
,some_table c
where p.id = c.parent_id_field
sort by p.id, c.id
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
