Cant insert an empty date value into mysql

Question

String datum = datumInvoer.getText();

    **String bdate = bdatumInvoer.getText();**

    String[] speler = spelers.getSelectedItem().toString().split(" ");


    String[] toernooi = toernooien.getSelectedItem().toString().split(" ");
    try {
        PreparedStatement query = con.prepareStatement("INSERT INTO `fullhouse`.`inschrijving_toernooi` ( `IT_datum`, `IT_betaaldatum`, `speler`, `toernooi`) VALUES (?, ?, ? ,?)");
        query.setString(1, datum);
        query.setString(2, bdatum);
        query.setString(3, speler[0]);
        query.setString(4, toernooi[0]);

        query.execute();

i have set the default to NULL , but it still wont insert it i am close to changing the datatype to varchar.

ERROR: incorrect date value

delimiter $$

CREATE TABLE `inschrijving_toernooi` (
  `IT_code` int(11) NOT NULL AUTO_INCREMENT,
 `IT_datum` date DEFAULT NULL,
  `IT_betaaldatum` date DEFAULT NULL,
 `speler` int(11) NOT NULL,
 `toernooi` int(11) NOT NULL,
PRIMARY KEY (`IT_code`),
UNIQUE KEY `aaa` (`speler`,`toernooi`),
 KEY `FS_code_idx` (`speler`),
KEY `FT_code_idx` (`toernooi`),

Why are you calling `setString` if these are meant to be *date* values? — Jon Skeet, Jan 24 '13 at 17:36
You need to use setDate instead setString, also check this link http://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date — fmodos, Jan 24 '13 at 17:40
why can i succesfully insert a string as date into mysql database? but not if its empty — hamchi, Jan 24 '13 at 17:43
how do insert date values then though, even though inserting the dates as string doese work fine. — hamchi, Jan 24 '13 at 17:46

Smit · Accepted Answer · 2013-01-24T18:55:39.753

1

First Convert your date parameter from string to date. For that follow code below.

String bdate = bdatumInvoer.getText();
// get the correct date Format
// follow the link to get correct date format
// throws ParseException 
Date date = new SimpleDateFormat("dd-MM-yyyy").parse(bdate);

Class SimpleDateFormat API

Now in prepared statement set them as date like shown below. As your table structure allows date in table.

query.setDate(2, date);

Now you still want to set null to that column then you can use and tell your query to set null by using following format in prepared statement.

query.setNull(2, java.sql.Types.DATE);

OR

query.setDate(2, null);

setNull API

Mapping of java.sql.Types to SQL types

I think this will solve your issue.

edited Jan 24 '13 at 18:55

answered Jan 24 '13 at 17:56

Smit

4,685
1
24
28

set null doesnt work it doesnt recognize DATE, i need format dd-mm-yyyy how do i apply your code to that format – hamchi Jan 24 '13 at 18:40
I updated the answer, Let me know if that works for you. For date format I already provided you the link for everything you need. However I changed the dateformat in answer. – Smit Jan 24 '13 at 18:47
thanks for hanging in there with me! works fine. sorry for bieng so stuborn and not parsing the date first guys – hamchi Jan 24 '13 at 19:48

score 0 · Answer 2 · answered Jan 24 '13 at 17:52

You need to change how you are passing parameters to you prepared statement

It should be something like below. And datum and bdatum should be of type Date. If you do not use what types are defined in your db you could put pass invalid data to your sql which would cause exceptions.

query.setDate(1, datum);
query.setDate(2, bdatum);
query.setInt(3, speler[0]);
query.setInt(4, toernooi[0]);

matts · Answer 3 · 2013-01-24T17:59:05.310

I suspect your bdatum variable contains an empty string. If you want to insert a SQL null, you need to pass in null, not empty string. (When I tried this on my machine with a similar insert, I got an "Incorrect date value" when trying to insert an empty string, but not with a null.)

if ("".equals(bdatum))
    bdatum = null;

ps.setString(2, bdatum);

When you pass in a string in JDBC for a date value, MySQL will try to parse it, but empty string ("") is not a valid value. This works similarly to if you tried to do an insert with a date literal:

INSERT INTO `some_table` (`some_nullable_date_column`) VALUES ('2000-01-01') -- this works
INSERT INTO `some_table` (`some_nullable_date_column`) VALUES (NULL) -- this works
INSERT INTO `some_table` (`some_nullable_date_column`) VALUES ('') -- this fails

As an aside, passing in a string for a date value is semantically wrong and possibly less robust than parsing the string to a Date object before passing it to the PreparedStatement.

I think he can also use `setNull()`. – Smit Jan 24 '13 at 18:16 — Smit, Jan 24 '13 at 18:16

Cant insert an empty date value into mysql

3 Answers3