Bug #5235 Connector/J reurns wrong type for Date
Submitted: 26 Aug 2004 16:36 Modified: 22 Nov 2004 15:55
Reporter: M Wensink Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:snapshot 26-8-2004 OS:Windows (w2k)
Assigned to: Mark Matthews CPU Architecture:Any

[26 Aug 2004 16:36] M Wensink
Description:
I have set the option zeroDateTimeBehavior to convertToNull.
My database table contains a Date field with value 0000-00-00
When I use getObject() on the ResultSet object a value of type String ("0000-00-00") is returned. This should be null.

How to repeat:
Obvious

Suggested fix:
Adding

         if (field.getSQLType() == Types.DATE) {
            return getDate(columnIndex);
         }

after the special case for type BIT in ResultSet.getObject() solves the problem, but maybe there is a better solution?
[31 Aug 2004 1:07] Mark Matthews
The following testcase doesn't demonstrate this behavior, am I doing something different than you are? You don't happen to have the field actually defined as a character type, do you?

public void testBug5235() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
    		this.stmt.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
    		this.stmt.executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");
    		
    		Properties props = new Properties();
    		props.setProperty("zeroDateTimeBehavior", "convertToNull");
    		
    		Connection nullConn = getConnectionWithProps(props);
    		
    		this.rs = nullConn.createStatement().executeQuery("SELECT field1 FROM testBug5235");
    		this.rs.next();
    		assertTrue(null == this.rs.getObject(1));
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
    	}
    }
[31 Aug 2004 23:37] M Wensink
The problem is with the SELECT query. If I use a PreparedStatement, the system generates a ClassCastException.

If you can't produce the error, I can send you my test program.

Regards Marten
[31 Aug 2004 23:55] Mark Matthews
Please attach your testcase on the 'Files' tab, and mark it as private. 

Make sure it is a _standalone_ testcase, i.e. it creates/drops and populates the tables.
[19 Sep 2004 21:24] M Wensink
I added a tescase on september the first.
[19 Sep 2004 21:39] Mark Matthews
The fix to this is already on my local drive. I will be doing some more testing and comitting the change on Monday.