Getting a null datetime out of MySQL

[elektrophyte]

I have a MySQL DB with a DATETIME column and some JDBC code that gets
dates out of it like this:

java.sql.Date d = rs.getDate(myColumn);

It works fine if the value in the column is an actual date. However, to
support the option of users not entering anything into that field, I've
made the column nullable. Apparantly in MySQL a null datetime becomes
"0000-00-00 00:00:00".

When I try to execute the Java code above and the value in the column
is "0000-00-00 00:00:00", I get this error:

java.sql.SQLException: Value '0000-00-00 00:00:00' can not be
represented as java.sql.Date
com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:4738)
com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1055)
com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1012)
com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1026)

[ ... ]

Has anyone else dealt with this issue, and if so, is there a way around
it?

Thanks,

E

I'm working with MySQL 4.0 and JDK 1.5.0_04

 

[Paul Tomblin]

I have a MySQL DB with a DATETIME column and some JDBC code that gets
dates out of it like this:

java.sql.Date d = rs.getDate(myColumn);

This is cut and pasted from my code. It runs on PostgresSQL, I'm sure it
will work exactly the same with MySQL:
java.sql.Date modificationDate = rs.getDate(p++);

if (!rs.wasNull())
{
playlist.setModificationDate(new java.util.Date(
modificationDate.getTime()));
}

 

[elektrophyte]

This is cut and pasted from my code. It runs on PostgresSQL, I'm sure it
will work exactly the same with MySQL:
java.sql.Date modificationDate = rs.getDate(p++);

if (!rs.wasNull())
{
playlist.setModificationDate(new java.util.Date(
modificationDate.getTime()));
}

The problem is in my program execution will never reach the "if"
statement. It errors out when it attempts to execute getDate(). Even

Object o = rs.getDate(foo);

doesn't work.

E

 

[karlheinz klingbeil]

elektrophyte schrub am Freitag, 29. Juli 2005 22:21
folgendes:

The problem is in my program execution will never
reach the "if" statement. It errors out when it
attempts to execute getDate(). Even

Object o = rs.getDate(foo);

doesn't work.

Then you have to make sure that MySQL cannot return
null values.
You have to modify the column in your database to
return at least a default value.
"Alter table <tablename> change column
<your-column-name> datetime DEFAULT '2000-01-01
00:00:00';

After that you won't get any null values for Date,
instead the Default Date which you can consider
invalid.

 

[shakah]

The problem is in my program execution will never reach the "if"
statement. It errors out when it attempts to execute getDate(). Even

Object o = rs.getDate(foo);

doesn't work.

E

Given the following table:
create table ndtest ( colA datetime not null ) ;
insert into ndtest values(null), (now()) ;

If you modify your SQL from (what I assume is) something like:
select colA from ndtest ;

to:
select IF(colA='0000-00-00 00:00:00',NULL,colA) from ndtest ;

I think rs.getDate(1) will return null rather than throwing an
Exception.

 

[Paul Tomblin]

Given the following table:
create table ndtest ( colA datetime not null ) ;
insert into ndtest values(null), (now()) ;

Except he asserted that he made his column nullable. And my experience
with both PostresSQL and MySQL seems to indicate that both of them have no
problem whatsoever with null datetimes, and return null (and not
'0000-00-00 00:00:00') when you try to retrieve a null. I would suggest
that the original poster either didn't actually make the column nullable,
or he didn't actually insert a null when he meant to.

 

[shakah]

Except he asserted that he made his column nullable. And my experience
with both PostresSQL and MySQL seems to indicate that both of them have no
problem whatsoever with null datetimes, and return null (and not
'0000-00-00 00:00:00') when you try to retrieve a null. I would suggest
that the original poster either didn't actually make the column nullable,
or he didn't actually insert a null when he meant to.

My mistake, I missed the "I made the column nullable" in the original
post. I assumed that he had run into the MySQL-ism of NOT NULL
datetime columns silently getting a "zero" default value (as opposed to
being NOT NULL).

 

[elektrophyte]

elektrophyte schrub am Freitag, 29. Juli 2005 22:21
folgendes:

[ ... ]

Then you have to make sure that MySQL cannot return
null values.
You have to modify the column in your database to
return at least a default value.

[ ... ]

After that you won't get any null values for Date,
instead the Default Date which you can consider
invalid.

Yeah, that's pretty much what I decided to do. It prevents the
exception, though doesn't seem like the ideal design. But it solves the
problem, so, onward.

Thanks all for the replies.

E