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.
Last modified: June 27, 1996
mail to: firstname.lastname@example.org
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".