Hi, can you please share any thoughts or guidelines regarding the following issue:

I have a varchar column in Oracle 10g, which stores datetime values, this column is mapped as a string in my mapping file. I'm creating a simpe query using this column:
...
Query query = hibernateSession.createQuery("from ELTLDetail as d where d.timeStamp >=?");
query.setString(0,"2007-04-17");
...

This code works perfectly with MySQL 5.X, but now that i'm running my app in Oracle 10G, it gives me the following error:

org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:77)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749)
at com.be.eagle.viewer.dao.MessageReportingDAOHibernateImpl.countRecords(MessageReportingDAOHibernateImpl.java:105)
at com.be.eagle.viewer.dao.MessageReportingDAOHibernateImpl.findEnterpriseMessages(MessageReportingDAOHibernateImpl.java:33)
at com.be.eagle.viewer.bo.MessageReportingBOImpl.searchMessages(MessageReportingBOImpl.java:37)
at com.be.eagle.viewer.bo.MessageReportingBODelegate.searchMessages(MessageReportingBODelegate.java:34)
at com.be.eagle.viewer.struts.action.SearchEnterpriseAction.execute(SearchEnterpriseAction.java:50)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:716)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: ORA-01861: literal does not match format string
...

All i've found about this, refers to issues with date format for date type colums, but i don't understand why this problem occurs for a varchar column mapped as string.

I'm using hibernate 3.1 with latest version of oracle jdbc driver for oracle

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Thanks a lot

Adolfo

Still, trying to figure out how to solve this.

I enabled show_sql property, and the query that is causing the error looks like this:

select count(*) as col_0_0_ from ELTL_DETAIL eltldetail0_ where eltldetail0_.timeStamp>=? order by eltldetail0_.timeStamp DESC

I translate it to:

select count(*) as col_0_0_ from ELTL_DETAIL eltldetail0_ where eltldetail0_.timeStamp>='2007-04-17' order by eltldetail0_.timeStamp DESC

and submit the query to oracle using an eclipse db tool, and it gives me the correct number of rows, without any error.

Is it may be that hibernate uses a a function like to_date or to_char under the covers? Or is the name of the column (timeStamp) that is causing the problem?

So i'm still struggling with this. Any help would be greatly appreciated. Thanks

Adolfo

Turn on debug logging and see what Hibernate is actually inserting into those parameters. I believe the parameters can be seen by debugging the org.hibernate.type package (someone correct me if I misremember).

It's because you are using a String instead of a Date. Replace


with


In Java, it's much better to deal with and store your dates as either the long primitive or Date objects. If you need to convert from that string to a Date object, use



Daniel

Thank you all for your comments. I'll try your suggestions.

My application, though, is just making selects on legacy tables that are updated by another application. Therefore i can't change the definition of the tables (change the column storing a timestamp from varchar to date).

Thanks

Adolfo

Sorry, I missed the part about the date field being stored as text in the database. Doing a text compare on a YYYY-MM-DD format string should work just fine too...

I have to go back to Ananasi said and recomend turning on debug output for Hibernate. You need a log4j.properties file in your classpath. I'll put my entire log4j.properties file below, but this is the important one (aside from having appenders setup):



My log4j.properties file:


If you still can't get it working, post the SQL that Hibernate logs.

Daniel

Thanks Daniel, Ananasi, i was able to determine what the problem was by adding this to my log4j.properties file:

log4j.logger.org.hibernate.type=DEBUG
log4j.logger.org.hibernate.SQL=DEBUG

This showed me that i was using query.setString(..) for one query but query.setDate(..) for another one. And the last one was the one causing the exception when using oracle instead of mysql.

Thanks again.

Adolfo

If our posts were helpful, giving us credit would be appreciated.

Daniel