I want to use the timestamp field in a MS SQL database table to implement optimistic locking from PHP.
I get a row with a select statement using the 'for browse' option.
eg - "select * from tab1 where col1='a' for browse"
When I do this, I can retrieve the value of the timestamp field.
Now what I want to do is use this timestamp value in the where clause of an update statement to ensure that the row has not been updated by anyone else in the interim.
eg - "update tab set col2='5' where col1='a' and timestamp=$ts" where $ts is the timestamp value retrieved from the previous select statement.
I get an error message that says I cannot implicitly convert a string to a timestamp and I should use convert.
So I tried - "update tab set col2='5' where col1='a' and timestamp=convert(timestamp, $ts)
This gives no errors but the row is never updated, presumably because something is wrong with the timestamp value. When I print out $ts I get a 16 character hexidecimal value that looks reasonable eg "000000000001be16".
Does anyone know how to get around this problem? Has anyone else successfully used a timestamp value in MS SQL Server in this way?
I get a row with a select statement using the 'for browse' option.
eg - "select * from tab1 where col1='a' for browse"
When I do this, I can retrieve the value of the timestamp field.
Now what I want to do is use this timestamp value in the where clause of an update statement to ensure that the row has not been updated by anyone else in the interim.
eg - "update tab set col2='5' where col1='a' and timestamp=$ts" where $ts is the timestamp value retrieved from the previous select statement.
I get an error message that says I cannot implicitly convert a string to a timestamp and I should use convert.
So I tried - "update tab set col2='5' where col1='a' and timestamp=convert(timestamp, $ts)
This gives no errors but the row is never updated, presumably because something is wrong with the timestamp value. When I print out $ts I get a 16 character hexidecimal value that looks reasonable eg "000000000001be16".
Does anyone know how to get around this problem? Has anyone else successfully used a timestamp value in MS SQL Server in this way?