Timestamps and Binary Array Data

Starting with version 7.224 of the database extensions, we have added support for timestamp columns and binary arrays. Microsoft SQL Server and a number of other SQL servers have provided support for timestamp columns for some time now. Timestamps are automatically updated by the SQL provider every time a record is updated. More recent versions of Microsoft SQL Server have changed the format of timestamp columns to a binary array which is also called a rowversion column. These columns are also updated automatically every time the record is updated. The rowversion columns contain either 6 or 8 bytes of binary data, depending upon whether or not nulls are allowed in the column.

The old-style timestamp fields are returned in the format "yyyy-mm-dd hh:nn:ss.ffffff" where the time is expressed in 24-hour clock format with fractional seconds. Binary columns are returned as a string of hexadecimal digits. The hex string is not preceded by a hex indicator, such as 0x or $. These hex strings may be converted to a 64-bit integer using the StrToInt function in the Application Support DLL.

Timestamp columns cannot be updated by the user. So you cannot include them in your update calls without causing some sort of database error usually. Timestamp columns do not require a name in Microsoft SQL Server. However, we recommend naming them so the value may be accessed through a known column name variable.

Timestamps and Concurrent Updates

If you use the update_record operation to update records you read, you are actually calling an ADO post operation on that record in the dataset. What happens behind the scenes is that ADO generates an SQL update statement with a where clause in which every column in the record contained in the dataset must be equal to the same columns in the record being updated. All of this is part of the optimistic concurrency update strategy. If the record you are attempting to update has changed, and any of the changes were made to columns in your dataset, the match will fail in the where clause, and the update will fail. This process has been deemed horribly inefficient by many SQL experts.

An alternative is to use the timestamp/rowversion column to determine if a record has been changed. In this case the where clause uses the equality operator to compare the timestamp of the record that was read to the timestamp of the record being updated. The value of the timestamp/rowversion column should be expressed in a format suitable for the SQL provider you have. You should consult your SQL server documentation for more information. The rowversion formats used in current versions of Microsoft SQL Server allow this value to be expressed as an integer.

Consider the following example:

; connect to database

$set_var  DBCONN "CC%=@CM_UDLFILE[*DUMMY*]"

; try connecting at least twice

$set_var  DB_FILE "@DBCONN"

$if @DB_RESULT $ne 0

 $set_var DB_FILE "@DBCONN"

$endif

; only proceed if we successfully connected

$if @DB_RESULT $eq 0

 ; try retrieving master record if we connected to database

 $set_var DBSQL "Select * from CampaignMaster where CampaignID = @CM_CAMPAIGNID"

 $set_var DB_SELECT "SQL=@DBSQL"

 ; retry if error (record missing is also an error during post-process)

 $if @DB_RESULT $lt 0

  $set_var DB_SELECT "SQL=@DBSQL"

 $endif

 ; read campaign master if query succeeded

 $if @DB_RESULT $gt 0

  $set_var DB_ACTION get_record

  $if @DB_RESULT $ne 0

    $set_var DB_ACTION get_record

  $endif

 $endif

 ; if we were able to read campaign master record then update it

 $if @DB_RESULT $eq 0

  ; get timestamp so we can use it in update

  $set_var LASTUPDATED "$fn:StrToInt('0x@CC%LastUpdated')"

  ; update call counter

  $set_var CC%CallsPlaced +1

  ; now prepare update statement

  $set_var DBSQL "Update CampaignMaster Set CallsPlaced = @CC%CallsPlaced "

  $set_var DBSQL2 "where CampaignID = @CAMPAIGN_ID and LastUpdated = @LASTUPDATED"

  $set_var DBSQL  |@DBSQL2

  ; now do the actual update of the master record

  $set_var DB_SELECT "SQL2=@DBSQL"

  ; if master updated successfully then proceed to update transaction

  $if @DB_RESULT $lt 1

   $set_var DBTRIES +1

   $set_var ERR_CLASS 1

   ...

In the first line highlighted in red we convert the value returned in the timestamp/rowversion column named LastUpdated from a hexadecimal string to an integer value. We then use this value in the where clause of the SQL update statement shown in the second highlighted line. This assures us that we will only be updating the record if the timestamp/rowversion matches the value at the time we read the record. It will only match if no other user has updated that record since we read it. Should the update fail, you can reread the record and try the update again, possibly after a suitable delay. Also note that in converting the hexadecimal string to an integer, we preceded the value with the characters 0x. This tells the conversion routine that the string contains a hexadecimal value. You could have used the dollar sign ($) instead of the 0x if you prefer.

In order to use timestamps or rowversion columns you may need to add this column to your tables. Only one such column may appear in a table. These columns are not to be confused with normal date/time columns, although they may contain date/time compatible values. The nature of the data in timestamp columns depends upon your SQL provider and which version you are using.