Oracle datetime mapping Hibernate bug?

rcosio · **Joined:** Tue Jan 02, 2007 11:08 am **Posts:** 7

Hello everybody, I've been working with hibernate for almost two years and I love it, however recently I found this behavior trying to map a java.util.Date field to a DATE oracle field and the database is apparently corrupted after inserting new rows (I'm using hibernate 3.2.1), tried almost every possible combination but it's very strange...

I have the following table (territoryDocument) in oracle 8.1.6:

Code:

Describing territorydocument....
NAME                            Null?     Type
------------------------------- --------- -----
ID_TERRITORYDOCUMENT            NOT NULL  NUMBER(*,0)
ID_TERRITORY                              NUMBER(*,0)
ID_GENERICFILE                            NUMBER(*,0)
DOCUMENTDATE                              DATE
DESCRIPTION                               VARCHAR2(128)

If I use this mapping:

Code:

<hibernate-mapping package="com.msd.database.sfs">
    <class
        name="TerritoryDocument"
        table="TERRITORYDOCUMENT">
        <id
            name="id_territoryDocument"
            column="ID_TERRITORYDOCUMENT">
            <generator class="sequence">
                <param name="sequence">id_territoryDocument</param>
            </generator>
        </id>
        <many-to-one
            name="territory"
            class="Territory"
            column="ID_TERRITORY"/>
        <many-to-one
            name="genericFile"
            class="GenericFile"
            column="ID_GENERICFILE"/>
        <property name="documentDate"   column="DOCUMENTDATE"   type="timestamp"/>
        <property name="description"    column="DESCRIPTION"    type="string"/>
    </class>
</hibernate-mapping>

and inserting with this code:

Code:

        Session session = HibernateUtil.newSession();
        try {
            Transaction tx = session.beginTransaction();
            
            TerritoryDocument td = new TerritoryDocument();
            td.setDocumentDate(new java.util.Date());
            session.save(td);
            
            tx.commit();
        } finally {
            session.close();
        }

The following is returned running a select * from territoryDocument in sqlplus:

Code:

id_territoryDocument                  documentDate
1458                                     31/07/2007 02:28:10 p.m.
1457                                     31/07/2007 02:27:18 p.m.
-0.00000000000000000000000000B3:1:1      31/07/2007 03:35:18 p.m.

As you can see id_territoryDocument value is apparently corrupted in the third row (and "apparently" because if I set documentDate to null into this row the correct value for id_territoryDocument is displayed, in this case should be 1459). But if I declare the following mapping (just moving documentDate declaration before many-to-one tag):

Code:

<hibernate-mapping package="com.msd.database.sfs">
    <class
        name="TerritoryDocument"
        table="TERRITORYDOCUMENT">
        <id
            name="id_territoryDocument"
            column="ID_TERRITORYDOCUMENT">
            <generator class="sequence">
                <param name="sequence">id_territoryDocument</param>
            </generator>
        </id>
        <many-to-one
            name="territory"
            class="Territory"
            column="ID_TERRITORY"/>
        <property name="documentDate"   column="DOCUMENTDATE"   type="timestamp"/>
        <many-to-one
            name="genericFile"
            class="GenericFile"
            column="ID_GENERICFILE"/>
        <property name="description"    column="DESCRIPTION"    type="string"/>
    </class>
</hibernate-mapping>

The new row is inserted properly:

Code:

1458                                     31/07/2007 02:28:10 p.m.
1457                                     31/07/2007 02:27:18 p.m.
-0.00000000000000000000000000B3:1:1      31/07/2007 03:35:18 p.m.
1460                                     31/07/2007 03:41:05 p.m.

Has someone experimented an error like this before?

bradsdavis · **Joined:** Fri Apr 07, 2006 11:29 am **Posts:** 17

Can you see the sequence.nextVal called when you up the Hibernate Logging prior to the save?

rcosio · **Joined:** Tue Jan 02, 2007 11:08 am **Posts:** 7

Yes, this is the log after an insert (wrong insert case):

Code:

16:51:54,260 DEBUG SQL:393 - select id_territoryDocument.nextval from dual
16:51:54,421 DEBUG SQL:393 - insert into TERRITORYDOCUMENT (ID_TERRITORY, ID_GENERICFILE, DOCUMENTDATE, DESCRIPTION, ID_TERRITORYDOCUMENT) values (?, ?, ?, ?, ?)

Table:

Code:

