• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
  • Paul Clapham
Sheriffs:
  • paul wheaton
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Piet Souris
Bartenders:
  • Mike London

Why oracle keep complaining "Invalid number" when using hibernate

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I'm new to Hibernate and came up with a problem when using HQL.

The problem is I want to search records from Oracle database according a column called 'txn_date', which its type is 'Date', and my HQL like this

=========================================================================
<sql-query name="job.getJobByDate">
select {job.*} from Newsletter_Message job
where to_char( ateType,'yyyy-mm-dd hh24:mi') =
to_char(:jobDate, 'yyyy-mm-dd hh24:mi')
<return alias="job"
class="com.newsletter.domain.job.Job" />
</sql-query>
=========================================================================

and the method is :
=========================================================================
public List<Job> getJobByDate(final Date jobDate, final String dateType)
{
return (List<Job> getHibernateTemplate().executeFind(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
Query query = session.getNamedQuery("job.getJobByDate");
query.setString("dateType", dateType);
query.setDate("jobDate",jobDate);
return query.list();
}
});
}
=========================================================================

Hibernate will generate the SQL like this:
=========================================================================
select job.TXN_NO as TXN1_5_0_,
job.TXN_TYPE as TXN2_5_0_,
job.TXN_DATE as TXN3_5_0_,
from Newsletter_Message job
where to_char(?, 'yyyy-mm-dd hh24:mi') = to_char(?, 'yyyy-mm-dd hh24:mi')
=========================================================================

When execute, it keep complainning that
"org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query"....

Caused by: java.sql.SQLException: ORA-01722: invalid number

I'm so confused that why oracle keep complainning "Invalid number"? I changed the sql to another format:

where to_char( ateType,'yyyy-mm-dd hh24:mi') = :strDate

and set string to variable strDate but also got same exception.

Can anybody help me? Thanks a lot !!!
 
pengpeng lin
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried another SQL with and it goes well, the code is :
=========================================================================
public List<Job> getJobByDate(final Date jobDate, final String dateType) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
String strDate = DateUtil.convertDateToString(jobDate,
"yyyy-MM-dd HH:mm");
Query query = session.createQuery("from Job job where "
+ "to_char(" + dateType + ",'yyyy-mm-dd hh24:mi') = '"
+ strDate + "'");
return (List<Job> query.list();
}
});
}
=========================================================================

and Hibernate will generate SQL like this:
=========================================================================
select job0_.TXN_NO as TXN1_5_,
job0_.TXN_TYPE as TXN2_5_,
job0_.TXN_DATE as TXN3_5_,
from NEWSLETTER_MESSAGE job0_
where to_char(job0_.TXN_DATE, 'yyyy-mm-dd hh24:mi') = '2008-05-22 16:11'
=========================================================================

but the code seems such ugly , does anyone have other methods to avoid hardcode and database-binding ?

Thanks
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why are you treating your date type as a String?
 
pengpeng lin
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Because it is a column name, such as

to_date(txn_date, 'yyyy-mm-dd hh24:mi:ss') = another_string_date

so I think I should set it as String, am I right?

If it should be treated as Date format, how should I call the method? since the argument 'dateType' is string ?

Thank you very much for your quick response !
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic