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

        Hide
        Si Chen added a comment -

        Is anyone working on this?

        Show
        Si Chen added a comment - Is anyone working on this?
        Hide
        Michael Imhof added a comment -

        I agree that using PreparedStatement should be the way to go.

        Show
        Michael Imhof added a comment - I agree that using PreparedStatement should be the way to go.
        Hide
        David E. Jones added a comment -

        I'll take a look at this sometime soon. We have a totally different approach for SQL variations in databases with configuration in the datasource elements in the entityengine.xml file, rather than having sets of database specific code.

        This should at least be changed to be done that way... IF we decide to go this route.

        Still, I think this may just be an issue of not using a PreparedStatement when we should be and fixing that will help/fix everything. So THAT is what I plan to look into and see if it resolves the issue.

        Show
        David E. Jones added a comment - I'll take a look at this sometime soon. We have a totally different approach for SQL variations in databases with configuration in the datasource elements in the entityengine.xml file, rather than having sets of database specific code. This should at least be changed to be done that way... IF we decide to go this route. Still, I think this may just be an issue of not using a PreparedStatement when we should be and fixing that will help/fix everything. So THAT is what I plan to look into and see if it resolves the issue.
        Hide
        Jacopo Cappellato added a comment -

        Michael,

        thanks for providing further information on this.

        Jacopo

        Show
        Jacopo Cappellato added a comment - Michael, thanks for providing further information on this. Jacopo
        Hide
        Michael Imhof added a comment -

        I copied the idea and not the code.

        I used the same class names as hibernate uses (DialectFactory, OracleDialect, MySqlDialect).
        Even the static table in DialectFactory (MAPPERS) has the same name as the one in hibernate.

        Show
        Michael Imhof added a comment - I copied the idea and not the code. I used the same class names as hibernate uses (DialectFactory, OracleDialect, MySqlDialect). Even the static table in DialectFactory (MAPPERS) has the same name as the one in hibernate.
        Hide
        Jacopo Cappellato added a comment -

        Michael,

        thanks for your contribution.
        Just to be sure: no code has been copied from Hibernate, right?

        Show
        Jacopo Cappellato added a comment - Michael, thanks for your contribution. Just to be sure: no code has been copied from Hibernate, right?

          People

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

            Dates

            • Created:
              Updated:

              Development