[jira] Created: (OFBIZ-1001) Oracle Database Date Query Problems

[Nicolas Malin (Jira)]

Oracle Database Date Query Problems
-----------------------------------

                 Key: OFBIZ-1001
                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
             Project: OFBiz (The Open for Business Project)
          Issue Type: Wish
          Components: framework
         Environment: SUSE Linux 10 & Oracle DB
            Reporter: Michael Imhof
            Priority: Minor


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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Michael Imhof updated OFBIZ-1001:
---------------------------------

    Attachment: oraclePatch.txt

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497358 ]

Jacopo Cappellato commented on OFBIZ-1001:
------------------------------------------

Michael,

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

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497361 ]

Michael Imhof commented on OFBIZ-1001:
--------------------------------------

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.

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497362 ]

Jacopo Cappellato commented on OFBIZ-1001:
------------------------------------------

Michael,

thanks for providing further information on this.

Jacopo

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497529 ]

David E. Jones commented on OFBIZ-1001:
---------------------------------------

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.

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David E. Jones reassigned OFBIZ-1001:
-------------------------------------

    Assignee: David E. Jones

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>         Assigned To: David E. Jones
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497811 ]

Michael Imhof commented on OFBIZ-1001:
--------------------------------------

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

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>         Assigned To: David E. Jones
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-1001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12519082 ]

Si Chen commented on OFBIZ-1001:
--------------------------------

Is anyone working on this?

> Oracle Database Date Query Problems
> -----------------------------------
>
>                 Key: OFBIZ-1001
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1001
>             Project: OFBiz
>          Issue Type: Wish
>          Components: framework
>         Environment: SUSE Linux 10 & Oracle DB
>            Reporter: Michael Imhof
>            Assignee: David E. Jones
>            Priority: Minor
>         Attachments: oraclePatch.txt
>
>
> 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).

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.