Handling Dates in a Legacy System

Mike Canham · **Posted:** Wed Aug 18, 2004 2:01 am

Hibernate version: 2.1.5

Database: Ingres 2.6

I have started this as a new topic despite briefly raising it under the "Trimming fixed length char fields" topic ...... apologies for the two threads but I felt it warranted its own thread.

I am trying to integrate to a legacy ingres database system.

Unfortunately, this system relies on the fact that an empty string can be successfully stored in a date field, to indicate that a date has not been stored (as opposed to making it a nullable field).

The existing statement can then execute commands such as :

Select max(lodgementDate) from xyz where lodgementDate != ''

Currently, when I read a date that has an empty string in it, I am getting "1970-01-01 00:00:00.0".

Mapping File :

Code:

<hibernate-mapping>
  <class name="wd.cl.client.ClClientPerson" table="cl_client_person" mutable="false">
    <composite-id>
      <key-property name="CClient" column="c_client" type="string" />
      <key-property name="NRow" column="n_row" type="integer" />
    </composite-id>
    
    <property name="TFormalName" column="t_formal_name" type="string"/>   
    <property name="DTo" column="d_to" type="date"/>               
  </class>
</hibernate-mapping>

I have temporarily created a UserType (TrimmedDateString) but I do not think that this is the real solution.

It relies on converting any dates read as "1970-01-01 00:00:00.0" to an empty string.

Code:

public class TrimmedDateString implements UserType { 
  public TrimmedDateString() { 
    super(); 
  } 

  public int[] sqlTypes() { 
    return new int[] { Types.DATE }; 
  } 

  public Class returnedClass() { 
    return String.class; 
  } 

  public boolean equals(Object x, Object y) throws HibernateException {
     return (x == y) || (x != null && y != null && (x.equals(y))); 
  } 

  public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { 
    String val = rs.getString(names[0]);
     
    if (null == val)
      return(null);
           
    if (val.trim().equals("1970-01-01 00:00:00.0"))
      return("");
               
     return(val.trim);        
   } 

  public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { 
    st.setString(index, (String)value); 
  } 

  public Object deepCopy(Object value) throws HibernateException {  
    if (value == null) 
      return null;

    return new String((String)value); 
  } 

  public boolean isMutable() { 
    return false; 
  } 
}

One problem with this, is that in my system I genuinely have some date set to "1970-01-01 00:00:00.0" and these would be seen as empty dates.

I did not try to update etc ..... but I am sure I would run into other problems .....

I considered changing the Database Dialect :

public class IngresDialect extends Dialect {

Code:

   
public IngresDialect() {
  super();
  registerColumnType( Types.BIT, "BYTE" );
  registerColumnType( Types.BIGINT, "INTEGER4" );
  registerColumnType( Types.SMALLINT, "INTEGER2" );
  registerColumnType( Types.TINYINT, "INTEGER1" );
  registerColumnType( Types.INTEGER, "INTEGER4" );
  registerColumnType( Types.CHAR, "CHAR(1)" );
  registerColumnType( Types.VARCHAR, "VARCHAR($l)" );
  registerColumnType( Types.FLOAT, "FLOAT" );
  registerColumnType( Types.DOUBLE, "DOUBLE PRECISION" );
  registerColumnType( Types.DATE, "DATE" );
  registerColumnType( Types.TIME, "DATE" );
  registerColumnType( Types.TIMESTAMP, "DATE" );
  registerColumnType( Types.VARBINARY, "VARBINARY($l)" );
  registerColumnType( Types.NUMERIC, "NUMERIC(19, $l)" );
  registerColumnType( Types.BLOB, "LONG VARCHAR" );
  registerColumnType( Types.CLOB, "LONG VARCHAR" );
  registerColumnType( Types.VARBINARY, "LONG VARCHAR" );
}

So that   

registerColumnType( Types.DATE, "DATE" ); 

became 

  registerColumnType( Types.DATE, "STRING" );

But I am sure this would cause be problems (eg Max, sort by etc ).

I accept that the legacy system is relying on an anomaly of the ingres database system, but there would be substantial owrk to go through the existing code and change it to use Nullable data fields etc ........

Any suggestions ??????

Mike.

emmanuel · **Posted:** Wed Aug 18, 2004 3:34 am

Why don't you consider null as a non defined date in you UserType ?

Mike Canham · **Posted:** Wed Aug 18, 2004 3:41 am

Emmanuel,

I do not understand your reply ........

I am reading a date column that is not nullable from the database.

It contains (from the legacy system) an empty string.

When I read the date, I get back a date of "1970-01-01 00:00:00.0" as hibernate can not return an empty string (under its current configuration).

If the database could return me null, I could do what I wanted .... but it is not a nullable column.

The problem, is that there is a vast amount of code supporting the legacy system and changing all of the date fields to nullable would be a very time consuming exercise.

Have I missed the point that you were raising ?????

Thanks,

Mike.

Mike Canham · **Posted:** Wed Aug 18, 2004 3:43 am

Emmanuel,

I should have mentioned, that the only reason that I have coded for the NULL in the UserType is in case we have new nullable date fields.

It will not get a Null value coming back from the legacy database.

Thanks,

Mike.

CSwinney · **Joined:** Mon Aug 16, 2004 6:15 am **Posts:** 24

I think what Emmanuel is getting at is why don't you use the UserType to translate between null on the java side, and empty string on the database side.

This way your java code will be able to distinguish between your case of "1970-01-01 00:00:00.0" as real data and the empty date in the database.

Mike Canham · **Posted:** Wed Aug 18, 2004 3:59 am

CSwinney,

How ?????

Lets just look at reading from the existing database :

Code:

  
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { 
   String val = rs.getString(names[0]); 
     
    if (null == val) 
      return(null); 
           
    if (val.trim().equals("1970-01-01 00:00:00.0")) 
      return(""); 
               
     return(val.trim);         
   } 

If I put in some debug code, to output the value of the empty string field read from the database, it does not return an empty string, but a string with "1970-01-01 00:00:00.0"

By which I mean, if I add in the statement

Code:

System.out.println("val is " + val)

just before 

if (val.trim().equals("1970-01-01 00:00:00.0")) 
    return("");

Then it prints out "1970-01-01 00:00:00.0"

And that is precisely my problem, I am not getting the empty string back from the rs.getString(names[0]); statement ..........

Is there some alternative that I am missing ??????

Thanks,

Mike.

CSwinney · **Joined:** Mon Aug 16, 2004 6:15 am **Posts:** 24

Is the data type in the database a varchar or date value? As you'd probably want to use rs.getDate() or rs.getTimeStamp() depending on implementation.

If the code is actually a varchar in the database, then your UserType should have

Code:

