Hi friends,
In my quest for achieving FailSafe record locking in MSSQL2000 programatically
using Delphi 7 , i came across the following article in web. My reaction to
this hint is ,this is simple and looks safe. But i would like our forum friends
to give any possible negative impact on implementing this tip. I would
appreciate if anybody can suggest better way of implementing failsafe record
locking in mssql2000 using delphi.
thanks in advance
regards
nisha.
-------------------------------------------------------------------------------
Author Name :Mr.Vassilis Perantzakis
For every record you want to lock to this:
Create a global temporary table that is named after the table on which the
record is, together with the Unique Id of the table. For example, if you have a
table named customers, with a unique id field called Uid and you want to lock
the record with uid=14, create the table using this query: Create table
##Customers14 (id int null) .When you want to unlock the record just drop that
table: Drop table ##Customers14
Now lets say that another user wants to use the same record. His client
programm tries to create the same global temporary table, but fails with an
exception, because no two global temporary tables can have the same name. Trap
the exception in a try-except clause and you are home free.
TIPS.
a) Use this only for SQLServer 7 and above. SQL 6.5 and below have a terrible
way of handling Temprorary tables that gives a lot of overhead.
b) You can create any kind of collumn in your temporary table, so you can have
info like what time the record was locked and by what user.
c) Never use this approach if there is a chance someone will forget his
computer open on a record for hours, and that computer is located lets say 100
miles from the server!!!
d) If the connection is lost by lets say an application error, the table is
automatically droped by the SQL Server.
e) If the computer shutsdown by a power failure, the SQL Server waits for
about 15 minutes and then drops the temporary table, or if the computer logs
on again the table is droped automatically.
f) If you don't want to have to handle an exception you can also check for the
existance of the Temporary table in the Master database.
------------------------------------------------------------
---------------------------------
Check out Yahoo! India Rakhi Special for Rakhi shopping, contests and lots
more.
http://in.promos.yahoo.com/rakhi/index.html
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12hb0gt4o/M=362131.6882499.7825260.1510227/D=groups/S=1705115362:TM/Y=YAHOO/EXP=1124350627/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life
- brought to you by One Economy</a>.</font>
--------------------------------------------------------------------~->
-----------------------------------------------------
Home page: http://groups.yahoo.com/group/delphi-en/
To unsubscribe: [EMAIL PROTECTED]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/delphi-en/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
