-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Scott Purcell wrote:
> Hello
> I am using org.gjt.mm.mysql.Driver to connect a web-app to a mysql database.
> I ran into an error, I could use some help with.
>
> I have a database that has a timestamp field
> CREATE TABLE FOO (
> create_date TIMESTAMP,
> approved_date TIMESTAMP,
> ) TYPE=InnoDB;
>
> Now when I insert into foo, I insert the create_date of course when new foo
> records are inserted. But I do not insert anything for the approved_date,
> because the foo is not known yet. Anyway what I found was a problem. When
> using the driver, and I say select * from foo, I get the following error,
> which makes sense, because it says it cannot convert 00000000000000 which is
> null of course.
Scott,
'00000000000000' is _not_ NULL. It's an invalid date. There's a reason
the server does this that has to do with the fact that MyISAM doesn't
have transactions, so _every_ column has to have a default value,
however Java has no way of dealing with an all-zero datetime value.
Older versions of the driver _silently_ converted this to NULL, but it's
not correct behavior according to either the SQL standard or the JDBC
specification.
> So is this a driver problem, or just the way the driver handles the data? Any
> ideas how to get around this?
Any reason you can't use DATETIME for one of the columns and leave that
one null when you do your inserts? TIMESTAMP is roughly the same,
however it auto-updates the value whenever the record is updated, while
DATETIMEs do not, in fact you probably want something more like:
CREATE TABLE FOO (
create_date DATETIME NOT NULL,
approved_date DATETIME NULL
) TYPE=InnoDB;
And then in your INSERT statement, use NOW() for your create_date.
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com
MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration until February 28: http://www.mysqluc.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCLGjxtvXNTca6JD8RAlhGAJ98hvHSVfsZPulH/3usn0QpwKvO9ACfUO8D
BAAscENk+vUXMsRXrvttlq4=
=9Rl1
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
