Data truncation: invalid datetime value: 'null' in Java
I have the following query that inserts data into mysql using java.
It gives me this error
Data truncation: Incorrect datetime value: 'null' for column 'lastEventTime' at row 1
lastEventTime
is set as a nullable type datetime
with a default value NULL
. Unfortunately, when I run the same query in phpmyadmin, it accepts and the data is inserted into the table. What is the java solution?
My part codes are below.
source to share
Single quotes ( '
) denote string literals. Here you didn't mean that you are inserting a string 'null'
, but the actual value null
is just stripping the quotes:
INSERT INTO tblDetails
SET eID=1010,entID=2,tID=65,eDateTime='2014-12-04 14:34:44',lastEventTime=null
Also, if it lsatEventTime
doesn't have an unwanted default, you can simply remove the reference to it altogether and let the database use the default ( null
):
INSERT INTO tblDetails
SET eID=1010,entID=2,tID=65,eDateTime='2014-12-04 14:34:44'
EDIT:
To answer the question asked in the comments, this can be done even when constructing the statement dynamically insert
. Just run the following line:
"',lastEventTime='"+rs10d.getString("lastEventTime")+"'";
... and add some logic to test null
s:
"',lastEventTime=" + (rs10d.getString("lastEventTime") == null ?
"null" :
"'"+rs10d.getString("lastEventTime")+"'");
source to share