Re: [Reactor For CF] Reactor & MSSQL Timestamp fields

[Callum McGillivray]

Hi Doug, When poking around with google, I found these snippets (one referenced from MSDN); <snip> A SQL Server timestamp is a column that gets updated automatically by SQL Server when a row is inserted or updated. You cannot update a timestamp column directly. A table may only have one timestamp column. The timestamp column is often used to implement optimistic locking - if the row's timestamp has changed since you last read the row then another process/user has updated the row since you read it. </snip> and also this; <snip> The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. </snip> >From my quick observations, it appears that the timestamp in SQL Server is actually stored as binary. Other quick searches seem to indicate that it's quite difficult / maybe impossible to actually get this data into a usable date and time format. The first snippet above actually applies to us, the database that we are working with was designed by a third party and has a traditional Client-Server GUI attached to it.  In general, when editing a customer record and trying to save, the GUI will check to see if the customer record has been updated, and offers the user the chance to reload the record before attempting to commit the changes. Using CF with SQL Server timestamps would not normally present an issue, simply omitting the timestamp field when doing an update or insert would see SQL Server automatically update the field.  However, the application that I am now building aims to mimic much of the functionality found in the desktop GUI, so the ability to read and compare the timestamp field suddenly becomes important. >From my (somewhat limited) understanding at the moment, simply omitting the timestamp field from an update or insert should probably be the default behavior of reactor when committing. (After all, trying to update the field would mean that reactor would be trying to do SQL Server's job, which in this case, it's apparently not qualified to do :p). But what should reactor do when reading the data ?  Possibly just leave the data as it's currently returned by CF,  making it available to a developers who, like me, might want to use it for comparisons etc. Now I'm stuck wondering how the desktop GUI does a comparison using this field... something I'm going to have to investigate. (Happy to hear from anyone who's encountered this previously !) Cheers, Callum P.S. Thanks for your seemingly tireless work on this Doug... Reactor is shaping up to be a fantastic tool ! Doug Hughes wrote: Callum, they're untested (by me). What are timestamps really? Integers? Are they supposed to be cast to dates or what? If you help I'll fix this. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Callum McGillivray Sent: Monday, May 01, 2006 10:14 PM To: reactor@doughughes.net Subject: [Reactor For CF] Reactor & MSSQL Timestamp fields Hi all, We have a table in our SQL Server 2000 DB that contains a timestamp in binary format. Whenever we call an update() method on the record, we get an error along the following lines; "Message The cause of this output exception was that: coldfusion.runtime.Cast$DateConversionException: The value class [B could not be converted to a date.." Has anyone else experienced this, how are timestamp fields in MS SQL generally handled by reactor? Any thoughts apprecaited. Warm Regards, Callum -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/