OFBiz
  1. OFBiz
  2. OFBIZ-1001

Oracle Database Date Query Problems

    Details

    • Type: Wish Wish
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: framework
    • Labels:
      None
    • Environment:

      SUSE Linux 10 & Oracle DB

      Description

      Problem with the date conversion.
      The Following SQL Statement is not working on a Oracle Database
      SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875' AND ....
      Using an Oracle database, this statement is wrong and generates the ORA-01861 error.
      The statement should look like this:
      SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15 07:45:10', 'YYYY-MM-DD HH:MI:SS') AND ....

      Solutions
      =======
      1. Use Oracle Trigger: http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle
      2. Set Oracle Date Format: ALTER SYSTEM set NLS_TIMESTAMP_FORMAT='RRRR-MM-DD HH24:MI:SS.FF'
      3. This patch.

      The patch is a general solutions based on the design hibernate is using. (Every driver has a Dialect class).
      All databases are working like before because the default implementation is just returning the input string (only
      for oracle databases, a special string is returned).

      1. oraclePatch.txt
        116 kB
        Michael Imhof

        Activity

        Gavin made changes -
        Workflow jira [ 12404432 ] OFbiz Workflow [ 12505221 ]
        David E. Jones made changes -
        Assignee David E. Jones [ jonesde ]
        Michael Imhof made changes -
        Field Original Value New Value
        Attachment oraclePatch.txt [ 12357719 ]
        Michael Imhof created issue -

          People

          • Assignee:
            David E. Jones
            Reporter:
            Michael Imhof
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development