GeeCON Prague 2014*
The moose likes JDBC and the fly likes MySQL JDBC Driver and Null Dates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL JDBC Driver and Null Dates" Watch "MySQL JDBC Driver and Null Dates" New topic
Author

MySQL JDBC Driver and Null Dates

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

For whatever reason my app has decided to start through SQLExceptions when encountering NULL dates and datetimes. I know that this is intentional as per the driver however I also found a way to tell MySQL to convert zero dates to null. On the connection String you can add:

zeroDateTimeBehavior=convertToNull

In DBVisualizer adding this to the connection string actually works. When I would normally get errors in its console I know longer get those errors. However, in my web application I am still getting the exception. I'm just curious if anyone else has ran into this issue and how they resolved it (other than not allowing null dates).


GenRocket - Experts at Building Test Data
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Gregg,

I thought convertToNull was to handle MySQL's use of zero for DATE/TIME/DATETIME/TIMESTAMP. The default value for DATE/TIME/DATETIME/TIMESTAMP is zeros (varies by format).

Is the date in the database truly null?

Paul

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Paul, yes, that was more or less what i was saying. Just didn't translate correctly. Anyway, I've done some more research and it turns out that the flag is working. However, the column is defined as NOT NULL in the schema. Some of the columns didn't have a date (were null or zero) but since the constraint is NOT NULL it doesn't matter what the flag is set to. An exception is still going to occur. Another date column that allows null values is working fine. I don't know that this discover is fact but from my own conclusions I can't see what else it could be.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Paul Campbell wrote:Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).


My code needs NULL. NULL denotes specific things about these date columns. For example, if the endDate column is null, then something is not complete. This I cannot change as I am working in an existing project and too much code is tied to those values being null.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Gregg Bolinger wrote:
Paul Campbell wrote:Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).


My code needs NULL. NULL denotes specific things about these date columns. For example, if the endDate column is null, then something is not complete. This I cannot change as I am working in an existing project and too much code is tied to those values being null.


Sorry Gregg... I'm slow today.

Okay... now I'm following you... jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull

The convertToNull parameter isn't converting to null when you add it to your jdbc connection for your application.

 
GeeCON Prague 2014
 
subject: MySQL JDBC Driver and Null Dates