Torque
  1. Torque
  2. TORQUE-94

DBOracle doesn't create proper TO_DATE() clause for TIMESTAMP(6) fields

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 3.3-RC1
    • Fix Version/s: 4.0-beta1
    • Component/s: Runtime
    • Labels:
      None
    • Environment:
      Java 1.5, Oracle 9i

      Description

      I observed when calling TablePeer.doDelete(tableObject) for an object that had a type="TIMESTAMP" (stored as TIMESTAMP(6) in Oracle), it would not find the matching row to delete. I tracked this down to the SQL that was being generated omitted the milliseconds.

      A row in a table with a column called 'ENTRY_TIMESTAMP' has the value:

      18-APR-07 03.41.56.705000 AM

      as viewed by SQL*Plus. The generated SQL fragment is

      TO_DATE('18-APR-2007 03:41:56', 'DD-MM-YYYY HH24:MI:SS')

      as evidenced by DBOracle.java. This is insufficient to match the milliseconds which Village apparently use when inserting the record.
      To get around this, I have written my own buildCriteria() for these objects that excludes the timestamp fields, but this is a temporary hack.

        Activity

        Hide
        Thomas Fox added a comment -

        Fixed. See org.apache.torque.generated.peer.DateTest.testSelectWithMillisecondsOnTimestampExactMatch() and org.apache.torque.generated.peer.DateTest.testSelectWithMillisecondsOnTimestampMillisecondMismatch() in the test project

        Show
        Thomas Fox added a comment - Fixed. See org.apache.torque.generated.peer.DateTest.testSelectWithMillisecondsOnTimestampExactMatch() and org.apache.torque.generated.peer.DateTest.testSelectWithMillisecondsOnTimestampMillisecondMismatch() in the test project
        Hide
        Brendan Miller added a comment -

        #1 and #2 are both present in my particular case.

        I have an object I would like to delete. It's not quite the way the code you presented, but similar. In my case I either got the record using a Criteria, or it was recently created, save()d, and now I want to delete the corresponding row.

        It also is true that this table has no primary key. And RecordPeer.doDelete() does generate a monster where clause including every column, including the timestamp field.

        Regardless, I believe the DBOracle.getDateString() is incorrect as the TO_DATE() function does not take into account fields defined with milliseconds added.

        Show
        Brendan Miller added a comment - #1 and #2 are both present in my particular case. I have an object I would like to delete. It's not quite the way the code you presented, but similar. In my case I either got the record using a Criteria, or it was recently created, save()d, and now I want to delete the corresponding row. It also is true that this table has no primary key. And RecordPeer.doDelete() does generate a monster where clause including every column, including the timestamp field. Regardless, I believe the DBOracle.getDateString() is incorrect as the TO_DATE() function does not take into account fields defined with milliseconds added.
        Hide
        CG Monroe added a comment -

        Just to clarify this... I assume that this is only a problem in either of the following two cases:

        1) You have created a record object to delete. E.g.

        Record toDelete = new Record();
        toDelete.setCol1=value1;
        toDelete.setCol2=value2;
        RecordPeer.doDelete(toDelete);

        Doing deletions this way depends a lot on the default values of the other columns because without a primary key, the SQL generated is a full set of "where col1= obj.col1 and col2 = obj.col2...." conditions that have to be met exactly (like date fields).

        or

        2) The table involved does not have a primary key defined.

        If I remember correctly, if you call the Peer.doDelete method and the record is not new and has a primary key, the primary key is the only thing used in the where clause. E.g.

        Delete from table where pk1 = xxx and pk2 = yyy

        FWIW, there was some discussion about dropping the do*(Record) methods for Torque 4.0 since it has a lot of "quirks" that make it hard to define a consistent behaviour.

        Show
        CG Monroe added a comment - Just to clarify this... I assume that this is only a problem in either of the following two cases: 1) You have created a record object to delete. E.g. Record toDelete = new Record(); toDelete.setCol1=value1; toDelete.setCol2=value2; RecordPeer.doDelete(toDelete); Doing deletions this way depends a lot on the default values of the other columns because without a primary key, the SQL generated is a full set of "where col1= obj.col1 and col2 = obj.col2...." conditions that have to be met exactly (like date fields). or 2) The table involved does not have a primary key defined. If I remember correctly, if you call the Peer.doDelete method and the record is not new and has a primary key, the primary key is the only thing used in the where clause. E.g. Delete from table where pk1 = xxx and pk2 = yyy FWIW, there was some discussion about dropping the do*(Record) methods for Torque 4.0 since it has a lot of "quirks" that make it hard to define a consistent behaviour.

          People

          • Assignee:
            Thomas Fox
            Reporter:
            Brendan Miller
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development