  public int[] sqlTypes() {
    return new int[] { Types.VARCHAR };
  } 

If indeed the database is using the value "1970-01-01 00:00:00.0" to represent the "empty date", then you've also got the rule by which you can never enter dates of that value.

Hope this helps.

Mike Canham · **Posted:** Wed Aug 18, 2004 6:44 pm

I changed the code to retrieve the column as a date field :

Code:

Date dateVal = rs.getDate(names[0]);

but it does not return null for an empty string (I am not surprised) and printing out its value (as a string) :

Code:

System.out.println("nullSafeGet: Date is " + dateVal.toString());

again gives the default date.

Whereas, writing my own JDBC interface and selecting :

Code:

varchar(d_form_received)

and

retrieving it as :

Code:

dFormReceived = rs.getString(23);

returns an empty string.

In conclusion, I am going to the same database and I am using the same jdbc class.

It would appear that Hibernate would be doing the mapping to the default date ......

Any suggestions as to how I can change this behaviour ?????

Thanks,

Mike.

CSwinney · **Joined:** Mon Aug 16, 2004 6:15 am **Posts:** 24

Actually I don't think it's the fault of Hibernate you're getting odd results here, it's more likely the fault of Java itsself and Date initialisation.

In your first example you're telling java to retrieve a date, the database will be returning this "empty string" value and leaves it up to java to interpret this as a valid date (as it's not null), this will more than likely lead java to instantiate a Date object set to be the epoch (which is what you're seeing).

The second example is forcing the database to perform the data type conversion, so by the time it gets to Java, it is the correct "empty string" you are expecting.

Out of curiosity, what happens when you don't use the varchar function in your plain jdbc test? Does it return the epoch or an empty string?

What happens if you treat the field as a string in jdbc (no using varchar to convert at the database end) - if this returns the empty string, then it's a matter of redefining your hibernate mappings to say the underlying column is a varchar. This will allow you to retrieve the data as expected, and you can then use the UserType to translate this into something that Java would find more useable (a Date which can be null).

Hope this helps,

Craig

Mike Canham · **Posted:** Sat Aug 21, 2004 4:38 am

Craig,

A work colleague also suggested that it could be the underlying java code.

I ran the following tests :

Code:

try {
  Date date = new Date("");
  System.out.println("date is " + date.toString());
}
catch (Exception e) {
  System.out.println("exception is " + e.getMessage());
}
 
This gave me an exception :  exception is null

I also tried :

Code:

try {
  SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
  Date date = sdf.parse("");
  System.out.println("date is " + date.toString());
}
catch (Exception e) {
System.out.println("exception is " + e.getMessage());
}

This gave me : exception is Unparseable date: ""

It appears that Java will generate an exception if you attempt to convert an empty string to a date (not an unreasonable response !!!!!).

I then tried a few tests with a JDBC connection :

The database column is declared as a date and contained an empty string.

I removed the varchar from the select statement .....

eg Select d_received from xyz

When I accessed the column as a string :

Code:

rs.getString(23)

Then I got an empty string (and when the column contains a date, I get the date as a string).

When I accessed the column as a date :

Code:

rs.getDate(23)

I got an exception : java.lang.IllegalArgumentException

I then returned back to my Hibernate mapping.

I modified the UserType to return CHAR and then VARCHAR (instead of date) and I got the default date back for an empty string (when accessing it as a string (rs.getString(names[0])),

If I declared the type as a Date

Code:

  
public int[] sqlTypes() { 
  return new int[] { Types.DATE }; 
} 

Then if I accessed the column as a string I got the default date (1970-01-01 00:00:00.0)

If I accessed the column as a date :

Code:

String val = rs.getDate(names[0]).toString();

Then I got the default date minus the time (1970-01-01).

In conclusion, I do not appear to be able to get an empty string out of Hibernate (using both createCriteria and createQuery). I believe this is because of the fact that Hibernate accesses the column directly using the column name provided in the mapping file :

Code:

and so to my final attempt (as a sudden idea from doumenting this) .......

I decided to try and declare the column as varchar(d_to) :

Code:

    <property name="DTo" column="varchar(d_to)" type="wd.qmdb.hibernate.TrimmedDateString"/>     

Expecting this to possibly work for just the read (and to fail on inserts etc) ....

But no, I got an exception : java.lang.Exception

The resulting HQL was :

Code:

clclientpe0_.varchar(d_to) as varchar(7_,

as opposed to :

Code:

clclientpe0_.d_to as d_to

If anyone has any other suggestions, can you please let me know ?????

Surely, I can not be the only person to have to contend with problem ?????

Thanks,

Mike.

Mike Canham · **Posted:** Mon Nov 01, 2004 10:07 pm

I have revisited this problem and I have to retract an earlier statement :

Quote:

The database column is declared as a date and contained an empty string.

I removed the varchar from the select statement .....

eg Select d_received from xyz

When I accessed the column as a string :

rs.getString(23)

Then I got an empty string (and when the column contains a date, I get the date as a string).

I have since retested and I have found that if I remove the varchar from the select (using JDBC) that I get the default date string.

Hence, Craig was correct in pointing out that the crucial thing with the JDBC driver was the fact that I cast the date using varchar (varchar(d_expiry)).

It is possible that different jdbc drivers will handle the empty string in a date field differently (eg it has been suggested that the Caribou Lakes JDBC driver may solve the problem).

Many Thanks,

Mike Canham.