StarQuest Technical Documents

StarSQL Workaround For Updating DB2 Tables with Timestamps in Microsoft Access and Visual Basic

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ027

Abstract

This document discusses an update problem using StarSQL and the Microsoft’s JET database engine for DB2 timestamp data. Applications that use the JET database engine include Microsoft Access and Visual Basic (DAO).

Symptoms

When using Microsoft Access to update a record in a table which contains a TIMESTAMP column, you may receive the error:

"This record has been changed by another user since you started reading it, if you save the record you will overwrite the changes made by the other user, copying the changes to the clipboard will let you look at the values the other user entered and then paste the changes back if you decide to use them"

The user is then presented with 3 dialog boxes: "Save Record" (which is grayed out), "Copy to clipboard", and "Drop changes."

Solution

StarQuest has a workaround for this problem. The workaround presents timestamps to the calling ODBC application as CHAR (26) strings to prevent truncation. This option is off by default.

To enable the workaround:

1. In the StarSQL data source, go to the Expert Page and set the TimeStampAsChar value to Yes.
2. Click Summary and then OK to save the data source changes.
3. Restart Access and re-link the table.

Note: It will add some complexity to any application using the timestamp column. Contact StarQuest Technical Support if you have any questions.

Details

This workaround will allow users to update DB2 for OS/390 or z/OS based tables which contain TIMESTAMP columns. DB2 uses a higher timestamp precision than Microsoft Access can store in its local representation (double precision float). Updates with Access/Jet may fail if the timestamp values have higher granularity than milliseconds.

Access executes an SQL Select statement when the user clicks on an attached table. If one of the result set columns is a timestamp with precision 26 (yyyy-mm-dd 00:00:00.000000), Access calls SQLGetData to get the timestamp value into an SQL_C_TIMESTAMP structure. When the user modifies a value in another column of a row, Access issues an Update SQL statement in order to verify that the data did not change between the time the result set was fetched and the current update attempt.

One of the parameters passed in the Update SQL statement corresponds to the timestamp column that Access previously retrieved with the SQLGetData function. Access reports to StarSQL (using the SQLBindParameter function) that it will be passing in a timestamp with precision 26 in an SQL_C_TIMESTAMP structure. When the SQL Update statement is executed, the SQL_C_TIMESTAMP structure contains a different value than what was retrieved by the SQLGetData function. In an ODBC API trace, you can see that what is being passed back to the ODBC driver, is data in the timestamp structure which has been truncated to a timestamp with precision 23. This constitutes data corruption since both values are supposed to be timestamp representations of the column content with precision 26.

Microsoft’s Response

The Jet implements datetime as an approximate numeric, i.e. a floating point number. The DB2 implementation is an exact numeric. When converting from exact numeric to approximate numeric, the value may be different. Since the TIMESTAMP column is used along with the other columns in the row to uniquely identify the row for concurrency checking prior to the update, and the value is different, the update fails.

Because a unique index has been created as described above, the above join statement is resolved on the host and only those rows that satisfy the join are returned, resulting in optimal performance.