Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL JDBC Driver and Null Dates

 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic