| Bug #57808 | getDate returns null, but wasNull returns false using convertToNull | ||
|---|---|---|---|
| Submitted: | 28 Oct 2010 13:36 | Modified: | 15 Apr 2011 15:30 |
| Reporter: | Christian Peter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | convertToNull, getDate, wasNull, zeroDateTimeBehavior | ||
[2 Mar 2011 13:54]
Christian Peter
Hi,
i just wanted to ask if you need more information about this case. And i found a small bug in my example. Here the correct code for the problem:
String stringDate = null;
Date theDate = resultset.getDate(columnIndex);
if( resultset.wasNull() == true ) {
stringDate = "null";
} else {
stringDate = theDate.toString();
}
[22 Mar 2011 6:59]
Tonci Grgin
Christian, according to JDBC specs, your expectations are correct. Please attach small but *complete* test case so I can check for problem here.
[28 Mar 2011 9:03]
Christian Peter
sample code to reproduce the behaviour
Attachment: MySQLDateTest.java (application/octet-stream, text), 2.08 KiB.
[28 Mar 2011 9:06]
Christian Peter
Tonci i added a complete testcase to reproduce. The commands to create the necessary table with a single row are: DROP TABLE FOO; CREATE TABLE FOO ( ID INT(3) NOT NULL, ADate DATE NOT NULL, PRIMARY KEY (ID) ); INSERT INTO FOO(ID, ADate) VALUES(1, 0000-00-00); The javacode for the testcase is in the attachment.
[13 Apr 2011 14:33]
Tonci Grgin
Christian, DATE field is marked NOT NULL so this is probably a culprit here. Testing.
[13 Apr 2011 14:45]
Tonci Grgin
So, changing test case as follows produces expected result:
createTable("bug57808", "(ID INT(3) NOT NULL PRIMARY KEY, ADate DATE DEFAULT NULL)");
Properties props = new Properties();
props.put("zeroDateTimeBehavior", "convertToNull");
Connection conn1 = null;
conn1 = getConnectionWithProps(props);
Statement stmt1 = conn1.createStatement();
stmt1.executeUpdate("INSERT INTO bug57808(ID, ADate) VALUES(1, NULL)");
...
Connected to 5.1.31-log
java.vm.version : 1.5.0_17-b04
java.vm.vendor : Sun Microsystems Inc.
java.runtime.version : 1.5.0_17-b04
os.name : Windows Server 2008
os.version : null
sun.management.compiler : HotSpot Client Compiler
value of date is null
OK, value is null and wasNull returns true
I have to think about this since wasNull is expected to check for *database* value of NULL, not something driver converted to NULL.
[13 Apr 2011 14:51]
Tonci Grgin
If .getDate() returns null, then .wasNull() should be true too. Verified as described.
[15 Apr 2011 15:30]
Tonci Grgin
Pushed up to revision 1057.
Description: I have set the option zeroDateTimeBehavior to convertToNull. My database table contains a DATE field with value 0000-00-00 I have the following code: String stringDate = null; Date theDate = resultset.getDate(columnIndex); if( resultset.wasNull() == true ) { stringDate = "null"; } else { stringDate = date.toString(); } The call to "getDate(...)" returns "null" for the field with value 0000-00-00 as expected, but the subsequent call to "wasNull()" returns "false". I expected the call to "wasNull()" to return "true" instead. When I alter the fieldtype to DATETIME and call resultset.getTimestamp(columnIndex) when the value is 0000-00-00 00:00 then I get "null" for the value and "true" from the call to wasNull as expected. How to repeat: see description Suggested fix: always set the flag for "wasNull" to true in method getDate(columnIndex), if this method returns "null"