Conference orarep::nomahs::odbc_rdb_driver

1296.0. " TIMESTAMP datatype affects ODBC with Access95." by svrav1.au.oracle.com::MCHAN () Thu Jan 30 1997 18:44

RDB 6.1 eco4. SQL services 6.2, VAX-VMS 6.2, oracle RDB odbc
Driver 2.10.11 /2.0.11

Hi,

One of customer having problem with Microsoft Access which return an 
inconsistent value for a floating point field in an SQL table.
Apparently Microsoft Knowledge Base problem with the fix (attached below).
But the fix wont work as TIMESTAMP datatype affects ODBC with Access95. 

Here is the log of what customer did.

1. Created a column (using the ALTER TABLE ADD COLUMN SQL command) named
TIMESTAMP of datatype TIMESTAMP in a table with double precision
floating point numbers. This caused a SQL-I-DEPR_FEATURE error, as
TIMESTAMP is a reserved word.

2. Stop all ODBC execute server process that are accessing the database.

3. Start access, refresh the table definition for the above table. Then
try and retrieve data, this causes a generic ODBC call failed message.
However, if I create a query that does not reference that column. then
the data is retrieved with no errors. Furthermore, a table that has a
timestamp column that is not called TIMESTAMP will be retrieved okay.
(eg. TIMES_STAMP)

Did I miss out anything? 


Thanks,
Michael.

-----------------------------------------------------------------------------
Here is the Microsoft Knowledge Base problem description/fix:

PSS ID Number: Q96897
Article last modified on 11-05-1996
PSS database name: ACCESS
 
1.00 1.10 2.00 7.00
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0, 1.1, 2.0, 7.0
---------------------------------------------------------------------
 
SYMPTOMS
========
 
Moderate: Requires basic macro, coding, and interoperability skills.
 
Microsoft Access may return an inconsistent value for a floating point
field in an SQL table when it checks the value of the field before
editing and before updating. This can result in the error message
 
   Data Has changed: Operation Stopped
 
when you are trying to update a record in a linked (attached) SQL table.
 
CAUSE
=====
 
The problem occurs if an SQL field data type is set to Float and the
table does not contain a TimeStamp field, or when an SQL indexed field data
type is set to either Date/Time or Float.
 
RESOLUTION
==========
 
1. Remove the index from any fields with the Date/Time or Float data
   types.
 
   To obtain information on any index within a SQL table, you can run
   the following system stored procedure within the System Administrator
   Facility (SAF):
 
      sp_helpindex <Table Name>
 
   To remove an index, use the following command:
 
      DROP INDEX <Table_Name.Index_Name>
 
   Note that you must be logged into the SQL Server as either the table
   owner or the System Administrator.
 
2. If other fields in the table have the Float data type, you must
   insert a TimeStamp field in the table. If a field with this data type is
   present, Microsoft Access returns a consistent value for fields of
   this data type and will use the value in this field to verify whether the
   record has been modified.
 
   This can be accomplished by performing the following command
   within SAF:
 
      ALTER TABLE <Table Name> ADD TimeStamp timestamp
 
   Note that you must be logged in as either the database owner or the
   System Administrator to run the above command.
 
Additional reference words: 1.00 1.10 2.00 7.00
KBCategory: kbusage
KBSubcategory: ObcOthr
 
=========================================================================

Copyright Microsoft Corporation 1996.

Here's the output from ODBC logging on the Client PC:

