File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Why oracle keep complaining Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Why oracle keep complaining "Invalid number" when using hibernate" Watch "Why oracle keep complaining "Invalid number" when using hibernate" New topic
Author

Why oracle keep complaining "Invalid number" when using hibernate

pengpeng lin
Greenhorn

Joined: Dec 24, 2005
Posts: 26
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

Joined: Dec 24, 2005
Posts: 26
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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why are you treating your date type as a String?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
pengpeng lin
Greenhorn

Joined: Dec 24, 2005
Posts: 26
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 !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why oracle keep complaining "Invalid number" when using hibernate