SQL Anywhere supports the Transact-SQL special timestamp column. The timestamp column, together with the tsequal system function, checks whether a row has been updated.
Two meanings of timestamp
SQL Anywhere has a TIMESTAMP data type, which holds accurate date and time information. It is distinct from the special Transact-SQL TIMESTAMP column and data type.
To create a Transact-SQL timestamp column, create a column that has the (SQL Anywhere) data type TIMESTAMP and a default setting of timestamp. The column can have any name, although the name timestamp is common.
For example, the following CREATE TABLE statement includes a Transact-SQL timestamp column:
CREATE TABLE tablename ( column_1 INTEGER , column_2 TIMESTAMP DEFAULT TIMESTAMP );
The following ALTER TABLE statement adds a Transact-SQL timestamp column to the SalesOrders table:
ALTER TABLE SalesOrders ADD timestamp TIMESTAMP DEFAULT TIMESTAMP;
In Adaptive Server Enterprise a column with the name timestamp and no data type specified automatically receives a TIMESTAMP data type. In SQL Anywhere you must explicitly assign the data type yourself.
If you have the automatic_timestamp database option set to On, you do not need to set the default value: any new column created with TIMESTAMP data type and with no explicit default receives a default value of timestamp. The following statement sets automatic_timestamp to On:
SET OPTION PUBLIC.automatic_timestamp='On';
Adaptive Server Enterprise treats a timestamp column as a domain that is VARBINARY(8), allowing NULL, while SQL Anywhere treats a timestamp column as the TIMESTAMP data type, which consists of the date and time, with fractions of a second held to six decimal places.
When fetching from the table for later updates, the variable into which the timestamp value is fetched should correspond to the column description.
In Interactive SQL, you may need to set the timestamp_format option to see the differences in values for the rows. The following statement sets the timestamp_format option to display all six digits in the fractions of a second:
SET OPTION timestamp_format='YYYY-MM-DD HH:NN:SS.SSSSSS';
If all six digits are not shown, some timestamp column values may appear to be equal: they are not.
With the tsequal system function you can tell whether a timestamp column has been updated or not.
For example, an application may SELECT a timestamp column into a variable. When an UPDATE of one of the selected rows is submitted, it can use the tsequal function to check whether the row has been modified. The tsequal function compares the timestamp value in the table with the timestamp value obtained in the SELECT. Identical timestamps means there are no changes. If the timestamps differ, the row has been changed since the SELECT was performed.
A typical UPDATE statement using the tsequal function looks like this:
UPDATE publishers SET City = 'Springfield' WHERE pub_id = '0736' AND TSEQUAL(timestamp, '2005/10/25 11:08:34.173226');
The first argument to the tsequal function is the name of the special timestamp column; the second argument is the timestamp retrieved in the SELECT statement. In embedded SQL, the second argument is likely to be a host variable containing a TIMESTAMP value from a recent FETCH on the column.