Problems with Oracle's SYSDATE and SYSTIMESTAMP

nestorjb · **Joined:** Sun Oct 26, 2003 9:02 pm **Posts:** 90

Hi I'm trying to get the sysdate or systimestamp from an Oracle database.

If I use sysdate Hibernate is not retrieving the hour, minute, second part. I always get 12:00:00pm

If I use systimestamp I get the following exception:

org.hibernate.MappingException: No Dialect mapping for JDBC type: -101
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:192)
at org.hibernate.loader.custom.CustomLoader.getHibernateType(CustomLoader.java:161)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:131)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1678)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749)

Hibernate version: 3.1.3

Code between sessionFactory.openSession() and session.close():

Session session = null;
Date date;

try
{
session = sessionFactory.openSession ();
date = (Date) session.createSQLQuery ("SELECT SYSDATE CURRENT_DATE FROM DUAL").uniqueResult ();
}
finally
{
try
{
if (session != null) session.close ();
}
catch (HibernateException e2) {}
}

System.out.println (new SimpleDateFormat ("hh:mm:ss").format (date));

Name and version of the database you are using: Oracle 10.2.0.1

steckemetz · **Posted:** Mon Jul 10, 2006 2:23 am

Hi,

a much better way to retrieve the timestamps is to use HQL.
Here you have the functions:

current_date(), current_time(), current_timestamp()

see chapter: 14.9. Expressions

These functions are than mapped via the dialect to the correct
SQL-functions of the database vendor.

hope this helps.

nestorjb · **Joined:** Sun Oct 26, 2003 9:02 pm **Posts:** 90

Thanks for the information

The only problem is that in Hibernate there is no DUAL table and I can't create a query without the from clause. So how can I create a query that just returns the current timestamp. The only way I figured out is to use any mapped table but the query will returns N dates depending on the number of rows in the table.

mojarrell · **Posted:** Mon Jul 10, 2006 2:24 pm

This worked for me under Oracle:

Code:

    public Date getDate() {
        Session session = getHibernateTemplate().getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("select sysdate as mydate from dual");
        query.addScalar("mydate", Hibernate.TIMESTAMP);
        return (Date) query.uniqueResult();
    }

steckemetz · **Posted:** Tue Jul 11, 2006 2:34 am

We are using another solution to this.
We found it when examining the code hibernate
uses for createing timestamps:

Code:

org.hibernate.type.DbTimestampType type = new DbTimestampType();
Timestamp ts = (Timestamp) type.seed((SessionImplementor)session);

This uses the hibernate dialect and retrieves only one row
from the database.

mojarrell · **Posted:** Tue Jul 11, 2006 8:24 am

That's cool. Thanks for sharing that!