Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Default Timestamp

How can I increase the level of Watcom SQL consistency checking beyond the "Key and Updateable Columns" setting available in the DataWindow Rows - Update window? I want to prevent an update from working if any column values have changed, not just those columns that happen to be updateable in this particular DataWindow.

Prior to SQL Anywhere 5 the most straightforward solution was to include all the table's columns in the result set and mark them all as updateable in Rows - Update even if your program has no intention of actually changing them.

With some very large result sets this causes the buffer to explode with unwanted data, stuff that's present just to force PowerBuilder to build a truly paranoid where clause for the SQL update and delete statements.

SQL Anywhere 5 brings a new column default value called simply "timestamp". Unlike the "current timestamp" default, the new value tells the database to update this column every time any other column in the row is changed:

create table concurrency (
   pkey         integer not null,
   data         char ( 10 ) not null,
   other_data   char ( 10 ) not null,
   last_updated timestamp not null default timestamp,
   primary key ( pkey ) );

Now all you need to do is include that column in every result set instead of all the other unwanted columns. Don't actually change it in your programs, just let the database maintain it.

Here's an example where row 3 has been changed and updated in the bottom DataWindow. An attempt is being made to change a different colummn in the same row in the top DataWindow, simulating two users who are competing to update the same row:

When the top Update button is pressed, here's what you get:

Not only can the new timestamp default be used to increase the level of consistency checking, it can also be used for application purposes. That's because the column contains real date and time values instead of the strange counter that SQL Server calls "timestamp".