MySQL Incorrect date value: null

Question

I'm trying to update a table from a Java application where a certain column may be NULL. I have tried several different approaches but I always get the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'null' for column 'scheidingsdatum' at row 1

I made sure that the table allowed NULL values for the scheidingsdatum field, and can insert NULL values when directly inserting in MySQL

This is the table structure in PHPMyAdmin:

The tables use innoDB

I have tried the following solutions:

1: Just use the NULL variable in a parameter

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, scheidingsdatum);
stmnt.setString(5, nummer);

2: Hardcode NULL in the query (inside if/else block)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = NULL "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, nummer);

3: Use setNull(4, java.sql.Types.DATE)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.DATE);
stmnt.setString(5, nummer);

4: Use setNull(4, java.sql.Types.NULL)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.NULL);
stmnt.setString(5, nummer);

the following is my database.properties file and connection creation:

database.properties

jdbc.drivers=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://IP:3306/TABLE_NAME
jdbc.username=USER
jdbc.password=PASSWORD

Connection creation

Class.forName(props.getProperty("jdbc.drivers")).newInstance();
this.conn = (Connection) DriverManager.getConnection(props.getProperty("jdbc.url"),props.getProperty("jdbc.username"),props.getProperty("jdbc.password"));

Answer 1

I just made a test and it worked for me with stmnt.setNull(4, java.sql.Types.Date);, are you sure that for stmnt.setString(3, huwelijksdatum); the value of huwelijksdatum is a valid mysql date string and not "null" ?