7:41 a.m.
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate3
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:45 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Frederic Leitenberger commented on HHH-2967:
--------------------------------------------
I found a workaround:
-- Create another function
create or replace function trunc_date(timestamp_ in timestamp) return date as
begin
return trunc(cast(timestamp_ as date));
end;
/
-- Use this in the Forumla (avoiding the cast):
@Formula(value = "trunc_date(created)")
public Date getDate() {
return date
}
This doesn't work with overloading, since the datatypes in oracle seem to be
overloaded themselfe and the original function is called instead of my new function.
Unless one knows the exact datatypes to use it won't work with overloading (namely
using the "trunc" name for the function).
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate3
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:54 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Frederic Leitenberger commented on HHH-2967:
--------------------------------------------
Ahh i forgot to say:
I also tried using this:
@Formula(value = "trunc_date(cast(created as _date))")
It generates the SQL correctly:
trunc(cast(eventlog0_.created as _date)) as formula6_0_
But of course the DB doesn't understand it anymore.
So it seems to be a problem concerning sloely "date".
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate3
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5:44 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Gail Badner commented on HHH-2967:
----------------------------------
Please attach a runnable test case (Java + mapping).
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate3
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:15 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Frederic Leitenberger updated HHH-2967:
---------------------------------------
Attachment: testcase.jar
Here is the testcase.
I added my .classpath-file (from eclipse) including a list of all used jar-files.
You need to create the schema like this:
create user testuser identified by test default tablespace users;
alter user testuser quota unlimited on users;
grant connect, resource to testuser;
create table testuser.TestEntity (id number(19,0) not null, created timestamp not null,
version number(10,0), primary key (id));
create sequence testuser.hibernate_sequence;
Then simply start de.meinebasis.hibernate.test.OracleCastTest main().
I get this:
Hibernate:
select
this_.id as id0_0_,
this_.created as created0_0_,
this_.version as version0_0_,
trunc(cast(this_.created as this_.date)) as formula0_0_
from
testuser.TestEntity this_
11.12.2007 10:57:33 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNUNG: SQL Error: 905, SQLState: 42000
11.12.2007 10:57:33 org.hibernate.util.JDBCExceptionReporter logExceptions
SCHWERWIEGEND: ORA-00905: missing keyword
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could
not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at de.meinebasis.hibernate.test.OracleCastTest.main(OracleCastTest.java:58)
Caused by: java.sql.SQLException: ORA-00905: missing keyword
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at
oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
at
oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 6 more
The error is here:
trunc(cast(this_.created as this_.date)) as formula0_0_
It should be:
trunc(cast(this_.created as date)) as formula0_0_
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate3
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Attachments: testcase.jar
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:34 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Michael Pätzold commented on HHH-2967:
--------------------------------------
Hi,
the same problem is existing in SQLServer SQLs, i.e.:
CONVERT(datetime, fieldXyz, 103)
is converted to
CONVERT (this_.datetime, this_.fieldXyz, 103)
-----------
A very very ugly workaround is using...
CONVERT([datetime], fieldXyz, 103)
in combination with
specifying MySQLDialect.java.
Somehow the expression is not converted to [this_.datetime]. But when I use
SQLServerDialect, the brackets will be removed and this_. will be added again.
Why does the converter not recognize the key words?
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate Core
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Attachments: testcase.jar
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:36 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
Arturo Frappé commented on HHH-2967:
------------------------------------
As Michael Pätzold says, it seems that are missing keywords in SybaseDialect
To address this exlcusive situation, where you have a formula with CONVERT(DATETIME, ....)
I suggest add the offending keyword to the list of keywords in the corresponding Dialect
class.
I'll put a patch as example.
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate Core
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Attachments: testcase.jar
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:44 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=c...
]
James Dasher commented on HHH-2967:
-----------------------------------
This issue also exists for casting to a NUMBER in Oracle. The workaround is to cast to
NUMBER(x,y).
where the following:
@Formula(value=" cast(MYFIELD as NUMBER )")
failed,
@Formula(value=" cast(MYFIELD as NUMBER(10,0) )")
worked.
Cast to date in Formula still doesn't work
------------------------------------------
Key: HHH-2967
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
Project: Hibernate Core
Issue Type: Bug
Components: query-criteria, query-hql, query-sql
Affects Versions: 3.2.5
Environment: Hibernate-Version: 3.2.5.ga
Oracle 9i/10g
Reporter: Frederic Leitenberger
Priority: Minor
Attachments: testcase.jar
Similar to HHH-473.
Oracle 10g supports trunc(TS). (TS is a timestamp)
Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
Therefore i need to add the cast to the Forumla.
@Basic
@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@Formula(value = "trunc(cast(created as date))")
public Date getDate() {
return date
}
public void setDate(Date date {
this.date= date
}
This results in the following query:
select
eventlog0_.id as id9_0_,
eventlog0_.created as created9_0_,
.........
trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
from
ICCS6.EventLog eventlog0_
where
eventlog0_.id=?
The alias in front of "date" is obviously misplaced there.
I also tried renaming the formula (since it was called "date"), but this
didn't change the result.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5379
days inactive
6070
days old
hibernate-issues@lists.jboss.org
7 comments
5 participants
participants (5)
-
Arturo Frappé (JIRA) -
Frederic Leitenberger (JIRA)
-
Gail Badner (JIRA)
-
James Dasher (JIRA)
-
Michael Pätzold (JIRA)