Optimistic Locking: How DATE field comparision is broken in Oracle 9i and possible options

From: Mike Kienenberger (mkienen..laska.net)
Date: Mon Feb 16 2004 - 14:35:39 EST

  • Next message: Mike Kienenberger: "Optimistic locking on obj-relationship fields / data-map dtd question"

    Here's my take on the Oracle Date problem. Please let me know if I'm
    off-base or if I've overlooked an option.

    Oracle has two fields for representing a date.

    DATE is what's been around since Oracle 8 and is accurate to the
    millisecond.
    TIMESTAMP was introduced in Oracle 9 and is much more precise.

    Java JDBC specifies three sql data types:
    java.sql.Time (only holds hour, minute, second, millisecond values)
    java.sql.Date (only holds year, day, month values)
    java.sql.Timestamp (Holds year, day, month, hour, minute, second,
    millisecond, nanosecond values)

    Java itself represents time as java.util.Date objects (Holds year, day,
    month, hour, minute, second, millisecond values).

    All java.util.Date objects have to be converted to an SQL type to be sent to
    the database driver.

    The problem appears to be that with the Oracle 9 driver, storing a
    java.sql.Timestamp into a DATE field rounds to the nearest millisecond.
    However, comparing a java.sql.Timestamp to a DATE field does not round the
    Timestamp. Thus, there is no way using the Oracle driver to compare a
    Timestamp with a DATE, such as WHERE paymentDate = ? [bound to a Timestamp]
    in an optimistic locking statement.

    This was not the case with the Oracle 8 driver since there was no notion of
    precision greater than milliseconds.

    (This would also explain why making WebObjects zero all timestamp fractional
    second fields solved the problem last year when we switched from Oracle 8 to
    9.)

    So what are my options?

    I can change all DATE fields to TIMESTAMP fields in the database. This
    seems to work, but requires a lot of changes to our production and
    development databases.

    I can ask Oracle to fix the driver to allow DATE values to be compared the
    same way they are stored. While this makes complete sense, I don't think
    it'll happen based on my experiences last year.

    I can round all dates to the nearest millisecond in code before sending them
    to Oracle. I guess this would need to be done as a modification to the
    Oracle adapter classes.

    Another possibility might be to change all code to represent dates as
    java.sql.Timestamps rather than as java.util.Dates in my model. I think
    Cayenne currently supports a non-java.util.Date class in the model by
    default.

    Any suggestions?

    -Mike

    This archive was generated by hypermail 2.0.0 : Mon Feb 16 2004 - 14:35:32 EST