1458                                     31/07/2007 02:28:10 p.m.
1457                                     31/07/2007 02:27:18 p.m.
-0.00000000000000000000000000B3:1:1      
1460                                     31/07/2007 03:41:05 p.m.
-2.8:1:1E-129                            31/07/2007 04:51:54 p.m.

thatmikewilliams · **Posted:** Tue Jul 31, 2007 6:37 pm

What do you get back if you read the data with hibernate? (restarting or making sure all caches flushed beforehand). i.e. could be a weird bug in sqlplus. Were all 3 rows inserted by the same code and only 1 is corrupt? I'm not an oracle expert but is it ok to insert into an oracle DATE column using the timestamp type? Hibernate has a "date" type too.

rcosio · **Joined:** Tue Jan 02, 2007 11:08 am **Posts:** 7

If I run this test:

Code:

List<TerritoryDocument> list = session.createQuery("from TerritoryDocument").list();
            for (TerritoryDocument td : list)
                System.out.println("id_territoryDocument=" + td.getId_territoryDocument() + ", documentDate=" + td.getDocumentDate());

The following error is generated:

Code:

17:55:58,854  INFO IntegerType:178 - could not read column value from result set: ID1_68_; -82
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: -82
        at oracle.sql.LnxLibThin.lnxnuc(LnxLibThin.java:6010)
        at oracle.sql.NUMBER.toInt(NUMBER.java:414)
        at oracle.jdbc.dbaccess.DBConversion.NumberBytesToInt(DBConversion.java:2959)
        at oracle.jdbc.driver.OracleStatement.getIntValue(OracleStatement.java:4596)
        at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:536)
        at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1595)
        at org.hibernate.type.IntegerType.get(IntegerType.java:28)
        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
        at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
        at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)
        at org.hibernate.loader.Loader.doQuery(Loader.java:689)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2211)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
        at org.hibernate.loader.Loader.list(Loader.java:2090)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at test.GenericFileTest.main(GenericFileTest.java:39)

About the possible bug in sqlplus: actually I mentioned sqlplus because everybody knows this tool, but these querys where run with SQLNavigator, I ran the same query with sqlplus and it freezes before displaying the last row, CPU goes to 100% (had to kill the app). Also ran the query with the JDBC connection in NetBeans 5.5.1, it displays similar weird values:

Code:

1458   NULL   NULL   2007-07-31 14:28:10.0   NULL
1457   NULL   NULL   2007-07-31 14:27:18.0   NULL
-6.5463E-24   NULL   NULL   NULL   NULL
1460   NULL   NULL   2007-07-31 15:41:05.0   NULL
-2.8E-129   NULL   NULL   2007-07-31 16:51:54.0   NULL

The reason I selected timestamp type is because if I use a date mapping it only inserts year/month/day and timestamp includes hh:mm:ss, in Oracle 9 documentation there's a TIMESTAMP type that supports time up to milliseconds, the oracle I'm using (8.1.6) doesn't have this type but a DATE type supports hh:mm:ss, I was resigned to use a NUMBER(19) and store the value returned in java.util.Date.getTime() but
would be difficult to do some queries outside Hibernate (like all values of 2007). With the lack of a TIMESTAMP type in oracle I was wondering if I was using oracle 9 jdbc driver in oracle 8 and this was the source of the problem (somehow internally trying to save to an unsupported TIMESTAMP type) but then I discovered that declaring my documentDate property in a different order in the XML file worked, so now I'm wondering if this problem is related to Hibernate...

thatmikewilliams · **Posted:** Tue Jul 31, 2007 8:57 pm

Perhaps JDBC tracing, if it showed the values of query parameters for example, would be useful to determine if the problem is with hibernate or oracle.

rcosio · **Joined:** Tue Jan 02, 2007 11:08 am **Posts:** 7

Not good news for me. Enablind JDBC trace lead me to the class TimestampType, I noticed in the source there's also a DbTimestampType class with the following comment:

Code:

/**
 * <tt>dbtimestamp</tt>: An extension of {@link TimestampType} which
 * maps to the database's current timestamp, rather than the jvm's
 * current timestamp.
 * <p/>
 * Note: May/may-not cause issues on dialects which do not properly support
 * a true notion of timestamp (Oracle < 8, for example, where only its DATE
 * datatype is supported).  Depends on the frequency of DML operations...
 *
 * @author Steve Ebersole
 */

