You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Michael Imhof (JIRA)" <ji...@apache.org> on 2007/05/21 08:27:16 UTC
[jira] Created: (OFBIZ-1001) Oracle Database Date Query Problems
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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Si Chen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "David E. Jones (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Jacopo Cappellato (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Assigned: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "David E. Jones (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Jacopo Cappellato (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Michael Imhof (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Michael Imhof (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OFBIZ-1001) Oracle Database Date Query Problems
Posted by "Michael Imhof (JIRA)" <ji...@apache.org>.
[ 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.