-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
csak
 Post subject: Timstamp subtraction doesn't work in Hibernate 3.0.5 DB2
PostPosted: Wed Dec 07, 2005 12:25 pm 
Newbie

Joined: Wed Oct 29, 2003 5:45 am
Posts: 3
Location: Hungary
We have an annoying problem. The timestamp subtraction doesn't seem to work properly.

The hibernate query:

select cat.name,
avg(current_timestamp() - ai.creationDate),
avg(ai.closeDate - ai.creationDate)
from xxx.beans.Category as cat, xxx.ActionItem as ai
where ai.abstractTaskType = some elements(cat.taskTypes) and cat.parent.id=109 group by cat.name

Hibernate version: 3.0.5

Full stack trace of any exception that occurs:
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at xxx.Report.execute(Report.java:91)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0102E Invalid conversion. SQLSTATE=07006
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getTimestamp(WSJdbcResultSet.java:1993)
at org.hibernate.type.TimestampType.get(TimestampType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.hql.QueryLoader.getResultColumnOrRow(QueryLoader.java:357)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 77 more

Name and version of the database you are using: DB2 8.2

The generated SQL (show_sql=true):
select category0_.NAME as col_0_0_,
sum(current timestamp-actionitem1_.CREATION_DATE) as col_3_0_ from ITQUEUE.CATEGORY category0_, ITQUEUE.ACTION_ITEM actionitem1_ where actionitem1_.TEMPLATE in ('F', 'P') and actionitem1_.TASK_TYPE_ID=some (select tasktypes2_.TT_ID from ITQUEUE.TT_TO_CAT tasktypes2_ where category0_.ID=tasktypes2_.CAT_ID) and category0_.PARENT_ID=109 group by category0_.NAME

The generated SQL runs flawlessly on any SQL client, but when the Query is executed anywhere from Hibernate it throws the error above.

The configuration and mapping files are widely used from the project, and have no problem with them.
Top
 Profile  
 
steve
 Post subject:
PostPosted: Wed Dec 07, 2005 12:31 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Well considering the error comes from your JDBC driver, why not try running it directly through JDBC (I seriously doubt your "SQL client" runs via JDBC). Then you can ask IBM about your "annoying problem" :)
Top
 Profile  
 
csak
 Post subject:
PostPosted: Wed Dec 07, 2005 2:43 pm 
Newbie

Joined: Wed Oct 29, 2003 5:45 am
Posts: 3
Location: Hungary
steve wrote:
Well considering the error comes from your JDBC driver, why not try running it directly through JDBC (I seriously doubt your "SQL client" runs via JDBC). Then you can ask IBM about your "annoying problem" :)

FYI: I'm not that newbie. I'm running the query through SquirrelSQL using identical JDBC driver setup that I use in Hibernate. The query runs there. The IBM manuals have also been consulted already, even non-public ones, as I currently work for IBM.
Please try to help instead of flaming.
Top
 Profile  
 
steve
 Post subject:
PostPosted: Wed Dec 07, 2005 2:56 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Wow, you think that's flaming? You *must* work for IBM ;)
Top
 Profile  
 
steve
 Post subject:
PostPosted: Wed Dec 07, 2005 3:16 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1239
Top
 Profile  
 
jtblair
 Post subject: avg() vs sum()
PostPosted: Wed Dec 07, 2005 3:31 pm 
Newbie

Joined: Mon Jul 18, 2005 10:15 am
Posts: 17
In your generated SQL, there is a sum() while in your Hibernate query, there is an avg(). Can you have a double check?
HTH,
JB
Top
 Profile  
 
csak
 Post subject: Re: avg() vs sum()
PostPosted: Thu Dec 08, 2005 4:05 am 
Newbie

Joined: Wed Oct 29, 2003 5:45 am
Posts: 3
Location: Hungary
jtblair wrote:
In your generated SQL, there is a sum() while in your Hibernate query, there is an avg(). Can you have a double check?
HTH,
JB


Sorry, that's right. I've tried with all aggregate functions, without any luck, and the sample is from different runs. I've been struggling with this most annoying problem for a day, just to boil the problem down to simple timestamp arithmetic.

Here is another much simpler sample, in HSQL:

Code:
select ai.closeDate - ai.creationDate from xxx.ActionItem as ai


the generated SQL

Code:
select actionitem0_.CLOSE_DATE-actionitem0_.CREATION_DATE as col_0_0_ from ITQUEUE.ACTION_ITEM actionitem0_


with the generated expression:

Code:
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at xxx.execute(Report.java:91)
   ... 71 more
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0102E  Invalid conversion. SQLSTATE=07006
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
   at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp2(Unknown Source)
   at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp(Unknown Source)
   at COM.ibm.db2.jdbc.app.DB2ResultSet.getTimestamp(Unknown Source)
   at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getTimestamp(WSJdbcResultSet.java:1993)
   at org.hibernate.type.TimestampType.get(TimestampType.java:28)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.hql.QueryLoader.getResultColumnOrRow(QueryLoader.java:352)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   ... 77 more


As I see the JIRA ticket, it might actually be fixed in 3.1. I'll try to have a go on that to see, but won't be able to use it until stable 3.1 is released.

Isn't there a workaround we could use? (Our big problem, is, that the HSQL query is generated from a report definition and the result list is fed straight to JasperReports, there is no way we could modify the resulting dataset in between.)[/code]
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Dec 08, 2005 6:16 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
OK, enough with the whinging, which is getting far more "annoying" than the original problem.

Steve has actually already fixed this one especially for you, in less than 24 hours. And this right in the middle of pushing out a 3.1 final release.

So show him some gratitude.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.