Re: Is there hidden timestamp column ?

wyang@aerotek.com (wyang) wrote: :Nils Myklebust wrote: :> :> lclee@nntp.best.com (Lin-Chuan Lee) wrote: :> :> :Is there a timestamp column that can be queried/fetched :> :like the rowid column ??? :> :> The lack of such a column is one of the major remaining problems with :> Informix databases. Of course it should *NOT* be hidden, but be a :> special datatype. I hope someone will do this for the Universal :> Server. (For those who are not 100% clear on what a timestamp is it's :> a type of serial that starts out at 1 (or 0) when a row is inserted :> and is increased by 1 every time an update is done to that same row. :> It's very usefull to avoid the need of locking rows in an optimistic :> locking strategy.) :I saw this a long time ago from a Visual Basic tip article(because it's :difficult to do an optimistic locking on sql server form VB). Some :people just add a datetime column to the table and update it to current :everytime the record is updated. But you have to query and compare this :timestamp to decide if another user has updated the row. This incurrs :extra disk I/O, so it's not the best solution. :Since Informix supports row level locking, and you can always add such a :column youself. I don't see why this "is one of the major remaining :problems with Informix databases". :Please correct me if I am wrong. First because you can't use a datetime column the way you describe. Whatever the resolution you use (and your engine and OS actually support) there is a risk that several updates can be done within that resolution (giving the same datetime). A timestamp in a db generally has nothing to do with time messured as a datetime variable. It is a number that is incremented sequentially as some operation is performed. A higher number means later in time. It says nothing about absolute time, only relative time (later than something else). Second as you say you have to query and compare the timestamp (even if you implemented it as a sequentially increased number) which requires more I/O than if it was supported by the engine. Third you have to put a lot of support into every application that can update the database if you did this in each program. Not a good idea. The solution used by some is to include an extended where clause in the update statement. Usually you update where primary_key = somevalue or use where current of cursorname. Instead you would say where primary_key = somevalue and col1 = original_value_of_col1 and col2 = originial_value_of_col2 and so on for all columns in the table (or all the once you where going to update depending on your requirements). This runs into problems if a column can contain null. Than if the original value was null you would have to replace the = test with "is null" instead. (I belive this PowerBuilder can do this, but I am not quite sure.) Also such an update statement has to be prepared again for every update you do. Not a good thing. It may be a better alternative to simply select the whole row again and compare it to the original to see if it was updated by someone else. If you are using 4GL there is a function in the iiug library that does such comparison on any two records including full support for null-values. (If the original and new "value" for a column are both null they should be considdered equal contrary to normal usage of null.) What other solution do you have for optimistic locking in an environement with interactive users doing updates of individual rows? Clearly support for a timestamp (again remember in the form of an incrementing number) in the database would be very benefitiary indeed, and make programming very much simpler. So much so that I see it as a major problem with the Informix engines. In may opinion this is something that should be the default way to do single updates in engines as well as in languages. The fact that it's not fully supported by everone in this bussiness is sad, and I have a creeping fealing there are a lot of not very good multi user programs out there for exactly this reason. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company