The best way to manage concurrency is to manage your own timestamp. In
effect the timestamp is a unique number that is assigned to each record
being inserted. Once the record is retrieved you have to increment it. When
the update is done you verify that the timestamp of the record (retrieved)
is the same as the timestamp of the record in the dB. If they are the same
the record has not been modified and you can update it.
So declare an int type column, set it to 1 when the insert is done and
increment it with 1 with each update. It is a little more work but you have
absolute control and the solution is database independent.
Marius Milosav
www.scorpiosoft.com
It's not about technology, it's about people.
Virtual Help Desk Demo (VHD)
www.scorpiosoft.com/vhd/login.cfm
----- Original Message -----
From: "Bob Silverberg" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Saturday, January 06, 2001 11:10 AM
Subject: Using SQL Server timestamp datatype to deal with concurrency issues
> Hi all,
>
> I am trying to come up with a generic scheme to deal with concurrency
issues
> in database updates, and I decided that the best approach would be to add
a
> field to each table with the timestamp datatype. SQL Server will
> automatically update this field anytime a record changes. I will retrieve
> the value of this field when I query the table, and store it in a hidden
> form field. When the user submits changes to the table, I'll requery the
> table and try to find a row with the same timestamp value. If one is
found,
> then the row hasn't changed and I can go ahead with the update. If a row
is
> not found then I know that someone else changed the data, and I can deal
> with that programmatically.
>
> The problem I encountered is working with these timestamp fields, as they
> are actually binary datatypes in SQL Server. After some experimentation,
I
> have discovered the following:
>
> 1. SQL server does return a text string to ColdFusion when you query this
> field, but if you pass that text string back to SQL Server in your where
> clause it causes an error.
>
> 2. If you compare the output of this column in Query Analyzer to that
> received by ColdFusion, you see that the data looks exactly the same,
except
> Query Analyzer has "0x" in front of the string that is passed to
ColdFusion.
> For example:
> QA: 0x00000000000002B6
> CF: 00000000000002B6
>
> 3. If you append the characters "0x" to the ColdFusion string, and then
use
> this in your where clause against the timestamp column, it works!
>
> I have tested a number of tables, and the above (#3) always seems to work,
> but I'm not sure why (does it have something to do with hex?). I wanted
to
> check to see if anyone on the list has had any experience with this, and
> could tell me whether solution #3 is valid, or whether it might not work
in
> all situations.
>
> As well, anyone who has come up with a different or better solution to
> dealing with concurrency issues, I'd love to hear about those too.
>
> Thanks,
> Bob
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