SQLAllocConnect(henv0042D288, phdbc0043FB6C);
SQLSetConnectOption(hdbc0043FB6C, 103, 00000014);
SQLDriverConnect(hdbc0043FB6C, hwnd000006D8,
"DSN=MetersODBCConnectionSpot;SVR=10.20.20.15;CLS=ODBC_METERS_CLASS;XPT=2

;DBA=W;DSO=0;UID=CRAIG;PWD=********;a", 108, szConnStrOut, 256,
pcbConnStrOut, 0);
SQLGetInfo(hdbc0043FB6C, 9, rgbInfoValue, 2, pcbInfoValue);
SQLGetInfo(hdbc0043FB6C, 6, rgbInfoValue, 100, pcbInfoValue);
SQLGetInfo(hdbc0043FB6C, 46, rgbInfoValue, 2, pcbInfoValue);
SQLGetInfo(hdbc0043FB6C, 23, rgbInfoValue, 2, pcbInfoValue);
SQLGetInfo(hdbc0043FB6C, 24, rgbInfoValue, 2, pcbInfoValue);
SQLGetInfo(hdbc0043FB6C, 1, rgbInfoValue, 2, pcbInfoValue);
SQLSetConnectOption(hdbc0043FB6C, 101, 00000000);
SQLAllocStmt(hdbc0043FB6C, phstmt0043F700);
SQLGetStmtOption(hstmt0043F700, 0, pvParam);
SQLSetStmtOption(hstmt0043F700, 0, 0000003C);
SQLGetStmtOption(hstmt0043F700, 1, pvParam);
SQLSetStmtOption(hstmt0043F700, 1, 00000019);
SQLExecDirect(hstmt0043F700, "SELECT
COLLECT_ID,COLLECT_DATE,COLLECT_TIME,READING,CONSUMPTION,ENTERED_VALUE,ME

TER_ID,TIME_STAMP,TIMESTAMP  FROM RESULTS ", -3);
SQLError(henv0042D288, hdbc0043FB6C, hstmt0043F700, szSqlState,
pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv0042D288, hdbc0043FB6C, hstmt0043F700, szSqlState,
pfNativeError, szErrorMsg, 8132, pcbErrorMsg);
SQLCancel(hstmt0043F700);
SQLFreeStmt(hstmt0043F700, 1);
SQLAllocStmt(hdbc0043FB6C, phstmt0043F80C);
SQLGetStmtOption(hstmt0043F80C, 0, pvParam);
SQLSetStmtOption(hstmt0043F80C, 0, 0000003C);
SQLPrepare(hstmt0043F80C, "SELECT
COLLECT_ID,COLLECT_DATE,COLLECT_TIME,READING,CONSUMPTION,ENTERED_VALUE,ME

TER_ID,TIME_STAMP,TIMESTAMP  FROM RESULTS  WHERE COLLECT_ID = ? AND
COLLECT_DATE = ? OR COLLECT_ID = ? AND COLLECT_DATE = ? OR COLLECT_ID =
? AND COLLECT_DATE = ? OR COLLECT_ID = ? AND COLLECT_DATE = ? OR
COLLECT_ID = ? AND COLLECT_DATE = ? OR COLLECT_ID = ? AND COLLECT_DATE =
? OR COLLECT_ID = ? AND COLLECT_DATE = ? OR COLLECT_ID = ? AND
COLLECT_DATE = ? OR COLLECT_ID = ? AND COLLECT_DATE = ? OR COLLECT_ID =
? AND COLLECT_DATE = ?", -3);
SQLError(henv0042D288, hdbc0043FB6C, hstmt0043F80C, szSqlState,
pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv0042D288, hdbc0043FB6C, hstmt0043F80C, szSqlState,
pfNativeError, szErrorMsg, 8132, pcbErrorMsg);
SQLFreeStmt(hstmt0043F80C, 1);
SQLFreeStmt(hstmt004242A0, 1);
SQLDisconnect(hdbc0042DC54);
SQLFreeConnect(hdbc0042DC54);
SQLDisconnect(hdbc0043FB6C);
SQLFreeConnect(hdbc0043FB6C);
SQLFreeEnv(henv0042D288);
------------------------------------------------------------------------------ 

    First, I don't think they meant you should actually name a column
    timestamp, I think they meant to create a data type of timestamp using
    something else as the name. 
    
    The problem with doubles is a well known problem and I don't think just
    adding a timestamp column is going to help. There is a technical reason
    why they recommend this but won't work with RDB. 
    
    I would suggest either casting the double as a char or changing the
    data type. Doubles have a habit of rounding during data conversions and
    when Ms Access checks to see if the row had been updated, the values
    are different (because of rounding) and Access thinks the data changed. 
    
    There is a good article in one of the 9.* notes in rdb_notes written by
    Norm Lastovica describing the rounding. There are also quite a few
    notes in this conference discussing this problem as well. There is
    nothing from a ODBC driver or RDB's point of view that can "fix" this
    problem. 
    
    John

    see 9.77 in rdb_60 notes for an explanation of the rounding issues with
    doubles...
    
    john