I tried using this type but didn't work, so now I realize my problem has no solution, sometimes it works because it works and sometimes not. I think the only solution (besides upgrading to oracle 9) is to manually update this field with session.createSQLQuery() to do an "update ... set documentDate=TO_DATE('....', 'DD/MM/YYYY HH24:MI:SS')", it would be nice if I could create a UserType but nullSafeSet() works with a PreparedStatement and I think there's no way to pass a SQL function with some setXXX method, is there?

thatmikewilliams · **Posted:** Thu Aug 02, 2007 5:44 pm

You might try using a UserType that sends the date as a String. I just tested setting a datetime field on MYSQL with a string and it does the conversion automatically. Probably will affect the DDL generated by hibernate incase your using that.

joseblas · **Joined:** Wed Feb 13, 2008 5:37 am **Posts:** 1

Hi mates,

I have the same problem. Inserts are ok in my oracle8, but when I try ti get my object I have no objects to "hour", leaving just the "date", ie 12/2/2008, it works fine.

Any idea?

rcosio · **Joined:** Tue Jan 02, 2007 11:08 am **Posts:** 7

After lots of reading I realized this is actually a bug with Oracle 8i, the workaround I used is this (of course the best solution is upgrade your oracle):

1. Map your dates as timestamp and tell hibernate to ignore your fields for insert and update:

Code:

<property name="myDate" type="timestamp" column="myDate" insert="false" update="false"/>

2. Update your date normally:

Code:

myPojo.setField1(...);
myPojo.setField2(...);
myPojo.setMyDate(new java.util.Date());

3. Update in the database manually:

Code:

myPojo.saveMyDate(session);

where saveMyDate() is something like this:

Code:

public void saveMyDate(org.hibernate.Session session) throws java.sql.SQLException
    {
        java.sql.Connection conn = session.connection();
        java.sql.Statement stmt = conn.createStatement();
        try {
            String s = "update MyPojo set myDate=" + Misc.toOracleDateTime(expirationDate) + " where id_myPojo=" + getId_myPojo();
            stmt.executeUpdate(s);
        } finally {
            stmt.close();
        }
    }

your java.sql.Connection comes from session.connection() so you can be sure your update belongs to the same transaction as your hibernate session.
Misc.toOracleDateTime() is just a static method that returns a "to_date()" oracle call:

Code:

public static String toOracleDateTime(java.util.Date d)
    {
        if (d == null)
            return "null";
        
        Calendar c = Calendar.getInstance();
        c.setTime(d);
        int year = c.get(Calendar.YEAR);
        int month = c.get(Calendar.MONTH) + 1;
        int day = c.get(Calendar.DATE);
        int hour = c.get(Calendar.HOUR_OF_DAY);
        int min = c.get(Calendar.MINUTE);
        int sec = c.get(Calendar.SECOND);
        StringBuffer sb = new StringBuffer(55);
        sb.append("TO_DATE('");
        sb.append(Misc.formatInt(day, '0', 2));
        sb.append("/");
        sb.append(Misc.formatInt(month, '0', 2));
        sb.append("/");
        sb.append(Misc.formatInt(year, '0', 4));
        sb.append(" ");
        sb.append(Misc.formatInt(hour, '0', 2));
        sb.append(":");
        sb.append(Misc.formatInt(min, '0', 2));
        sb.append(":");
        sb.append(Misc.formatInt(sec, '0', 2));
        sb.append("', 'DD/MM/YYYY HH24:MI:SS')");
        return sb.toString();
    }

Misc.formatInt() is a simple method to covert an int to a string with 0's to the left.

If your pojo is a new object you need to flush your session to guarantee it will be inserted before you update, otherwise you will try to update an object not yet written in db:

Code:

MyPojo p = new MyPojo();
...
session.save(p);
session.flush();
p.saveMyDate(session);

I'm sure there must be more elegant ways to do this but it was a fast solution for me and now that I'm running oracle 9 don't need to do this anymore...

tiagoaugusto · **Joined:** Mon Aug 28, 2006 4:43 pm **Posts:** 6

Hello Guys, i have the same error. I am using oracle 8i and i have some timestamp mapped, but i erros still persiste. Do i have to make my timestamp dates a distinct update? or have a better way?

Cameron McKenzie · **Posted:** Fri Jul 25, 2008 3:34 am

This JavaRanch thread might be enlightening:

http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=78&t=004116

Quote:

Managing dates as lookup objects (keys) in Java is tricky. The java.sql.Date has a granularity of 1 day, but it's based on java.util.Date, which has a granularity of 1 millisecond. Oracle Dates have a granularity of 1 second. I can personally attest to the grief that this can cause, both with JDO and with OpenJPA. So the date you write may not be the date you read.

In fact, this such a nuisance that I opened an issue on the OpenJPA documentation, requesting that the documentation devote a section to explaining this problem and how to avoid it.

"Operation attempted on a deleted instance" refers to OpenJPA's understanding of the data, not what's actually in the database. If a delete has been scheduled but not yet committed to the database, for example, you'd get that message.

While you might not think you'd deleted the record, problems with data granularity might be to blame. An date in memory could have been written out the the database, purged from local memory, then the date read back in from the database truncated (in Oracle's case, to seconds). This might cause it to look like another date, possibly also truncated. In other words, something like a hash collision. The first object might have been explicitly deleted, but since both objects had the same date, deleting the second object would appear to be an attempt to delete the first object for a second time.

facisumlo · **Posted:** Tue Nov 25, 2008 4:37 am

Hi, we faced the same issue a couple of days ago (Oracle 8i never dies...) and worked out a different workaround. We "removed" the milliseconds from the Calendar object and it worked for us.

Old code:

Code:

product.setCancelledAt(Calendar.getInstance());

Workaround:

Code:

Calendar cancelledAt = Calendar.getInstance();
cancelledAt.set(Calendar.MILLISECOND, 0);
product.setCancelledAt(cancelledAt);

At least it works for us.

rcosio wrote:

After lots of reading I realized this is actually a bug with Oracle 8i, the workaround I used is this (of course the best solution is upgrade your oracle):

1. Map your dates as timestamp and tell hibernate to ignore your fields for insert and update:

Code:

<property name="myDate" type="timestamp" column="myDate" insert="false" update="false"/>

2. Update your date normally:

Code:

myPojo.setField1(...);
myPojo.setField2(...);
myPojo.setMyDate(new java.util.Date());

3. Update in the database manually:

Code:

myPojo.saveMyDate(session);

where saveMyDate() is something like this:

Code:

public void saveMyDate(org.hibernate.Session session) throws java.sql.SQLException
    {
        java.sql.Connection conn = session.connection();
        java.sql.Statement stmt = conn.createStatement();
        try {
            String s = "update MyPojo set myDate=" + Misc.toOracleDateTime(expirationDate) + " where id_myPojo=" + getId_myPojo();
            stmt.executeUpdate(s);
        } finally {
            stmt.close();
        }
    }

your java.sql.Connection comes from session.connection() so you can be sure your update belongs to the same transaction as your hibernate session.
Misc.toOracleDateTime() is just a static method that returns a "to_date()" oracle call:

Code:

public static String toOracleDateTime(java.util.Date d)
    {
        if (d == null)
            return "null";
        
        Calendar c = Calendar.getInstance();
        c.setTime(d);
        int year = c.get(Calendar.YEAR);
        int month = c.get(Calendar.MONTH) + 1;
        int day = c.get(Calendar.DATE);
        int hour = c.get(Calendar.HOUR_OF_DAY);
        int min = c.get(Calendar.MINUTE);
        int sec = c.get(Calendar.SECOND);
        StringBuffer sb = new StringBuffer(55);
        sb.append("TO_DATE('");
        sb.append(Misc.formatInt(day, '0', 2));
        sb.append("/");
        sb.append(Misc.formatInt(month, '0', 2));
        sb.append("/");
        sb.append(Misc.formatInt(year, '0', 4));
        sb.append(" ");
        sb.append(Misc.formatInt(hour, '0', 2));
        sb.append(":");
        sb.append(Misc.formatInt(min, '0', 2));
        sb.append(":");
        sb.append(Misc.formatInt(sec, '0', 2));
        sb.append("', 'DD/MM/YYYY HH24:MI:SS')");
        return sb.toString();
    }

Misc.formatInt() is a simple method to covert an int to a string with 0's to the left.

If your pojo is a new object you need to flush your session to guarantee it will be inserted before you update, otherwise you will try to update an object not yet written in db:

Code:

MyPojo p = new MyPojo();
...
session.save(p);
session.flush();
p.saveMyDate(session);

I'm sure there must be more elegant ways to do this but it was a fast solution for me and now that I'm running oracle 9 don't need to do this anymore...