Getting a null datetime out of MySQL

Thread view:

elektrophyte - 29 Jul 2005 20:28 GMT

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

Reply to this Message

Paul Tomblin - 29 Jul 2005 20:50 GMT

In a previous article, "elektrophyte" <elektrophyte@yahoo.com> said:

>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()));
}

Signature

Paul Tomblin <ptomblin@xcski.com> http://xcski.com/blogs/pt/
"Panic kills"
-- Rick Grant (quoting RCAF pilot training)

Reply to this Message

elektrophyte - 29 Jul 2005 21:21 GMT

> This is cut and pasted from my code. It runs on PostgresSQL, I'm sure it
> will work exactly the same with MySQL:
[quoted text clipped - 5 lines]
> 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

Reply to this Message

karlheinz klingbeil - 30 Jul 2005 12:54 GMT

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
[quoted text clipped - 3 lines]
>
> 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.

Signature

greetz Karlheinz Klingbeil (lunqual)
http://www.lunqual.de oder http:www.lunqual.net

Reply to this Message

elektrophyte - 30 Jul 2005 20:16 GMT

> 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().

[ ... ]

> 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

Reply to this Message

shakah - 30 Jul 2005 15:57 GMT

> > This is cut and pasted from my code. It runs on PostgresSQL, I'm sure it
> > will work exactly the same with MySQL:
[quoted text clipped - 14 lines]
>
> 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.

Reply to this Message

Paul Tomblin - 30 Jul 2005 19:26 GMT

In a previous article, "shakah" <shakahshakah@gmail.com> said:

>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.

Signature

Paul Tomblin <ptomblin@xcski.com> http://xcski.com/blogs/pt/
God is real, unless declared as an integer.

Reply to this Message

shakah - 30 Jul 2005 20:06 GMT

> In a previous article, "shakah" <shakahshakah@gmail.com> said:
> >Given the following table:
[quoted text clipped - 11 lines]
> Paul Tomblin <ptomblin@xcski.com> http://xcski.com/blogs/pt/
> God is real, unless declared as an integer.

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).

Reply to this Message

Getting a null datetime out of MySQL

Free Magazines