timestamp in delete sql statement

Den Orlov · **Posted:** Tue Feb 10, 2004 1:35 pm

I am using JDK 1.4.2_02, Hibernate 2.1.2, Oracle 9.2.0 client and server, Oracle 9.2.0 OCI8 driver.

I've been trying to figure out the reason of primary key constraint violation in my code and found that this set of statements (from the log):

Code:

DEBUG  [net.sf.hibernate.collection.BasicCollectionPersister] Deleting rows of collection: [com.orionbilling.server.model.account.Subscription.networkIdentifiers#474]
DEBUG  [net.sf.hibernate.impl.BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
DEBUG  [net.sf.hibernate.SQL] delete from network_identifier where subscription_id=? and start_date=? and end_date=?
DEBUG  [net.sf.hibernate.impl.BatcherImpl] preparing statement
DEBUG  [net.sf.hibernate.type.LongType] binding '474' to parameter: 1
DEBUG  [net.sf.hibernate.type.TimestampType] binding '10 Февраль 2004 19:58:40' to parameter: 2
DEBUG  [net.sf.hibernate.type.TimestampType] binding null to parameter: 3
DEBUG  [net.sf.hibernate.impl.BatcherImpl] Adding to batch
DEBUG  [net.sf.hibernate.impl.BatcherImpl] Executing batch size: 1
DEBUG  [net.sf.hibernate.impl.BatcherImpl] success of batch update unknown: 0
DEBUG  [net.sf.hibernate.collection.BasicCollectionPersister] done deleting collection rows: 1 deleted

don't perform real deletion of row from 'network_identifier' table!

This is p6spy log that corresponds to the manipulations:

Code:

1076432323256|0|0|statement|delete from network_identifier where subscription_id=? and start_date=? and end_date=?|delete from network_identifier where subscription_id=474 and start_date='2004-02-10 19:58:40.0' and end_date=''

network_identifier table is:

Code:

create table network_identifier (
   subscription_id NUMBER(19,0) not null,
   imsi VARCHAR2(15),
   phone_number VARCHAR2(15),
   start_date DATE not null,
   end_date DATE,
   primary key (subscription_id, start_date) INITIALLY DEFERRED DEFERRABLE
);

Mappings is:

Code:

<class name="com.orionbilling.server.model.account.Subscription" table="subscription">

        <id name="id" column="id" type="java.lang.Long">
            <generator class="sequence"><param name="sequence">seq_subscription_id</param></generator>
        </id>

        <map name="networkIdentifiers" table="network_identifier" sort="com.orionbilling.common.TimeIntervalComp">
                <key column="subscription_id"/>
                
                <composite-index class="com.orionbilling.common.TimeInterval">
                        <key-property name="start" column="start_date"/>
                        <key-property name="end" column="end_date"/>
                </composite-index>

                <composite-element class="com.orionbilling.server.model.account.NetworkIdentifier">...</composite-element>
        <map>
        ...
</class>

And this is snippet of my classes:

Code:

public class TimeInterval implements Serializable {
        private Date start;
        private Date end;
        ...
}

public class Subscription {
        private Long id;
        private SortedMap networkIdentifiers;
        ...
}

I suggest that it's some problem with timestamps there but can't find what is really wrong.

Best Regards,
Den Orlov

gavin · **Posted:** Tue Feb 10, 2004 9:19 pm

Oracle 9 has buggy JDBC driver, as documented on our WIKI. In future, please search the forum for similar problems before posting TIA.

Den Orlov · **Posted:** Wed Feb 11, 2004 9:11 am

gavin wrote:

Oracle 9 has buggy JDBC driver, as documented on our WIKI. In future, please search the forum for similar problems before posting TIA.

Did you mean this page - http://hibernate.org/80.html or something more detailed?

I've tried to find something similar to my problem on the forum but without results.

I've updated my drivers onto 9.2.0.3 version but it didn't help.

Then I write a simple jbdc test. This code:

Code:

  ...
PreparedStatement st = c.prepareStatement("insert into network_identifier (subscription_id, start_date) values(?,?)");
st.setLong(1, id);
st.setTimestamp(2, new Timestamp(cur.getTime()));
st.executeUpdate();
c.commit();
st.close();
  ...
st = c.prepareStatement("delete from network_identifier where subscription_id=? and start_date=?");
st.setLong(1, id);
st.setTimestamp(2, new Timestamp(startDateFromInsertedRecord.getTime()));
st.execute();
l.debug("deleted count: " + st.getUpdateCount());
c.commit();
st.close();

works perfect. But this code doesn't delete inserted record:

Code:

  ...
PreparedStatement st = c.prepareStatement("insert into network_identifier (subscription_id, start_date) values(?,?)");
st.setLong(1, id);
st.setTimestamp(2, new Timestamp(cur.getTime()));
st.executeUpdate();
c.commit();
st.close();
  ...
st = c.prepareStatement("delete from network_identifier where subscription_id=? and start_date=? and end_date=?");
st.setLong(1, id);
st.setTimestamp(2, new Timestamp(startDateFromInsertedRecord.getTime()));
st.setNull(3, Types.TIMESTAMP);
st.execute();
l.debug("deleted count: " + st.getUpdateCount());
c.commit();
st.close();

I.e select by null timestamp doesn't work.

May be, of course, this is driver problem but how can I workaround it using Hibernate?

Den

Den Orlov · **Posted:** Wed Feb 11, 2004 11:08 am

Hm, may be I am not right but...

As I understand for database fileds with possible NULL values it's necessary to write queries this way:

Code:

where ((end_date is null) or (end_date=?)) and ((start_date is null) or (start_date=?))

But what Hibernate does is:

Code:

where end_date=? and start_date=?

Is it correct?

Den

emmanuel · **Posted:** Thu Feb 12, 2004 9:21 am

If you think it's an Hibernate bug, then raise it to JIRA, it won't be lost in the forum flood.

Den Orlov · **Posted:** Thu Feb 12, 2004 10:18 am

gavin · **Posted:** Thu Feb 12, 2004 12:29 pm

This is not a bug.

foo = null

is a PERFECTLY valid expression in both SQL and HQL. OK, it is almost never useful, but it is needed for language consistency. Hibernate queries behave the same as the underlying JDBC!